Azure CosmosDB Using SQL API

Introduction

 

In this tutorial, we are going to discuss Azure CosmosDB and run a few queries using one of the APIs that is recommended when creating new applications. Before this, let's understand what Azure CosmosDB is, and why it's amazing.

 

Azure CosmosDB is a NoSQL Database. By NoSQL, we mean that it stores data in a Non-Relational Format. It is a document-based database system. However, unlike others, it is Multi-Model. This means that there are several ways to access and store data in it, depending on the business use cases.

1. You can migrate data from Cassendra and MongoDB and use Azure CosmosDB as a backend store and run queries of Cassendra or MongoDB, whichever you used. Your backend will change and the application will remain running with minimal changes.
2. You can use Azure Table Storage API, which means you can change the backend from Azure Table Storage to Azure CosmosDB by running the same Queries.
3. If the business demands a Graph Database-Based Query Model, we can use Gremlin API to get things done.
4. If we are building an application from scratch, we can use SQL API that is strongly recommended for working with Azure. The queries we run using SQL API offer query structure similar to SQL with some limitations.

Azure CosmosDB is cloud-based. It offers low-latency (<=10ms). It ensures high availability i.e. 99.99% Availability and 99.999% in Geo-Replication. It can scale very large and ensures unlimited scaling with high workloads.

 

It has enterprise-level security.

 

Anyone can do hands-on without hassle. This will allow developers to do easy on-boarding and improve their skills: https://www.documentdb.com/sql/demo

 

More Details: https://azure.microsoft.com/en-in/services/cosmos-db/

 

Storage Structure

 

Here is the diagram representation of how a document is organized internally:

 

More details: https://docs.microsoft.com/en-us/azure/cosmos-db/account-databases-containers-items

 

Hands-On

 

Now, we are going to work on CosmosDB using SQL API.

 

Follow the steps below:

To get all records:

SELECT * FROM Persons

 

To count all records:

 

Method 1

SELECT COUNT(P) FROM Persons P

[
    {
        "$1": 4
    }
]

Method 2

SELECT VALUE COUNT(P) FROM Persons P

[
    4
]

Note

WON’T WORK - SELECTCOUNT(*) FROM Persons

 

Failed to query item for container Persons: Gateway Failed to Retrieve Query Plan: Message: {"errors":[{"severity":"Error","location",

{"start":13,"end":14},"code":"SC1001","message":"Syntax error, incorrect syntax near '*'."}]} ActivityId: 5d1842d2-d232-4948-a4c8-21599f2980c4, Microsoft.Azure.Documents.Common/2.11.0, Microsoft.Azure.Documents.Common/2.11.0

 

Selecting Specific Columns

SELECT P.firstname,P.age FROM Persons P

[
    {
        "firstname": "亜妃子",
        "age": 5
    },
    {
        "firstname": "Eva",
        "age": 44
    },
    {
        "firstname": "John",
        "age": 23
    },
    {
        "firstname": "Véronique",
        "age": 50
    }
]

Where Condition

SELECT P.firstname,P.age FROM Persons P WHERE P.age>20 AND P.age<50

[
    {
        "firstname": "Eva",
        "age": 44
    },
    {
        "firstname": "John",
        "age": 23
    }
]

Array Data

SELECT [P.firstname,P.age] AS Array_fname_age FROM Persons P

[
    {
        "Array_fname_age": [
            "亜妃子",
            5
        ]
    },
    {
        "Array_fname_age": [
            "Eva",
            44
        ]
    },
    {
        "Array_fname_age": [
            "John",
            23
        ]
    },
    {
        "Array_fname_age": [
            "Véronique",
            50
        ]
    }
]

Return Records with Specific Key in JSON

SELECT P.firstname,P.lastname,P.age FROM Persons P WHERE IS_DEFINED(P.lastname)

[
    {
        "firstname": "Varun",
        "lastname": "Setia",
        "age": 30
    }
]

To Maintain Data Consistency

 

Suppose one document has lastname and others don’t. We use Coalesce

SELECT P.firstname,P.lastname??"NotSet" AS lastname,P.age FROM Persons P

[
    {
        "firstname": "亜妃子",
        "lastname": "NotSet",
        "age": 5
    },
    {
        "firstname": "Eva",
        "lastname": "NotSet",
        "age": 44
    },
    {
        "firstname": "John",
        "lastname": "NotSet",
        "age": 23
    },
    {
        "firstname": "Véronique",
        "lastname": "NotSet",
        "age": 50
    },
    {
        "firstname": "Varun",
        "lastname": "Setia",
        "age": 30
    }
]

To get all IDs as an array:

SELECT * FROM Persons.id

[
    "e141ad63-ea4b-4778-9788-1d39ec7956dd",
    "9cbac020-2b79-460b-b037-9af18ab21f42",
    "a82ae393-fdf4-4f78-a66e-d3b2a98d1696",
    "20566083-a724-4e02-a035-c17691bf17ba",
    "65313e59-9c7c-4fe5-bef8-e0891b4b7cd0"
]

Combined Query Array and JSON

SELECT P.id,[P.firstname,P.lastname] AS fullname FROM Persons P

[
    {
        "id": "e141ad63-ea4b-4778-9788-1d39ec7956dd",
        "fullname": [
            "亜妃子"
        ]
    },
    {
        "id": "9cbac020-2b79-460b-b037-9af18ab21f42",
        "fullname": [
            "Eva"
        ]
    },
    {
        "id": "a82ae393-fdf4-4f78-a66e-d3b2a98d1696",
        "fullname": [
            "John"
        ]
    },
    {
        "id": "20566083-a724-4e02-a035-c17691bf17ba",
        "fullname": [
            "Véronique"
        ]
    },
    {
        "id": "65313e59-9c7c-4fe5-bef8-e0891b4b7cd0",
        "fullname": [
            "Varun",
            "Setia"
        ]
    }
]

Want to know more? Visit here.

 

Thanks for reading. Also, please share your thoughts and opinions.