DocumentDB is Microsoft”s newest NoSQL document database platform that runs on Azure. DocumentDB is designed keeping in mind the requirements of managing data for latest applications. This tutorial talks about querying documents using the special version of SQL supported by DocumentDB with illustrative examples.

Composite Query enables you to combine data from existing queries and then apply filters, aggregates, and so on before presenting the report results, which show the combined data set. Composite Query retrieves multiple levels of related information on existing queries and presents the combined data as a single and flattened query result.

Using Composite Query, you also have the option to −

• Select the SQL pruning option to remove tables and fields that are not needed based on users’ attribute selections.

• Set the ORDER BY and GROUP BY clauses.

• Set the WHERE clause as a filter over the result set of a composite query.

The above operators can be composed to form more powerful queries. Since DocumentDB supports nested collections, the composition can either be concatenated or nested.

Let’s consider the following documents for this example.

AndersenFamily document is as follows.

{
   "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
}

SmithFamily document is as follows.

{
   "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
}

WakefieldFamily document is as follows.

{
   "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 an example of concatenated query.

Following is the query which will retrieve the id and location of the family where the first child givenName is Michelle.

SELECT f.id,f.location
FROM Families f
WHERE f.children[0].givenName = "Michelle"

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

[
   {
      "id": "SmithFamily",
      "location": {
         "state": "NY",
         "county": "Queens",
         "city": "Forest Hills"
      }
   }
]

Let’s consider another example of concatenated query.

Following is the query which will return all the documents where the first child grade greater than 3.

SELECT *
FROM Families f
WHERE ({grade: f.children[0].grade}.grade > 3)

When the above query is executed, it produces 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/"
   },

   {
      "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,
      "_rid": "Ic8LAJFujgEEAAAAAAAAAA==",
      "_ts": 1450541624,
      "_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgEEAAAAAAAAAA==/",
      "_etag": ""00000700-0000-0000-0000-567582380000"",
      "_attachments": "attachments/"
   }
]

Let’s take a look at an example of nested queries.

Following is the query which will iterate all the parents and then return the document where familyName is Smith.

SELECT *
FROM p IN Families.parents
WHERE p.familyName = "Smith"

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

[
   {
      "familyName": "Smith",
      "givenName": "James"
   }
]

Let’s consider another example of nested query.

Following is the query which will return all the familyName.

SELECT VALUE p.familyName
FROM Families f
JOIN p IN f.parents

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

[
   "Wakefield",
   "Miller",
   "Smith",
   "Curtis"
]

These days JavaScript is everywhere, and not just in browsers. DocumentDB embraces JavaScript as a sort of modern day T-SQL and supports the transactional execution of JavaScript logic natively, right inside the database engine. DocumentDB provides a programming model for executing JavaScript-based application logic directly on the collections in terms of stored procedures and triggers.

Let’s take a look at an example where we create a simple store procedure. Following are the steps −

Step 1 − Create a new console applications.

Step 2 − Add in the .NET SDK from NuGet. We are using the .NET SDK here, which means that we”ll be writing some C# code to create, execute, and then delete our stored procedure, but the stored procedure itself gets written in JavaScript.

Step 3 − Right-click on the project in Solution explorer.

Step 4 − Add a new JavaScript file for the stored procedure and call it HelloWorldStoreProce.js

Every stored procedure is just a JavaScript function so we”ll create a new function and naturally we”ll also name this function HelloWorldStoreProce. It doesn”t matter if we give the function a name at all. DocumentDB will only refer to this stored procedure by the Id that we provide when we create it.

function HelloWorldStoreProce() {
   var context = getContext();
   var response = context.getResponse();
   response.setBody(''Hello, and welcome to DocumentDB!'');
}

All the stored procedure does is obtain the response object from the context and call its setBody method to return a string to the caller. In C# code, we will create the stored procedure, execute it, and then delete it.

Stored procedures are scoped per collection, therefore we will need the SelfLink of the collection to create the stored procedure.

Step 5 − First query for the myfirstdb database and then for the MyCollection collection.

Creating a stored procedure is just like creating any other resource in DocumentDB.

private async static Task SimpleStoredProcDemo() {
   var endpoint = "https://azuredocdbdemo.documents.azure.com:443/";
   var masterKey =
      "BBhjI0gxdVPdDbS4diTjdloJq7Fp4L5RO/StTt6UtEufDM78qM2CtBZWbyVwFPSJIm8AcfDu2O+AfV T+TYUnBQ==";

   using (var client = new DocumentClient(new Uri(endpoint), masterKey)) {
      // Get database
      Database database = client
         .CreateDatabaseQuery("SELECT * FROM c WHERE c.id = ''myfirstdb''")
         .AsEnumerable()
         .First();

      // Get collection
      DocumentCollection collection = client
         .CreateDocumentCollectionQuery(database.CollectionsLink, "SELECT * FROM
         c WHERE c.id = ''MyCollection''")
         .AsEnumerable()
         .First();

      // Create stored procedure
      var sprocBody = File.ReadAllText(@"....HelloWorldStoreProce.js");

      var sprocDefinition = new StoredProcedure {
         Id = "HelloWorldStoreProce",
         Body = sprocBody
      };

      StoredProcedure sproc = await client.
         CreateStoredProcedureAsync(collection.SelfLink, sprocDefinition);
      Console.WriteLine("Created stored procedure {0} ({1})",
         sproc.Id, sproc.ResourceId);

      // Execute stored procedure
      var result = await client.ExecuteStoredProcedureAsync(sproc.SelfLink);
      Console.WriteLine("Executed stored procedure; response = {0}", result.Response);

      // Delete stored procedure
      await client.DeleteStoredProcedureAsync(sproc.SelfLink);
      Console.WriteLine("Deleted stored procedure {0} ({1})",
         sproc.Id, sproc.ResourceId);
   }
}

Step 6 − First create a definition object with the Id for the new resource and then call one of the Create methods on the DocumentClient object. In the case of a stored procedure, the definition includes the Id and the actual JavaScript code that you want to ship over to the server.

Step 7 − Call File.ReadAllText to extract the stored procedure code out of the JS file.

Step 8 − Assign the stored procedure code to the body property of the definition object.

As far as DocumentDB is concerned, the Id we specify here, in the definition, is the name of the stored procedure, regardless of what we actually name the JavaScript function.

Nevertheless when creating stored procedures and other server-side objects, it is recommended that we name JavaScript functions and that those function names do match the Id that we have set in the definition for DocumentDB.

Step 9 − Call CreateStoredProcedureAsync, passing in the SelfLink for the MyCollection collection and the stored procedure definition. This creates the stored procedure and ResourceId that DocumentDB assigned to it.

Step 10 − Call the stored procedure. ExecuteStoredProcedureAsync takes a type parameter that you set to the expected data type of the value returned by the stored procedure, which you can specify simply as an object if you want a dynamic object returned. That is an object whose properties will be bound at run-time.

In this example we know that our stored procedure is just returning a string and so we call ExecuteStoredProcedureAsync<string>.

Following is the complete implementation of Program.cs file.

using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Microsoft.Azure.Documents.Linq;

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;

using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DocumentDBStoreProce {
   class Program {
      private static void Main(string[] args) {
         Task.Run(async () => {
            await SimpleStoredProcDemo();
         }).Wait();
      }

      private async static Task SimpleStoredProcDemo() {
         var endpoint = "https://azuredocdbdemo.documents.azure.com:443/";
         var masterKey =
            "BBhjI0gxdVPdDbS4diTjdloJq7Fp4L5RO/StTt6UtEufDM78qM2CtBZWbyVwFPSJIm8AcfDu2O+AfV T+TYUnBQ==";

         using (var client = new DocumentClient(new Uri(endpoint), masterKey)) {
            // Get database
            Database database = client
               .CreateDatabaseQuery("SELECT * FROM c WHERE c.id = ''myfirstdb''")
               .AsEnumerable()
               .First();

            // Get collection
            DocumentCollection collection = client
               .CreateDocumentCollectionQuery(database.CollectionsLink,
               "SELECT * FROM c WHERE c.id = ''MyCollection''")
               .AsEnumerable()
               .First();

            // Create stored procedure
            var sprocBody = File.ReadAllText(@"....HelloWorldStoreProce.js");

            var sprocDefinition = new StoredProcedure {
               Id = "HelloWorldStoreProce",
               Body = sprocBody
            };

            StoredProcedure sproc = await client
               .CreateStoredProcedureAsync(collection.SelfLink, sprocDefinition);

            Console.WriteLine("Created stored procedure {0} ({1})", sproc
               .Id, sproc.ResourceId);

            // Execute stored procedure
            var result = await client
               .ExecuteStoredProcedureAsync<string>(sproc.SelfLink);
            Console.WriteLine("Executed stored procedure; response = {0}",
               result.Response);

            // Delete stored procedure
            await client.DeleteStoredProcedureAsync(sproc.SelfLink);
            Console.WriteLine("Deleted stored procedure {0} ({1})",
               sproc.Id, sproc.ResourceId);
         }
      }
   }
}

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

Created stored procedure HelloWorldStoreProce (Ic8LAMEUVgACAAAAAAAAgA==)

Executed stored procedure; response = Hello, and welcome to DocumentDB!

As seen in the above output, the response property has the “Hello, and welcome to DocumentDB!” returned by our stored procedure.

DocumentDB SQL provides support for User-Defined Functions (UDFs). UDFs are just another kind of JavaScript functions you can write and these work pretty much as you”d expect. You can create UDFs to extend the query language with custom business logic that you can reference in your queries.

The DocumentDB SQL syntax is extended to support custom application logic using these UDFs. UDFs can be registered with DocumentDB and then be referenced as part of a SQL query.

Let’s consider the following three documents for this example.

AndersenFamily document is as follows.

{
   "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
}

SmithFamily document is as follows.

{
   "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
}

WakefieldFamily document is as follows.

{
   "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 an example where we will create some simple UDFs.

Following is the implementation of CreateUserDefinedFunctions.

private async static Task CreateUserDefinedFunctions(DocumentClient client) {
   Console.WriteLine();
   Console.WriteLine("**** Create User Defined Functions ****");
   Console.WriteLine();

   await CreateUserDefinedFunction(client, "udfRegEx");
}

We have a udfRegEx, and in CreateUserDefinedFunction we get its JavaScript code from our local file. We construct the definition object for the new UDF, and call CreateUserDefinedFunctionAsync with the collection”s SelfLink and the udfDefinition object as shown in the following code.

private async static Task<UserDefinedFunction>
CreateUserDefinedFunction(DocumentClient client, string udfId) {
   var udfBody = File.ReadAllText(@"....Server" + udfId + ".js");

   var udfDefinition = new UserDefinedFunction {
      Id = udfId,
      Body = udfBody
   };

   var result = await client
      .CreateUserDefinedFunctionAsync(_collection.SelfLink, udfDefinition);
   var udf = result.Resource;

   Console.WriteLine("Created user defined function {0}; RID: {1}",
      udf.Id, udf.ResourceId);

   return udf;
}

We get back the new UDF from the resource property of the result and return it back up to the caller. To display the existing UDF, following is the implementation of ViewUserDefinedFunctions. We call CreateUserDefinedFunctionQuery and loop through them as usual.

private static void ViewUserDefinedFunctions(DocumentClient client) {
   Console.WriteLine();
   Console.WriteLine("**** View UDFs ****");
   Console.WriteLine();

   var udfs = client
      .CreateUserDefinedFunctionQuery(_collection.UserDefinedFunctionsLink)
      .ToList();

   foreach (var udf in udfs) {
      Console.WriteLine("User defined function {0}; RID: {1}", udf.Id, udf.ResourceId);
   }
}

DocumentDB SQL doesn”t provide built-in functions to search for substrings or for regular expressions, hence the following little one-liner fills that gap which is a JavaScript function.

function udfRegEx(input, regex) {
   return input.match(regex);
}

Given the input string in the first parameter, use JavaScript”s built-in regular expression support passing in the pattern matching string in the second parameter into .match. We can run a substring query to find all stores with the word Andersen in their lastName property.

private static void Execute_udfRegEx(DocumentClient client) {
   var sql = "SELECT c.name FROM c WHERE udf.udfRegEx(c.lastName, ''Andersen'') != null";

   Console.WriteLine();
   Console.WriteLine("Querying for Andersen");

   var documents = client.CreateDocumentQuery(_collection.SelfLink, sql).ToList();
   Console.WriteLine("Found {0} Andersen:", documents.Count);

   foreach (var document in documents) {
      Console.WriteLine("Id: {0}, Name: {1}", document.id, document.lastName);
   }
}

Note that we must qualify every UDF reference with the prefix udf. We just passed the SQL along to CreateDocumentQuery like any ordinary query. Finally, let’s call the above queries from the CreateDocumentClient task

private static async Task CreateDocumentClient() {
   // Create a new instance of the DocumentClient

   using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)){
      database = client.CreateDatabaseQuery("SELECT * FROM c WHERE
         c.id = ''myfirstdb''").AsEnumerable().First();
      collection = client.CreateDocumentCollectionQuery(database.CollectionsLink,
         "SELECT * FROM c WHERE c.id = ''Families''").AsEnumerable().First();

      await CreateUserDefinedFunctions(client);

      ViewUserDefinedFunctions(client);

      Execute_udfRegEx(client);
   }
}

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

**** Create User Defined Functions ****
Created user defined function udfRegEx; RID: kV5oANVXnwAlAAAAAAAAYA==
**** View UDFs ****
User defined function udfRegEx; RID: kV5oANVXnwAlAAAAAAAAYA==
Querying for Andersen
Found 1 Andersen:
 Id: AndersenFamily, Name: Andersen

In DocumentDB SQL, Microsoft has added a key feature with the help of which we can easily create an array. It means when we run a query, then as a result it will create an array of collection similar to JSON object as a result of query.

Let’s 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 an example.

Following is the query which will return the family name and address of each family.

SELECT f.id AS FamilyName,
[f.location.city, f.location.county, f.location.state] AS Address
FROM Families f

As can be seen city, county and state fields are enclosed in square brackets, which will create an array and this array is named Address. When the above query is executed, it produces the following output.

[
   {
      "FamilyName": "WakefieldFamily",
      "Address": [
         "NY",
         "Manhattan",
         "NY"
      ]
   },

   {
      "FamilyName": "SmithFamily",
      "Address": [
         "Forest Hills",
         "Queens",
         "NY"
      ]
   },

   {
      "FamilyName": "AndersenFamily",
      "Address": [
         "Seattle",
         "King",
         "WA"
      ]
   }
]

The city, county, and state information are added in the Address array in the above output.

In DocumentDB SQL, the SELECT clause also supports scalar expressions like constants, arithmetic expressions, logical expressions, etc. Normally, scalar queries are rarely used, because they don”t actually query documents in the collection, they just evaluate expressions. But it”s still helpful to use scalar expression queries to learn the basics, how to use expressions and shape JSON in a query, and these concepts apply directly to the actual queries you”ll be running against documents in a collection.

Let’s take a look at an example which contains multiple scalar queries.

In the Query Explorer, select just the text to be executed and click ‘Run’. Let”s run this first one.

SELECT "Hello"

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

[
   {
      "$1": "Hello"
   }
]

This output may look a bit confusing, so let”s break it down.

• First, as we saw in the last demo, query results are always contained in square brackets because they are returned as a JSON array, even results from scalar expression queries like this one that only returns a single document.

• We have an array with one document in it, and that document has a single property in it for the single expression in the SELECT statement.

• The SELECT statement doesn”t provide a name for this property, thus DocumentDB auto generates one using $1.

• This is usually not what we want, which is why we can use AS to alias the expression in the query, which sets the property name in the generated document the way you”d like it to be, word, in this example.

SELECT "Hello" AS word

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

[
   {
      "word": "Hello"
   }
]

Similarly, following is another simple query.

SELECT ((2 + 11 % 7)-2)/3

The query retrieves the following output.

[
   {
      "$1": 1.3333333333333333
   }
]

Let’s take a look at another example of shaping nested arrays and embedded objects.

SELECT
   {
      "words1":
         ["Hello", "World"],
      "words2":
         ["How", "Are", "You?"]
   } AS allWords

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

[
   {
      "allWords": {
         "words1": [
            "Hello",
            "World"
         ],

         "words2": [
            "How",
            "Are",
            "You?"
         ]
      }
   }
]

DocumentDB supports a host of built-in functions for common operations that can be used inside queries. There are a bunch of functions for performing mathematical calculations, and also type checking functions that are extremely useful while working with varying schemas. These functions can test if a certain property exists and if it does whether it”s a number or a string, Boolean or object.

We also get these handy functions for parsing and manipulating strings, as well as several functions for working with arrays allowing you to do things like concatenate arrays and test to see if an array contains a particular element.

Following are the different types of built-in functions −

In DocumentDB, we actually use SQL to query documents. If we are doing .NET development, there is also a LINQ provider that can be used and which can generate appropriate SQL from a LINQ query.

Supported Data Types

In DocumentDB, all JSON primitive types are supported in the LINQ provider included with the DocumentDB .NET SDK which are as follows −

• Numeric
• Boolean
• String
• Null

Supported Expression

The following scalar expressions are supported in the LINQ provider included with the DocumentDB .NET SDK.

• Constant Values − Includes constant values of the primitive data types.

• Property/Array Index Expressions − Expressions refer to the property of an object or an array element.

• Arithmetic Expressions − Includes common arithmetic expressions on numerical and Boolean values.

• String Comparison Expression − Includes comparing a string value to some constant string value.

• Object/Array Creation Expression − Returns an object of compound value type or anonymous type or an array of such objects. These values can be nested.

Supported LINQ Operators

Here is a list of supported LINQ operators in the LINQ provider included with the DocumentDB .NET SDK.

• Select − Projections translate to the SQL SELECT including object construction.

• Where − Filters translate to the SQL WHERE, and support translation between && , || and ! to the SQL operators.

• SelectMany − Allows unwinding of arrays to the SQL JOIN clause. Can be used to chain/nest expressions to filter array elements.

• OrderBy and OrderByDescending − Translates to ORDER BY ascending/descending.

• CompareTo − Translates to range comparisons. Commonly used for strings since they’re not comparable in .NET.

• Take − Translates to the SQL TOP for limiting results from a query.

• Math Functions − Supports translation from .NET’s Abs, Acos, Asin, Atan, Ceiling, Cos, Exp, Floor, Log, Log10, Pow, Round, Sign, Sin, Sqrt, Tan, Truncate to the equivalent SQL built-in functions.

• String Functions − Supports translation from .NET’s Concat, Contains, EndsWith, IndexOf, Count, ToLower, TrimStart, Replace, Reverse, TrimEnd, StartsWith, SubString, ToUpper to the equivalent SQL built-in functions.

• Array Functions − Supports translation from .NET’s Concat, Contains, and Count to the equivalent SQL built-in functions.

• Geospatial Extension Functions − Supports translation from stub methods Distance, Within, IsValid, and IsValidDetailed to the equivalent SQL built-in functions.

• User-Defined Extension Function − Supports translation from the stub method UserDefinedFunctionProvider.Invoke to the corresponding user-defined function.

• Miscellaneous − Supports translation of coalesce and conditional operators. Can translate Contains to String CONTAINS, ARRAY_CONTAINS or the SQL IN depending on context.

Let’s take a look at an example where we will be using the .Net SDK. Following are the three documents which we will be consider for this example.

New Customer 1

{
   "name": "New Customer 1",
   "address": {
      "addressType": "Main Office",
      "addressLine1": "123 Main Street",

      "location": {
         "city": "Brooklyn",
         "stateProvinceName": "New York"
      },

      "postalCode": "11229",
      "countryRegionName": "United States"
   },
}

New Customer 2

{
   "name": "New Customer 2",

   "address": {
      "addressType": "Main Office",
      "addressLine1": "678 Main Street",

      "location": {
         "city": "London",
         "stateProvinceName": " London "
      },

      "postalCode": "11229",
      "countryRegionName": "United Kingdom"
   },
}

New Customer 3

{
   "name": "New Customer 3",

   "address": {
      "addressType": "Main Office",
      "addressLine1": "12 Main Street",

      "location": {
         "city": "Brooklyn",
         "stateProvinceName": "New York"
      },

      "postalCode": "11229",
      "countryRegionName": "United States"
   },
}

Following is the code in which we query using LINQ. We”ve defined a LINQ query in q, but it won”t execute until we run .ToList on it.

private static void QueryDocumentsWithLinq(DocumentClient client) {
   Console.WriteLine();
   Console.WriteLine("**** Query Documents (LINQ) ****");
   Console.WriteLine();
   Console.WriteLine("Quering for US customers (LINQ)");
   var q =
      from d in client.CreateDocumentQuery<Customer>(collection.DocumentsLink)
      where d.Address.CountryRegionName == "United States"

   select new {
      Id = d.Id,
      Name = d.Name,
      City = d.Address.Location.City
   };

   var documents = q.ToList();
   Console.WriteLine("Found {0} US customers", documents.Count);

   foreach (var document in documents) {
      var d = document as dynamic;
      Console.WriteLine(" Id: {0}; Name: {1}; City: {2}", d.Id, d.Name, d.City);
   }

   Console.WriteLine();
}

The SDK will convert our LINQ query into SQL syntax for DocumentDB, generating a SELECT and WHERE clause based on our LINQ syntax.

Let’s call the above queries from the CreateDocumentClient task.

private static async Task CreateDocumentClient() {
   // Create a new instance of the DocumentClient
   using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)) {
      database = client.CreateDatabaseQuery("SELECT * FROM c WHERE c.id =
         ''myfirstdb''").AsEnumerable().First();
      collection = client.CreateDocumentCollectionQuery(database.CollectionsLink,
         "SELECT * FROM c WHERE c.id = ''MyCollection''").AsEnumerable().First();
      QueryDocumentsWithLinq(client);
   }
}

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

**** Query Documents (LINQ) ****

Quering for US customers (LINQ)
Found 2 US customers
   Id: 7e9ad4fa-c432-4d1a-b120-58fd7113609f; Name: New Customer 1; City: Brooklyn
   Id: 34e9873a-94c8-4720-9146-d63fb7840fad; Name: New Customer 1; City: Brooklyn

In relational databases, a parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. DocumentDB also supports parameterized queries, and parameters in parameterized query can be expressed with the familiar @ notation. The most important reason to use parameterized queries is to avoid SQL injection attacks. It can also provide robust handling and escaping of user input.

Let’s take a look at an example where we will be using the .Net SDK. Following is the code which will delete the collection.

private async static Task DeleteCollection(DocumentClient client, string collectionId) {
   Console.WriteLine();
   Console.WriteLine(">>> Delete Collection {0} in {1} <<<",
   collectionId, _database.Id);
   var query = new SqlQuerySpec {
      QueryText = "SELECT * FROM c WHERE c.id = @id",
      Parameters = new SqlParameterCollection { new SqlParameter { Name =
         "@id", Value = collectionId } }
   };

   DocumentCollection collection = client.CreateDocumentCollectionQuery(database.SelfLink,
      query).AsEnumerable().First();

   await client.DeleteDocumentCollectionAsync(collection.SelfLink);

   Console.WriteLine("Deleted collection {0} from database {1}",
      collectionId, _database.Id);
}

The construction of a parameterized query is as follows.

var query = new SqlQuerySpec {
   QueryText = "SELECT * FROM c WHERE c.id = @id",
   Parameters = new SqlParameterCollection { new SqlParameter { Name =
      "@id", Value = collectionId } }
};

We are not hardcoding the collectionId so this method can be used to delete any collection. We can use ‘@’ symbol to prefix parameter names, similar to SQL Server.

In the above example, we are querying for a specific collection by Id where the Id parameter is defined in this SqlParameterCollection assigned to the parameter”s property of this SqlQuerySpec. The SDK then does the work of constructing the final query string for DocumentDB with the collectionId embedded inside of it. We run the query and then use its SelfLink to delete the collection.

Following is the CreateDocumentClient task implementation.

private static async Task CreateDocumentClient() {
   // Create a new instance of the DocumentClient
   using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)) {
      database = client.CreateDatabaseQuery("SELECT * FROM
         c WHERE c.id = ''earthquake''").AsEnumerable().First();

      collection = client.CreateDocumentCollectionQuery(database.CollectionsLink,
         "SELECT * FROM c WHERE c.id = ''myfirstdb''").AsEnumerable().First();

      await DeleteCollection(client, "MyCollection1");
      await DeleteCollection(client, "MyCollection2");
   }
}

When the code is executed, it produces the following output.

**** Delete Collection MyCollection1 in mydb ****
Deleted collection MyCollection1 from database myfirstdb

**** Delete Collection MyCollection2 in mydb ****
Deleted collection MyCollection2 from database myfirstdb

Let’s take a look at another example. We can write a query that takes last name and address state as parameters, and then executes it for various values of lastname and location.state based on the user input.

SELECT *
FROM Families f
WHERE f.lastName = @lastName AND f.location.state = @addressState

This request can then be sent to DocumentDB as a parameterized JSON query as shown in the following code.

{
   "query": "SELECT * FROM Families f WHERE f.lastName = @lastName AND
      f.location.state = @addressState",

   "parameters": [
      {"name": "@lastName", "value": "Wakefield"},
      {"name": "@addressState", "value": "NY"},
   ]
}

In relational databases, the Joins clause is used to combine records from two or more tables in a database, and the need to join across tables is very important while designing normalized schemas. Since DocumentDB deals with the denormalized data model of schema-free documents, the JOIN in DocumentDB SQL is the logical equivalent of a “selfjoin”.

Let’s consider the three 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 an example to understand how the JOIN clause works.

Following is the query which will join the root to children subdocument.

SELECT f.id
FROM Families f
JOIN c IN f.children

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

[
   {
      "id": "WakefieldFamily"
   },

   {
      "id": "WakefieldFamily"
   },

   {
      "id": "SmithFamily"
   },

   {
      "id": "SmithFamily"
   },

   {
      "id": "AndersenFamily"
   }
]

In the above example, the join is between the document root and the children sub-root which makes a cross-product between two JSON objects. Following are certain points to note −

• In the FROM clause, the JOIN clause is an iterator.

• The first two documents WakefieldFamily and SmithFamily contain two children, hence the result set also contains the cross-product which produces a separate object for each child.

• The third document AndersenFamily contains only one children, hence there is only a single object corresponding to this document.

Let’s take a look at the same example, however this time we retrieve the child name as well for better understanding of JOIN clause.

Following is the query which will join the root to children subdocument.

SELECT
   f.id AS familyName,
   c.givenName AS childGivenName,
   c.firstName AS childFirstName
FROM Families f
JOIN c IN f.children

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

[
   {
      "familyName": "WakefieldFamily",
      "childGivenName": "Jesse"
   },

   {
      "familyName": "WakefieldFamily",
      "childGivenName": "Lisa"
   },

   {
      "familyName": "SmithFamily",
      "childGivenName": "Michelle"
   },

   {
      "familyName": "SmithFamily",
      "childGivenName": "John"
   },

   {
      "familyName": "AndersenFamily",
      "childFirstName": "Henriette Thaulow"
   }
]