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
};

如何查询让宠物叫高飞"的孩子?

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.

在查询宠物名为高飞"的孩子时，可以尝试:

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/en-us/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屋！