DocumentDB: JSON-in-JSON-out in NOSQL with familiar SQL–True Story: Part 2

In the world of DataBases – words like Scale, Performance and Schema flexibility have almost gained the status of a cliché these days – and there is always a reason why some words gain such status – because it is overtly used and most importantly – it is crucial to applications. With scalable internet applications becoming ubiquitous, the need for databases to rise up to such performances needs has never been so high – and this is reflected in the adoption of NOSQL databases. Through this tutorial I will cover the basics of Azure DocumentDB, its design elements and how it is suited for applications these days.

In DocumentDB: The move towards NOSQL - Part 1, I basically covered why NOSQL as a platform has become popular and how Azure DocumentDB is designed to incorporate this shift. In this part, I am just going to get you familiarized with the query engine in DocumentDB – will also give you an idea of how easy it is to manage and learn this platform.

First Things first – lets connect the DocumentDB to your application

I have essentially created a simple .NET MVC todo app – pretty self explanatory. All it does is creates a todo list – and does the regular CRUD (create, read, update, delete) functions to show the functionality:

In order to make the connection to the DocumentDB as the backend database for this app – all I needed to do is to go to the Azure portal –>documentDB->Settings –> Key and make a copy of the URI and key:

Now, we can go back to Visual Studio on our application and enter these details in our web.config file:

As easy as that – now all you needed to do is adjust your data model and query language to JSON. Lets see how does that work:

Query Time

There are a plethora of options for you to explore your documents in DocumentDB. The first thing is to explore the options there on the portal itself:

1. Document Explorer

As you can see. a document can be considered as a record. We can create documents in a table through the Document Explorer as shown above. Click on Create Document allows you to create a ‘record’ in the Database – under the collection. The hierarchy in a DocumentDB works like: DocumentDB Account – > Document DB-> Database –> Collections –> Documents. Most important thing to note is that these Documents are in JSON. By clicking on ‘Add document’ you can add multiple documents through a single file which will have the JSON input. ‘id’ is an attribute is a unique identified and will be present in all documents – even if you don’t assign it, it will be automatically assigned.

2. Query Explorer

Now you click on the Query Explorer – as you can try out your queries directly in this space here – click on Run Query and the output will be seen in the results pane. The output in JSON – reiterating that Azure DocumentDB is JSON-in-JSON-out.

3. Programmatically Query DocumentDB

SQL Query in DocumentDB explains in detail the query options that exist with DocumentDB Query engine. Since we are using query language on JSON – there is a mindset shift required. You basically need to stop thinking <Tables> <Rows> <Columns> and start thinking hierarchy set through the document structure – and each hierarchy can be referred to as node1.node2.node3…etc.

As you can also guess, JOINS are fundamentally different too – there is no schema. DocumentDB is de-normalised data. The link above has a beautiful summary of all such clauses and syntax. Definitely worth a read to understand how the query language has been designed.

4. Query Playground

Now this is a literal playground for DocumentDB: https://www.documentdb.com/sql/demo.

This allows you to play around with your query and experiment with DocumentDB – allowing you to try out some of the newest features on DocumentDB such as Geospatial and Sandbox. You can click on Sample Docs to understand the sample data set on which you will be executing your queries.

In conclusion, DocumentDB is an excellent NOSQL solution with a very powerful query engine, excellent toolsets and integration with a whole set of languages such as JavaScript, .NET & LINQ. After all if DocumentDB stores all the data for MSN which has over 450 million monthly visitors and aspiring to hit a million users, definitely - Azure DocumentDB has got it right!

We would love to hear your stories and experiments with NOSQL and specifically DocumentDB. Keep me posted @AdarshaDatta.

Technorati Tags: NOSQL,DocumentDB,Azure,SQL,query,PaaS,document-as-a-service,cloud