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.

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

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.

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"
]