在这样的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",
"gender": "female",
"grade": 8
}
],
"address": { "state": "NY", "county": "Manhattan", "city": "NY" },
"isRegistered": false
};
如何查询让宠物叫“高飞”的 child ?
看起来以下语法无效
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处理的是无模式文档的去 ionic 化数据模型)。
简而言之,您可以将DocumentDB的JOIN
视为自连接,可用于在嵌套JSON对象之间形成交叉乘积。
在查询宠物名为“高飞”的 child 时,可以尝试:
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")