An operator is a reserved word or a character used primarily in an SQL WHERE clause to perform operation(s), such as comparisons and arithmetic operations. DocumentDB SQL also supports a variety of scalar expressions. The most commonly used are binary and unary expressions.

The following SQL operators are currently supported and can be used in queries.

SQL Comparison Operators

Following is a list of all the comparison operators available in DocumentDB SQL grammar.

SQL Logical Operators

Following is a list of all the logical operators available in DocumentDB SQL grammar.

SQL Arithmetic Operators

Following is a list of all the arithmetic operators available in DocumentDB SQL grammar.

We will consider the same documents in this example as well. Following is the AndersenFamily document.

{
   "id": "AndersenFamily",
   "lastName": "Andersen",

   "parents": [
      { "firstName": "Thomas", "relationship":  "father" },
      { "firstName": "Mary Kay", "relationship":  "mother" }
   ],

   "children": [
      {
         "firstName": "Henriette Thaulow",
         "gender": "female",
         "grade": 5,
         "pets": [ { "givenName": "Fluffy", "type":  "Rabbit" } ]
      }
   ],

   "location": { "state": "WA", "county": "King", "city": "Seattle" },
   "isRegistered": true
}

Following is the SmithFamily document.

{
   "id": "SmithFamily",

   "parents": [
      { "familyName": "Smith", "givenName": "James" },
      { "familyName": "Curtis", "givenName": "Helen" }
   ],

   "children": [
      {
         "givenName": "Michelle",
         "gender": "female",
         "grade": 1
      },

      {
         "givenName": "John",
         "gender": "male",
         "grade": 7,

         "pets": [
            { "givenName": "Tweetie", "type": "Bird" }
         ]
      }
   ],

   "location": {
      "state": "NY",
      "county": "Queens",
      "city": "Forest Hills"
   },

   "isRegistered": true
}

Following is the WakefieldFamily document.

{
   "id": "WakefieldFamily",

   "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
   ],

   "children": [
      {
         "familyName": "Merriam",
         "givenName": "Jesse",
         "gender": "female",
         "grade": 6,

         "pets": [
            { "givenName": "Charlie Brown", "type": "Dog" },
            { "givenName": "Tiger", "type": "Cat" },
            { "givenName": "Princess", "type": "Cat" }
         ]
      },

      {
         "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 3,

         "pets": [
            { "givenName": "Jake", "type": "Snake" }
         ]
      }
   ],

   "location": { "state": "NY", "county": "Manhattan", "city": "NY" },
   "isRegistered": false
}

Let’s take a look at a simple example in which a comparison operator is used in WHERE clause.

In this query, in WHERE clause, the (WHERE f.id = “WakefieldFamily”) condition is specified, and it will retrieve the document whose id is equal to WakefieldFamily.

SELECT *
FROM f
WHERE f.id = "WakefieldFamily"

When the above query is executed, it will return the complete JSON document for WakefieldFamily as shown in the following output.

[
   {
      "id": "WakefieldFamily",
      "parents": [
         {
            "familyName": "Wakefield",
            "givenName": "Robin"
         },

         {
            "familyName": "Miller",
            "givenName": "Ben"
         }
      ],

      "children": [
         {
            "familyName": "Merriam",
            "givenName": "Jesse",
            "gender": "female",
            "grade": 6,

            "pets": [
               {
                  "givenName": "Charlie Brown",
                  "type": "Dog"
               },

               {
                  "givenName": "Tiger",
                  "type": "Cat"
               },

               {
                  "givenName": "Princess",
                  "type": "Cat"
               }
            ]

         },

         {
            "familyName": "Miller",
            "givenName": "Lisa",
            "gender": "female",
            "grade": 3,

            "pets": [
               {
                  "givenName": "Jake",
                  "type": "Snake"
               }
            ]
         }
      ],

      "location": {
         "state": "NY",
         "county": "Manhattan",
         "city": "NY"
      },

      "isRegistered": false,
      "_rid": "Ic8LAJFujgECAAAAAAAAAA==",
      "_ts": 1450541623,
      "_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgECAAAAAAAAAA==/",
      "_etag": ""00000500-0000-0000-0000-567582370000"",
      "_attachments": "attachments/"
   }
]

Let’s take a look at another example in which the query will retrieve the children data whose grade is greater than 5.

SELECT *
FROM Families.children[0] c
WHERE (c.grade > 5)

When the above query is executed, it will retrieve the following sub document as shown in the output.

[
   {
      "familyName": "Merriam",
      "givenName": "Jesse",
      "gender": "female",
      "grade": 6,

      "pets": [
         {
            "givenName": "Charlie Brown",
            "type": "Dog"
         },

         {
            "givenName": "Tiger",
            "type": "Cat"
         },

         {
            "givenName": "Princess",
            "type": "Cat"
         }
      ]
   }
]

Microsoft Azure DocumentDB supports querying documents using SQL over JSON documents. You can sort documents in the collection on numbers and strings using an ORDER BY clause in your query. The clause can include an optional ASC/DESC argument to specify the order in which results must be retrieved.

We will consider the same documents as in the previous examples.

Following is the AndersenFamily document.

{
   "id": "AndersenFamily",
   "lastName": "Andersen",

   "parents": [
      { "firstName": "Thomas", "relationship":  "father" },
      { "firstName": "Mary Kay", "relationship":  "mother" }
   ],

   "children": [
      {
         "firstName": "Henriette Thaulow",
         "gender": "female",
         "grade": 5,
         "pets": [ { "givenName": "Fluffy", "type":  "Rabbit" } ]
      }
   ],

   "location": { "state": "WA", "county": "King", "city": "Seattle" },
   "isRegistered": true
}

Following is the SmithFamily document.

{
   "id": "SmithFamily",

   "parents": [
      { "familyName": "Smith", "givenName": "James" },
      { "familyName": "Curtis", "givenName": "Helen" }
   ],

   "children": [
      {
         "givenName": "Michelle",
         "gender": "female",
         "grade": 1
      },

      {
         "givenName": "John",
         "gender": "male",
         "grade": 7,

         "pets": [
            { "givenName": "Tweetie", "type": "Bird" }
         ]
      }
   ],

   "location": {
      "state": "NY",
      "county": "Queens",
      "city": "Forest Hills"
   },

   "isRegistered": true
}

Following is the WakefieldFamily document.

{
   "id": "WakefieldFamily",

   "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
   ],

   "children": [
      {
         "familyName": "Merriam",
         "givenName": "Jesse",
         "gender": "female",
         "grade": 6,

         "pets": [
            { "givenName": "Charlie Brown", "type": "Dog" },
            { "givenName": "Tiger", "type": "Cat" },
            { "givenName": "Princess", "type": "Cat" }
         ]
      },

      {
         "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 3,

         "pets": [
            { "givenName": "Jake", "type": "Snake" }
         ]
      }
   ],

   "location": { "state": "NY", "county": "Manhattan", "city": "NY" },
   "isRegistered": false
}

Let’s take a look at a simple example.

Following is the query which contains the ORDER BY keyword.

SELECT  f.id, f.children[0].givenName,f.children[0].grade
FROM Families f
ORDER BY f.children[0].grade

When the above query is executed, it produces the following output.

[
   {
      "id": "SmithFamily",
      "givenName": "Michelle",
      "grade": 1
   },

   {
      "id": "AndersenFamily",
      "grade": 5
   },

   {
      "id": "WakefieldFamily",
      "givenName": "Jesse",
      "grade": 6
   }
]

Let’s consider another simple example.

Following is the query which contains the ORDER BY keyword and DESC optional keyword.

SELECT f.id, f.parents[0].familyName
FROM Families f
ORDER BY f.parents[0].familyName DESC

When the above query is executed, it will produce the following output.

[
   {
      "id": "WakefieldFamily",
      "familyName": "Wakefield"
   },

   {
      "id": "SmithFamily",
      "familyName": "Smith"
   },

   {
      "id": "AndersenFamily"
   }
]

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.