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

适用于: SQL API

Azure Cosmos DB SQL API 的一个重要功能是创建数组和对象。A key feature of the Azure Cosmos DB SQL API is array and object creation. 本文档使用可以通过家庭数据集重新创建的示例。This document uses examples that can be recreated using the Family dataset.

下面是此数据集中的一个示例项:Here's an example item in this dataset:

{
  "id": "AndersenFamily",
  "lastName": "Andersen",
  "parents": [
     { "firstName": "Thomas" },
     { "firstName": "Mary Kay"}
  ],
  "children": [
     {
         "firstName": "Henriette Thaulow",
         "gender": "female",
         "grade": 5,
         "pets": [{ "givenName": "Fluffy" }]
     }
  ],
  "address": { "state": "WA", "county": "King", "city": "Seattle" },
  "creationDate": 1431620472,
  "isRegistered": true
}

数组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

其结果是:The results are:

[
    {
        "id": "AndersenFamily",
        "ChildNames": []
    },
    {
        "id": "WakefieldFamily",
        "ChildNames": [
            "Jesse",
            "Lisa"
        ]
    }
]

迭代Iteration

SQL API 支持循环访问 JSON 数组,其中 IN 关键字在 FROM 源中。The SQL API provides support for iterating over JSON arrays, with 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(1) AS Count
FROM child IN Families.children

其结果是:The results are:

[
  {
    "Count": 3
  }
]

备注

使用 IN 关键字进行迭代时,不能筛选或投射数组外部的任何属性。When using the IN keyword for iteration, you cannot filter or project any properties outside of the array. 应改用 JOINInstead, you should use JOINs.

有关其他示例,请阅读有关在 Azure Cosmos DB 中使用数组的博客文章For additional examples, read our blog post on working with arrays in Azure Cosmos DB.

后续步骤Next steps