在这样的Azure Documentdb文档中
{
"id": "WakefieldFamily","parents": [
{ "familyName": "Wakefield","givenName": "Robin" },{ "familyName": "Miller","givenName": "Ben" }
],"children": [
{
"familyName": "Merriam","givenName": "Jesse","gender": "female","grade": 1,"pets": [
{ "givenName": "Goofy" },{ "givenName": "Shadow" }
]
},{
"familyName": "Miller","givenName": "Lisa","grade": 8
}
],"address": { "state": "NY","county": "Manhattan","city": "NY" },"isRegistered": false
};
宠物给孩子的名字叫做“傻瓜”,我怎么查询？
看来下面的语法无效
Select * from root r WHERE r.children.pets.givenName="Goofy"
相反,我需要做
Select * from root r WHERE r.children[0].pets[0].givenName="Goofy"
这不是真正通过数组搜索.
任何关于如何处理这些查询的建议？
您应该利用DocumentDB的JOIN子句,它与RDBM中的JOIN有所不同(因为DocumentDB处理无模式文档的denormlaize数据模型).
简单来说,您可以将DocumentDB的JOIN视为自联接,可用于在嵌套JSON对象之间形成交叉产品.
在询问叫做“Goofy”的宠物的孩子的背景下,你可以试试：
SELECT
f.id AS familyName,c AS child,p.givenName AS petName
FROM Families f
JOIN c IN f.children
JOIN p IN c.pets
WHERE p.givenName = "Goofy"
哪个返回：
[{
familyName: WakefieldFamily,child: {
familyName: Merriam,givenName: Jesse,gender: female,grade: 1,pets: [{
givenName: Goofy
},{
givenName: Shadow
}]
},petName: Goofy
}]
参考：http://azure.microsoft.com/en-us/documentation/articles/documentdb-sql-query/
编辑：
您还可以使用ARRAY_CONTAINS功能,如下所示：
SELECT food.id,food.description,food.tags FROM food WHERE food.id = "09052" or ARRAY_CONTAINS(food.tags.name,"blueberries")