Edit

Working with arrays and objects in Azure Cosmos DB

  • Article

APPLIES TO: NoSQL

A key feature of the Azure Cosmos DB for NoSQL 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"
    ]
  }
]

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

The API for NoSQL 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
    }
  ]
]

The next query performs iteration over children in the Families container. The output array is different from the preceding query. 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
  }
]

Note

When using the IN keyword for iteration, you cannot filter or project any properties outside of the array. Instead, you should use JOINs.

For additional examples, read our blog post on working with arrays in Azure Cosmos DB.

Next steps