DocumentDB SQL - Overview

DocumentDB is Microsoft's newest NoSQL document database platform that runs on Azure. In this tutorial, we will learn all about querying documents using the special version of SQL supported by DocumentDB.

NoSQL Document Database

DocumentDB is Microsoft's newest NoSQL document database, however, when we say NoSQL document database, what precisely do we mean by NoSQL, and document database?

• SQL means Structured Query Language which is a traditional query language of relational databases. SQL is often equated with relational databases.

• It is really more helpful to think of a NoSQL database as a non-relational database, so NoSQL really means non-relational.

There are different types of NoSQL databases which include key value stores such as −

• Azure Table Storage
• Column-based stores, like Cassandra
• Graph databases, like NEO4
• Document databases, like MongoDB and Azure DocumentDB

Why SQL Syntax?

This can sound strange at first, but in DocumentDB which is a NoSQL database, we query using SQL. As mentioned above, this is a special version of SQL rooted in JSON and JavaScript semantics.

• SQL is just a language, but it's also a very popular language that's rich and expressive. Thus, it definitely seems like a good idea to use some dialect of SQL rather than come up with a whole new way of expressing queries that we would need to learn if you wanted to get documents out of your database.

• SQL is designed for relational databases, and DocumentDB is a non-relational document database. DocumentDB team has actually adapted the SQL syntax for the non-relational world of document databases, and this is what is meant by rooting SQL in JSON and JavaScript.

• The language still reads as familiar SQL, but the semantics are all based on schemafree JSON documents rather than relational tables. In DocumentDB, we will be working with JavaScript data types rather than SQL data types. We will be familiar with SELECT, FROM, WHERE, and so on, but with JavaScript types, which are limited to numbers and strings, objects, arrays, Boolean, and null are far fewer than the wide range of SQL data types.

• Similarly, expressions are evaluated as JavaScript expressions rather than some form of T-SQL. For example, in a world of denormalized data, we're not dealing with the rows and columns, but schema-free documents with hierarchal structures that contain nested arrays and objects.

How does SQL Work?

The DocumentDB team has answered this question in several innovative ways. Few of them are listed as follows −

• First, assuming you've not changed the default behavior to automatically index every property in a document, you can use dotted notation in your queries to navigate a path to any property no matter how deeply nested it may be within the document.

• You can also perform an intra-document join in which nested array elements are joined with their parent element within a document in a manner very similar to the way a join is performed between two tables in the relational world.

• Your queries can return documents from the database as it is, or you can project any custom JSON shape you want based on as much or as little of the document data that you want.

• SQL in DocumentDB supports many of the common operators including −

  • Arithmetic and bitwise operations

  • AND and OR logic

  • Equality and range comparisons

  • String concatenation

• The query language also supports a host of built-in functions.