As the devices become faster and faster and the internet also, everyone has a device so the amount of data being produced everyday is getting larger every minute, and this is what NoSQL for, if you are a developer, of course you have used Microsoft SQL Server, Oracle or MySQL as a data store you are familiar with the concept of tables, rows, columns and foreign keys, the term NoSQL is not new, but many developers are not get used for it or even imagine how a database with now rows, columns and relationships could look like and how you can query that database. If you are one of those developers then this article is just for you. 

This blog is associated with the lecture on AK Academy in the following link https://youtu.be/oJA_Q306Rpo

In this article we will see how you can query and fetch data from a NoSQL Azure Cosmos DB database. If you also not familiar with what is Azure Cosmos Db checkout the following link to get started https://youtu.be/gz8eEXxJ-rI

Using the Core API with Cosmos DB you store the data as JSON documents for example here is a snapshot of a Person document stored in Azure Cosmos Db:

{

    "id": "12345",

    "firstName": "Charles",

    "lastName": "Byrd",

    "salary": 7500,

    "city": "Florence",

    "children": [

        {

            "name": "Stanford",

            "age": 12

        },

        {

            "name": "Esther",

            "age": 8

        }

    ],

    "job": "Software Developer",

    "version": "v2",

    "_rid": "9phSANnK3wcBAAAAAAAAAA==",

    "_self": "dbs/9phSAA==/colls/9phSANnK3wc=/docs/9phSANnK3wcBAAAAAAAAAA==/",

    "_etag": "\"00000000-0000-0000-74bd-2b79f41901d6\"",

    "_attachments": "attachments/",

    "_ts": 1597685888

}

As you can see here we don't have tables, it is basically a simple JSON document with set of properties that represent a simple values like string, int, boolean or a complicated objects.

To be able to query these documents, Cosmos Db support a development version of T-SQL for Cosmos so you will use the same concept to fetch the documents as following:

SELECT * FROM c

The previous query fetches all the documents within a specific container 'c' here refers to the container.

WHERE Clause:

Just like Relational SQL databases you can use the WHERE clause to filter your data in the query like this

SELECT * FROM c WHERE c.id = '12345'

Of course, you can use the operators (AND, OR, >, <, >=, <=, <>)

Functions:

The following table shows a set of functions that can be used to retrieve a specific results as following:

There is still a set of specific functions related to the Geo-Location data will be mentioned in a separated blog post

Retrieving JSON Objects:

We will see in this section how you can control the retrieved JSON in your response, T-SQL provides you with high flexible syntax to return a JSON object in the way you want.

In this example we will see how we will get a person but only the ID and the FullName and a boolean indicator to indicates if the person has children or not:

SELECT { 'id': c.id, 'fullName': CONCAT(c.firstName, ' ', c.lastName), 'hsaChildren': ARRAY_LENGTH(c.children) > 0 ? true : false }  FROM c

Many Blog Posts will be ready soon to talk more about how to deal with Azure Cosmos DB that great database which is being used by Microsoft for long time internally, will see also how we can develop a highly scalable solutions with Azure Cosmos DB and ASP.NET Core via the .NET SDK

Thanks for reading