Edit

Work with arrays and objects in Azure Cosmos DB for NoSQL

  • Article

APPLIES TO: NoSQL

Here's an item that's used in examples throughout this article.

{
  "name": "Sondon Fins",
  "categories": [
     { "name": "swim" },
     { "name": "gear"}
  ],
  "metadata": {
    "sku": "73310",
    "manufacturer": "AdventureWorks"
  },
  "priceInUSD": 132.35,
  "priceInCAD": 174.50
}

Arrays

You can construct arrays using static values, as shown in the following example.

SELECT
  [p.priceInUSD, p.priceInCAD] AS priceData
FROM products p

[
  {
    "priceData": [
      132.35,
      174.5
    ]
  }
]

You can also use the ARRAY expression to construct an array from a subquery's results. This query gets all the distinct categories.

SELECT
    p.id,
    ARRAY (SELECT DISTINCT VALUE c.name FROM c IN p.categories) AS categoryNames
FROM
    products p

[
  {
    "id": "a0151c77-ffc3-4fa6-a495-7b53d936faa6",
    "categoryNames": [
      "swim",
      "gear"
    ]
  }
]

Iteration

The API for NoSQL provides support for iterating over JSON arrays, with the IN keyword in the FROM source.

As an example, the next query performs iteration over tags for each item in the container. The output splits the array value and flattens the results into a single array.

SELECT
    *
FROM 
  products IN products.categories

[
  {
    "name": "swim"
  },
  {
    "name": "gear"
  }
]

You can filter further on each individual entry of the array, as shown in the following example:

SELECT VALUE
    p.name
FROM
    p IN p.categories
WHERE
    p.name LIKE "ge%"

The results are:

[
  "gear"
]

You can also aggregate over the result of an array iteration. For example, the following query counts the number of tags:

SELECT VALUE
    COUNT(1)
FROM
    p IN p.categories

The results are:

[
  2
]

Note

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

Related content