Let’s use SQL API in CosmosDB

In this very short blog post, we ll see an introduction on how to query Cosmos DB with the SQL API. Now SQL is just like another language when you want to get data out of the database, but the problem that arises is that it a relational database whereas CosmosDB is a NoSQL database.

To avoid this scenario, the semantics used for SQL here are all JSON documents and the JavaScript data types in place of SQL API data types. There is this another option of using the LINQ provider when working with the .NET sdk.

What is LINQ?

Linq provider translates the language-integrated queries written in C# into Cosmos DB SQL. It is good to use Linq for such query translation however this also eventually is an SQL only.

So to avoid all of this, it is implemented in the form of JSON and JavaScript. For Example, in place of using T-SQL, expressions will be evaluated as the JavaScript expressions. Similarly, the operations like SELECT, FROM, WHERE etc. are used but only with JavaScript defined data types like boolean, number etc.

But…

JavaScript has objects, nested arrays, etc. How does SQL manage with that!!?

So, to do this, The Cosmos DB team answers with the options below:

• Using the dot notation to navigate to the path to properties of the nested objects, and arrays.
• The second technique is, just like the tables are joined using Inner Join inside a relational database, we can use Intra-document join to combine the nested elements under a parent element.

For an example, let us consider this:

Doesn’t it look very much like a SQL query?

But do you notice these dot notations between the properties which are actually helping to navigate to the paths of the properties. Also, you can see these intra-document joins done between these documents which again show that it isn’t a SQL query but a smart way of using it by implementing the techniques of JSON semantics and JavaScript expressions.

SUMMARY

In this blog post, we saw an introduction on querying CosmosDB with SQL API. We covered how we can adapt to NoSQL CosmosDB and work with it using the SQL API which is used for relational databases ironically.