nosql – 在Azure DocumentDb中的数组上的WHERE子句

发布时间：2020-12-13 13:31:54 所属栏目：百科来源：网络整理

导读：在这样的Azure Documentdb文档中 {"id": "WakefieldFamily","parents": [ { "familyName": "Wakefield","givenName": "Robin" },{ "familyName": "Miller","givenName": "Ben" }],"children": [ { "familyName": "Merriam","givenName": "Jesse","gender": "

在这样的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")

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!