Azure DocumentDb 中数组的 WHERE 子句 [英] WHERE clause on an array in Azure DocumentDb
问题描述
在这样的 Azure Documentdb 文档中
In an Azure Documentdb document like this
{
"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
};
如何查询以获取宠物名字为Goofy"的孩子?
How do I query to get children whose pets given name is "Goofy" ?
看起来下面的语法无效
Select * from root r
WHERE r.children.pets.givenName="Goofy"
相反,我需要这样做
Select * from root r
WHERE r.children[0].pets[0].givenName="Goofy"
这并不是真正搜索数组.
which is not really searching through an array.
关于我应该如何处理此类查询的任何建议?
Any suggestion on how I should handle queries like these ?
推荐答案
你应该利用 DocumentDB 的 JOIN
子句,它的操作与 RDBM 中的 JOIN
有点不同(因为 DocumentDB 处理无模式文档的非规范化数据模型).
You should take advantage of DocumentDB's JOIN
clause, which operates a bit differently than JOIN
in RDBMs (since DocumentDB deals w/ denormlaized data model of schema-free documents).
简单地说,您可以将 DocumentDB 的 JOIN
视为自联接,可用于在嵌套的 JSON 对象之间形成交叉积.
To put it simply, you can think of DocumentDB's JOIN
as self-joins which can be used to form cross-products between nested JSON objects.
在查询宠物名字为Goofy"的孩子的上下文中,您可以尝试:
In the context of querying children whose pets given name is "Goofy", you can try:
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/zh-CN/documentation/articles/documentdb-sql-query/
您还可以使用 ARRAY_CONTAINS
函数,它看起来像这样:
You can also use the ARRAY_CONTAINS
function, which looks something like this:
SELECT food.id, food.description, food.tags
FROM food
WHERE food.id = "09052" or ARRAY_CONTAINS(food.tags.name, "blueberries")
这篇关于Azure DocumentDb 中数组的 WHERE 子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!