在 Azure Cosmos DB 中使用数组和对象Working with arrays and objects in Azure Cosmos DB

Azure Cosmos DB SQL API 的一个重要功能是创建数组和对象。A key feature of the Azure Cosmos DB SQL API is array and object creation.

数组Arrays

可以构造数组,如以下示例所示:You can construct arrays, as shown in the following example:

    SELECT [f.address.city, f.address.state] AS CityState
    FROM Families f

结果有:The results are:

    [
      {
        "CityState": [
          "Seattle",
          "WA"
        ]
      },
      {
        "CityState": [
          "NY", 
          "NY"
        ]
      }
    ]

还可以使用 ARRAY 表达式根据子查询的结果构造数组。You can also use the ARRAY expression to construct an array from subquery's results. 此查询获取数组中子项的所有不同给定名称。This query gets all the distinct given names of children in an array.

SELECT f.id, ARRAY(SELECT DISTINCT VALUE c.givenName FROM c IN f.children) as ChildNames
FROM f

迭代Iteration

SQL API 支持循环访问 JSON 数组,它可以通过 FROM 源中的 IN 关键字添加一个新的构造。The SQL API provides support for iterating over JSON arrays, with a new construct added via the IN keyword in the FROM source. 在以下示例中:In the following example:

    SELECT *
    FROM Families.children

结果有:The results are:

    [
      [
        {
          "firstName": "Henriette Thaulow",
          "gender": "female",
          "grade": 5,
          "pets": [{ "givenName": "Fluffy"}]
        }
      ], 
      [
        {
            "familyName": "Merriam",
            "givenName": "Jesse",
            "gender": "female",
            "grade": 1
        }, 
        {
            "familyName": "Miller",
            "givenName": "Lisa",
            "gender": "female",
            "grade": 8
        }
      ]
    ]

下一个查询循环访问 Families 容器中的 childrenThe next query performs iteration over children in the Families container. 输出的数组与前面的查询不同。The output array is different from the preceding query. 此示例拆分 children 并将结果平展为单个数组:This example splits children, and flattens the results into a single array:

    SELECT *
    FROM c IN Families.children

结果有:The results are:

    [
      {
          "firstName": "Henriette Thaulow",
          "gender": "female",
          "grade": 5,
          "pets": [{ "givenName": "Fluffy" }]
      },
      {
          "familyName": "Merriam",
          "givenName": "Jesse",
          "gender": "female",
          "grade": 1
      },
      {
          "familyName": "Miller",
          "givenName": "Lisa",
          "gender": "female",
          "grade": 8
      }
    ]

可以进一步筛选该数组的每个条目,如以下示例所示:You can filter further on each individual entry of the array, as shown in the following example:

    SELECT c.givenName
    FROM c IN Families.children
    WHERE c.grade = 8

结果有:The results are:

    [{
      "givenName": "Lisa"
    }]

还可基于数组迭代的结果进行聚合。You can also aggregate over the result of an array iteration. 例如,以下查询计数所有家庭中的孩子数目。For example, the following query counts the number of children among all families:

    SELECT COUNT(child)
    FROM child IN Families.children

结果有:The results are:

    [
      {
        "$1": 3
      }
    ]

后续步骤Next steps