Query Using Azure Document-DB SQL

This blog explains the creation of Azure Document DB and querying the documents.

What Is DocumentDB 

 
Azure DocumentDB supports querying of documents using a familiar SQL (Structured Query Language) over hierarchical JSON documents. DocumentDB is truly schema-free; by virtue of its commitment to the JSON data model directly within the database engine, it provides automatic indexing of JSON documents without requiring explicit schema or creation of secondary indexes.
 
Step 1
 
In your system, please install Azure Cosmos DB emulator. And after the installation, run your emulator.
 
Step 2
 
Click on the "Explorer" button to create Document DB.
 
Query Using Azure Document-DB SQL 
figure 1
 
See the below snapshot for the structure of Document DB which I created.
 
In the above, I have created test as a database and studentcollection as a collection. After creating the collection, you need to add a document.
 
Query Using Azure Document-DB SQL
figure 2
 

We can add documents in two ways 

  • Create a file with .json extension. In that file, add a proper or valid JSON data. Using the upload option, you can add or create your documents.
  • The second way is by using the text-editor which is provided in the emulator.
For this see the below figure 3
 
Query Using Azure Document-DB SQL
figure 3 
 
First, select your collection and click on the New Document button. In the editor, type your JSON and click the Save button.
 
Step 3 - Query Using Azure Document-db SQL
 
Please see how I am querying the below document.
  1. [{  
  2.     "id""100",  
  3.     "timestamp""2018-08-10T11:47:37+00:00",  
  4.     "user_id""DZbCkVFXkFqFrxYdPSvZnr",  
  5.     "event""Cosmos db ",  
  6.     "student": {  
  7.         "name""Madan Sorab",  
  8.         "age""25",  
  9.         "sex""Male",  
  10.         "adress""bangalore",  
  11.         "status""active",  
  12.         "nickname""maddy"  
  13.     },  
  14.     "_rid""AFgvAIe9lQALAAAAAAAACA==",  
  15.     "_self""dbs/AFgvAA==/colls/AFgvAIe9lQA=/docs/AFgvAIe9lQALAAAAAAAACA==/",  
  16.     "_etag""\"00000000-0000-0000-ae51-77746e8201d4\"",  
  17.     "_attachments""attachments/",  
  18.     "_ts": 1547721802  
  19. }, {  
  20.     "id""101",  
  21.     "timestamp""2018-08-10T11:47:37+00:00",  
  22.     "user_id""GZbCkVFXkFqFrxYdPSvZnr",  
  23.     "event""Cosmos",  
  24.     "student": {  
  25.         "name""Lavanya",  
  26.         "age""24",  
  27.         "sex""Female",  
  28.         "adress""Mysore",  
  29.         "status""active",  
  30.         "nickname""lovely"  
  31.     },  
  32.     "_rid""AFgvAIe9lQAMAAAAAAAACA==",  
  33.     "_self""dbs/AFgvAA==/colls/AFgvAIe9lQA=/docs/AFgvAIe9lQAMAAAAAAAACA==/",  
  34.     "_etag""\"00000000-0000-0000-ae51-9535dbee01d4\"",  
  35.     "_attachments""attachments/",  
  36.     "_ts": 1547721851  
  37. }]  
Document query
 
Query is:
 
SELECT*FROM c
 
It will give all the documents which are in the collection. 
 

Sub-documents Query 

 
Now let's try a few queries against this data to understand some of the key aspects of DocumentDB SQL. For example, the following query will return the documents where the id field matches "Madan Sorab". Since it's a SELECT *, the output of the query is the complete JSON document,
 
Query Is:
 
SELECT*FROM c where c.student.name="Madan Sorab"
 
please refer to figure 4
 
Query Using Azure Document-DB SQL
figure 4
 
Now consider the case where we need to reformat the JSON output in a different shape. This query projects a new JSON object with 2 selected fields.
 

Aliasing Query 

 
Query is:
SELECT {"studentname":c.student.name, "studentage":c.student.age} AS studentinfo
FROM c
 
Above I have mentioned my JSON, please check in that student array having key name and age. Using the above query, I am reformating the JSON. Please check figure 5 for understanding.
 
Query Using Azure Document-DB SQL
figure 5
 
The WHERE clause (WHERE <filter_condition>) is optional. It specifies the condition(s) that the JSON documents provided by the source must satisfy in order to be included as part of the result. Any JSON document must evaluate the specified conditions to "true" to be considered for the result. The WHERE clause is used by the index layer in order to determine the absolute smallest subset of source documents that can be part of the result. To learn more about how indexing works, please refer to the DocumentDB indexing documentation. The following query requests documents that contain a name property and that the property's value is "Madam Sorab". Any other document that does not have a name property, or where the value does not match "Madan Sorab" is excluded.
 
Query is:
SELECT*FROM c where c.student.name="Madan Sorab"
 
please refer to figure 4 for understanding.
 
In this blog, I explained the creation of collections, adding documents, document query, sub document query, and alias.