Basics of an Azure Cosmos DB SQL query

In my previous post, I wrote about getting started with Azure Cosmos DB. In this post, let us see basic SQL's to query the JSON documents.

Microsoft Azure Cosmos DB supports querying documents using SQL as a JSON query language on SQL API accounts. Azure Cosmos DB is truly schema-free.

To show some examples, I am going to create two sample JSON documents:

 --Doc1
 {
    "id": "Doc1",
    "name": "Sathya",
    "age": 29,
    "cars": [
        {
            "name": "Ford",
            "variation": [
                "Fiesta",
                "Focus",
                "Mustang"
            ]
        }
    ],
    "address": [
        {
            "county": "UK",
            "city": "London"
        }
    ]
}

--Doc2
{
    "id": "Doc2",
    "name": "Shalu",
    "age": 27,
    "cars": [
        {
            "name": "BMW",
            "models": [
                "320",
                "X3",
                "X5"
            ]
        }
    ],
    "address": {
        "state": "NY",
        "county": "Manhattan",
        "city": "NY"
    }
}

Difference in the above JSON documents are Doc1 does not have state property inside address and inside cars -> Doc1 has Variation property and Doc2 has models property.

--To query the documents and filter based on document unique Ids
SELECT * FROM c
WHERE c.id = "Doc1"

--In the From clause, we can mention c or collection name - JsonDocs in this examples

-- To get segment of Json document in different shape
SELECT {"DocumentId":c.id,"CustomerName":c.name,"Age":c.age} AS CarOwnerDetail 
FROM c

--To Sort output of documents
SELECT * FROM c
ORDER BY c.age DESC

--To query subdocuments
SELECT *
FROM JsonDocs.address

--Adding another object inside cars in doc2 by manually editing the document
 {
            "name": "Audi",
            "models": [
                "A6",
                "A8",
                "Q2"
            ]
        }

--To query based on Index value
SELECT *
FROM JsonDocs.cars[1]

--To query based on IN keyword
SELECT *
FROM JsonDocs S
WHERE S.address.city IN ('TX', 'NY')

-- Selecting nested properties
SELECT c.address.state, c.address.city, c.address.county
FROM c
WHERE c.id = "Doc2"

--Return top N documents
SELECT TOP 1 *
FROM c

--Use of Aggregate functions, to get total no. of cars
SELECT VALUE COUNT(c.name)
FROM c IN JsonDocs.cars
--VALUE is used to resturn JSON values

-- JOIN Example
SELECT c.name AS CarNames
FROM JsonDocs J
JOIN c IN J.cars

--Modifying models inside cars in doc2 as key-value pairs by manually editing the document
"cars": [
        {
            "name": "BMW",
            "models": [
                {"no" : "320"},
                {"no":"X3"},
                {"no":"X5"}
            ]
        },
        {
            "name": "Audi",
            "models": [
               {"no": "A6"},
               {"no": "A8"},
               {"no": "Q2"}
            ]

--Another JOIN Example
SELECT c.name AS CarName,m.no AS ModelNumber
FROM JsonDocs J
JOIN c IN J.cars
JOIN m IN c.models

--There are various built-in functions
SELECT c.name AS CarName,m.no AS ModelNumber
FROM JsonDocs J
JOIN c IN J.cars
JOIN m IN c.models
WHERE ARRAY_CONTAINS(c.models,{"no": "A6"})

SELECT c.id,c.name
FROM c
WHERE STARTSWITH(c.name, "S")

Reference:

• SQL queries for Azure Cosmos DB

See Also:

• My posts on Microsoft SQL Azure