Skip to main content
 首页 » 编程设计

azure-cosmosdb之Azure DocumentDb中数组上的WHERE子句

2024年10月25日8jpfss

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