Query Using Azure Document-DB SQL

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.

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.

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

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.

[{
"id": "100",
"timestamp": "2018-08-10T11:47:37+00:00",
"user_id": "DZbCkVFXkFqFrxYdPSvZnr",
"event": "Cosmos db ",
"student": {
"name": "Madan Sorab",
"age": "25",
"sex": "Male",
"adress": "bangalore",
"status": "active",
"nickname": "maddy"
},
"_rid": "AFgvAIe9lQALAAAAAAAACA==",
"_self": "dbs/AFgvAA==/colls/AFgvAIe9lQA=/docs/AFgvAIe9lQALAAAAAAAACA==/",
"_etag": "\"00000000-0000-0000-ae51-77746e8201d4\"",
"_attachments": "attachments/",
"_ts": 1547721802
}, {
"id": "101",
"timestamp": "2018-08-10T11:47:37+00:00",
"user_id": "GZbCkVFXkFqFrxYdPSvZnr",
"event": "Cosmos",
"student": {
"name": "Lavanya",
"age": "24",
"sex": "Female",
"adress": "Mysore",
"status": "active",
"nickname": "lovely"
},
"_rid": "AFgvAIe9lQAMAAAAAAAACA==",
"_self": "dbs/AFgvAA==/colls/AFgvAIe9lQA=/docs/AFgvAIe9lQAMAAAAAAAACA==/",
"_etag": "\"00000000-0000-0000-ae51-9535dbee01d4\"",
"_attachments": "attachments/",
"_ts": 1547721851
}]

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

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.

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.