How to write an array in Cosmos DB

by
How to write an array in Cosmos DB

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

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

How to write an array in Cosmos DB

Well, as part of this article, we will discuss the process to write 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?

Well, to discuss all the above stuff, let’s consider an example. Here 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 will closely look at the below query, it will find all the reward lists that have the person name as Michel in the first position in the award array.

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

This is how 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 down here is, in Azure Cosmos DB, all the joins are scoped within a single item.

Consider an example, if you will 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"
   }
   },
…
]

Even, 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 and are Correlated and Non-correlated.

You have the provision to use correlated subqueries in Azure Cosmos DB and another important point to note down 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 returns. Those are Multi-value, Table and Scalar, etc.

Multi-value subqueries

Basically, 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

Basically, 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 will run the above query, you will get the output like 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. Along with the JOIN, you can use the array expression in Azure Cosmos DB.

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 will run the above query, you can get the output as below.

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

Cosmos DB query array

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"]
}

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

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 then 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 below articles

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 !!!