How to write an array in Cosmos DB

by
How to write an array in Cosmos DB

This Azure tutorial will discuss how to write an array in Cosmos DB. Along with this, we will also discuss a few other topics below

  • How to perform array projection Azure Cosmos DB
  • Cosmos DB query Array

How to write an array in Cosmos DB

As part of this article, we will discuss the process of writing an array in Azure Cosmos DB, where we will also discuss the below stuff.

  • How to access a specific element of the array?
  • How to iterate over arrays?
  • How to perform Joins?
  • How to use Subqueries?

To discuss all the above, let’s consider an example. The Azure Cosmos DB container contains an award list as JSON documents.

{
  "id": "30",
  "city": "Georgia",
  "awards": [
     {
        "to": "Michel",
        "award": "1000 USD"
     },
     {
        "to": "Leo",
        "award": "750 USD"
     },
     {
        "to": "Richard",
        "award": "500 USD"
     }
  ]
}

How to iterate over arrays?

You can easily iterate over arrays in Azure Cosmos DB using the IN and Fromm keywords. If you will run the below query, it will retrieve data from the award array.

SELECT *
FROM a IN m.awards

The output will be like below

[

{
        "to": "Michel",
        "award": "1000 USD"
     },
     {
        "to": "Leo",
        "award": "750 USD"
     },
     {
        "to": "Richard",
        "award": "500 USD"
     }
  ]

How to access a specific element of the array?

It’s quite easy to access a specific array element on a particular position on the array in Azure Cosmos DB. If you look closely at the below query, it will find all the reward lists with the person named Michel in the first position in the award array.

SELECT *
FROM a
WHERE a.awards[0].to = "Michel"

You can access a specific array position in Azure Cosmos DB.

How to perform Joins?

Let’s discuss here how to perform joins in Azure Cosmos DB. An important point to note here is in Azure Cosmos DB. All the joins are scoped within a single item.

Consider an example: if you see the below query, it will join the awards array with the rest items.

SELECT a.id, m AS award
FROM a
JOIN m IN a.awards

The output will be as below

[
   {
      "id": "30",
      "award": {
                 "to": "Michel",
                 "award": "1000 USD"
              }
   },
   {
      "id": "30",
      "award": {
                 "to": "Leo",
                 "award": "750 USD"
   }
   },
…
]

You can also filter conditions on your query, like below.

SELECT a.id, m AS awards
FROM a
JOIN m IN a.awards
WHERE a.to = "Michel" AND a.city = "Georgia"

How to use Subqueries?

Subqueries are mainly of two types: Correlated and Non-correlated.

You have the provision to use correlated subqueries in Azure Cosmos DB, and another important point to note here is Azure Cosmos DB only supports correlated subqueries.

Further, subqueries can be classified into three categories based on the number of rows and columns they return. Those are Multi-value, Table, and Scalar, etc.

Multi-value subqueries

Multi-value subqueries are the subqueries that return a set of documents. Those are used within the FROM clause.

Optimize JOIN expressions

You can add filter conditions with JOINs in the subqueries in order to optimize the JOIN expressions. Let’s consider the below example. This is better to use in the case of large arrays.

SELECT a.id, awards
FROM a
JOIN (SELECT VALUE m FROM m IN a.awards WHERE m.to = 'Michel') AS awards
WHERE a.city = "Georgia"

EXISTS Expression

The EXISTS expression takes the subquery as input and returns the output as true in case the subquery returns at least one result.

You can use this in the SELECT clause.

Let’s consider the below query as an example

SELECT a.id, EXISTS(SELECT VALUE m FROM m IN a.awards WHERE m.award = "1000 USD") AS awardingTalent
FROM a
WHERE a.city = "Georgia"

If you run the above query, you will get the output like the one below

[
   {
      "id": "30",
      "awardingTalent": false
   },
   {
      "id": "Michel",
      "awardingTalent": true
   }
…
]

How to perform array projection Azure Cosmos DB

You can project the result of a query using the Array expression. You can use the array expression in Azure Cosmos DB along with the JOIN.

Let’s consider the below example

SELECT a.id, ARRAY(SELECT VALUE m.award FROM m in a.awards) as AwardList
FROM a

If you run the above query, you can get the output below.

[
   {
      "id": "30",
      "AwardList": [
         "1000 USD",
         "750 USD",
         "500 USD"
      ]
   },
   {
      "id": "25",
      "AwardList": [
         "600 USD",
         "550 USD",
         "350 USD"
   ]
   }
…
]

Cosmos DB query Array

Are you thinking about what the cosmos db query array contains?

When you have an array of strings in Azure Cosmos DB, you can use the below-listed queries to work with the Cosmos DB array.

For example, consider a document that looks like below

{
  "name" : "Phil",
  "desc" : ["IT", "SWEng"]
}

{
  "name" : "hello",
  "type" : ["first", "item"]
}

Below are a few queries that can be used here to retrieve the items.

Select * from c JOIN desc IN c.desc WHERE desc IN ("SWEng") 
Select * from c JOIN desc IN c.desc WHERE ARRAY_CONTAINS([IT], "SWEng")

If you don’t want to use join, you can try something like the below to return the items.

Select * from c WHERE ARRAY_CONTAINS(c.desc, "SWEng")

You may also like following the articles below

Wrapping Up

In this article, we have discussed how to write an array in Cosmos DB and along with that we have also discussed the below topics.

  • How to perform array projection Azure Cosmos DB
  • Cosmos DB query array

Hope you have enjoyed this article !!!