edu Toidayhoc

Author: alien

  • Khóa học miễn phí DocumentDB – Create Collection nhận dự án làm có lương

    DocumentDB – Create Collection

    In this chapter, we will learn how to create a collection. It is similar to creating a database. You can create a collection either from the portal or from the code using .Net SDK.

    Step 1 − Go to main dashboard on Azure portal.

    Create Collection

    Step 2 − Select myfirstdb from the databases list.

    myfirstdb

    Step 3 − Click on the ‘Add Collection’ option and specify the ID for collection. Select the Pricing Tier for different option.

    Add Collection

    Step 4 − Let’s select S1 Standard and click Select → OK button.

    select S1 Standard

    As you can see that MyCollection is added to the myfirstdb.

    You can also create collection from the code by using .Net SDK. Let’s have a look at the following steps to add collections from the code.

    Step 1 − Open the Console application in Visual Studio.

    Step 2 − To create a collection, first retrieve the myfirstdb database by its ID in 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();

      await CreateCollection(client, "MyCollection1");
      await CreateCollection(client, "MyCollection2", "S2");
   }
}

    Following is the implementation for CreateCollection task.

    private async static Task CreateCollection(DocumentClient client, string collectionId,
   string offerType = "S1") {

   Console.WriteLine();
   Console.WriteLine("**** Create Collection {0} in {1} ****", collectionId, database.Id);

   var collectionDefinition = new DocumentCollection { Id = collectionId };
   var options = new RequestOptions { OfferType = offerType };
   var result = await client.CreateDocumentCollectionAsync(database.SelfLink,
      collectionDefinition, options);
   var collection = result.Resource;

   Console.WriteLine("Created new collection");
   ViewCollection(collection);
}

    We create a new DocumentCollection object that defines the new collection with the desired Id for the CreateDocumentCollectionAsync method which also accepts an options parameter that we”re using here to set the performance tier of the new collection, which we”re calling offerType.

    This defaults to S1 and since we didn”t pass in an offerType, for MyCollection1, so this will be an S1 collection and for MyCollection2 we have passed S2 which make this one an S2 as shown above.

    Following is the implementation of the ViewCollection method.

    private static void ViewCollection(DocumentCollection collection) {
   Console.WriteLine("Collection ID: {0} ", collection.Id);
   Console.WriteLine("Resource ID: {0} ", collection.ResourceId);
   Console.WriteLine("Self Link: {0} ", collection.SelfLink);
   Console.WriteLine("Documents Link: {0} ", collection.DocumentsLink);
   Console.WriteLine("UDFs Link: {0} ", collection.UserDefinedFunctionsLink);
   Console.WriteLine(" StoredProcs Link: {0} ", collection.StoredProceduresLink);
   Console.WriteLine("Triggers Link: {0} ", collection.TriggersLink);
   Console.WriteLine("Timestamp: {0} ", collection.Timestamp);
}

    Following is the complete implementation of program.cs file for collections.

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

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

using Newtonsoft.Json;

namespace DocumentDBDemo {

   class Program {

      private const string EndpointUrl = "https://azuredocdbdemo.documents.azure.com:443/";

      private const string AuthorizationKey = "BBhjI0gxdVPdDbS4diTjdloJq7Fp4L5RO/
         StTt6UtEufDM78qM2CtBZWbyVwFPSJIm8AcfDu2O+AfV T+TYUnBQ==";

      private static Database database;

      static void Main(string[] args) {
         try {
            CreateDocumentClient().Wait();
         } catch (Exception e) {
            Exception baseException = e.GetBaseException();
            Console.WriteLine("Error: {0}, Message: {1}", e.Message, baseException.Message);
         }
         Console.ReadKey();
      }

      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();
            await CreateCollection(client, "MyCollection1");
            await CreateCollection(client, "MyCollection2", "S2");

            //await CreateDatabase(client);
            //GetDatabases(client);
            //await DeleteDatabase(client);
            //GetDatabases(client);
         }
      }

      private async static Task CreateCollection(DocumentClient client,
         string collectionId, string offerType = "S1") {

         Console.WriteLine();
         Console.WriteLine("**** Create Collection {0} in {1} ****", collectionId,
            database.Id);

         var collectionDefinition = new DocumentCollection { Id = collectionId };
         var options = new RequestOptions { OfferType = offerType };
         var result = await

			client.CreateDocumentCollectionAsync(database.SelfLink,
            collectionDefinition, options);
         var collection = result.Resource;

         Console.WriteLine("Created new collection");
         ViewCollection(collection);
      }

      private static void ViewCollection(DocumentCollection collection) {
         Console.WriteLine("Collection ID: {0} ", collection.Id);
         Console.WriteLine("Resource ID: {0} ", collection.ResourceId);
         Console.WriteLine("Self Link: {0} ", collection.SelfLink);
         Console.WriteLine("Documents Link: {0} ", collection.DocumentsLink);
         Console.WriteLine("UDFs Link: {0} ", collection.UserDefinedFunctionsLink);
         Console.WriteLine("StoredProcs Link: {0} ", collection.StoredProceduresLink);
         Console.WriteLine("Triggers Link: {0} ", collection.TriggersLink);
         Console.WriteLine("Timestamp: {0} ", collection.Timestamp);
      }

   }
}

    When the above code is compiled and executed, you will receive the following output which contains all the information related to collection.

    **** Create Collection MyCollection1 in myfirstdb ****
Created new collection
   Collection ID: MyCollection1
      Resource ID: Ic8LAPPvnAA=
         Self Link: dbs/Ic8LAA==/colls/Ic8LAPPvnAA=/
   Documents Link: dbs/Ic8LAA==/colls/Ic8LAPPvnAA=/docs/
         UDFs Link: dbs/Ic8LAA==/colls/Ic8LAPPvnAA=/udfs/
   StoredProcs Link: dbs/Ic8LAA==/colls/Ic8LAPPvnAA=/sprocs/
      Triggers Link: dbs/Ic8LAA==/colls/Ic8LAPPvnAA=/triggers/
         Timestamp: 12/10/2015 4:55:36 PM

**** Create Collection MyCollection2 in myfirstdb ****
Created new collection
   Collection ID: MyCollection2
      Resource ID: Ic8LAKGHDwE=
         Self Link: dbs/Ic8LAA==/colls/Ic8LAKGHDwE=/
   Documents Link: dbs/Ic8LAA==/colls/Ic8LAKGHDwE=/docs/
         UDFs Link: dbs/Ic8LAA==/colls/Ic8LAKGHDwE=/udfs/
   StoredProcs Link: dbs/Ic8LAA==/colls/Ic8LAKGHDwE=/sprocs/
      Triggers Link: dbs/Ic8LAA==/colls/Ic8LAKGHDwE=/triggers/
         Timestamp: 12/10/2015 4:55:38 PM

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí DocumentDB – Environment Setup nhận dự án làm có lương

    DocumentDB – Environment Setup

    Microsoft provides a free version of Visual Studio which also contains SQL Server and it can be downloaded from

    Installation

    Step 1 − Once downloading is completed, run the installer. The following dialog will be displayed.

    Installer

    Step 2 − Click on the Install button and it will start the installation process.

    Installation Process

    Step 3 − Once the installation process is completed successfully, you will see the following dialog.

    Restart Now

    Step 4 − Close this dialog and restart your computer if required.

    Step 5 − Now open Visual studio from start Menu which will open the below dialog. It will take some time for the first time only for preparation.

    open visual studio

    Once all is done, you will see the main window of Visual Studio.

    Main Window

    Step 6 − Let’s create a new project from File → New → Project.

    Create New Project

    Step 7 − Select Console Application, enter DocumentDBDemo in the Name field and click OK button.

    Step 8 − In solution Explorer, right-click on your project.

    Right click on project

    Step 9 − Select Manage NuGet Packages which will open the following window in Visual Studio and in the Search Online input box, search for DocumentDB Client Library.

    Manage NuGet Packages

    Step 10 − Install the latest version by clicking the install button.

    License Acceptance

    Step 11 − Click “I Accept”. Once installation is done you will see the message in your output window.

    Start Application

    You are now ready to start your application.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí DocumentDB – Create Database nhận dự án làm có lương

    DocumentDB – Create Database

    In this chapter, we will learn how to create a database. To use Microsoft Azure DocumentDB, you must have a DocumentDB account, a database, a collection, and documents. We already have a DocumentDB account, now to create database we have two options −

    • Microsoft Azure Portal or
    • .Net SDK

    Create a Database for DocumentDB using the Microsoft Azure Portal

    To create a database using portal, following are the steps.

    Step 1 − Login to Azure portal and you will see the dashboard.

    Login to portal

    Step 2 − Now click on the created DocumentDB account and you will see the details as shown in the following screenshot.

    click on created DocumentDB

    Step 3 − Select the Add Database option and provide the ID for your database.

    Select Add Database

    Step 4 − Click OK.

    Database added

    You can see that the database is added. At the moment, it has no collection, but we can add collections later which are the containers that will store our JSON documents. Notice that it has both an ID and a Resource ID.

    Create a Database for DocumentDB Using .Net SDK

    To create a database using .Net SDK, following are the steps.

    Step 1 − Open the Console Application in Visual Studio from the last chapter.

    Step 2 − Create the new database by creating a new database object. To create a new database, we only need to assign the Id property, which we are setting to “mynewdb” in a CreateDatabase task.

    private async static Task CreateDatabase(DocumentClient client) {
   Console.WriteLine();
   Console.WriteLine("******** Create Database *******");

   var databaseDefinition = new Database { Id = "mynewdb" };
   var result = await client.CreateDatabaseAsync(databaseDefinition);
   var database = result.Resource;

   Console.WriteLine(" Database Id: {0}; Rid: {1}", database.Id, database.ResourceId);
   Console.WriteLine("******** Database Created *******");
}

    Step 3 − Now pass this databaseDefinition on to CreateDatabaseAsync, and get back a result with a Resource property. All the create object methods return a Resource property that describes the item that was created, which is a database in this case.

    We get the new database object from the Resource property and it is displayed on the Console along with the Resource ID that DocumentDB assigned to it.

    Step 4 − Now call CreateDatabase task from the CreateDocumentClient task after DocumentClient is instantiated.

    using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)) {
   await CreateDatabase(client);
}

    Following is the complete Program.cs file so far.

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

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

namespace DocumentDBDemo {

   class Program {
      private const string EndpointUrl = "https://azuredocdbdemo.documents.azure.com:443/";

      private const string AuthorizationKey = "BBhjI0gxdVPdDbS4diTjdloJq7Fp4L5RO/
         StTt6UtEufDM78qM2CtBZWbyVwFPSJIm8AcfDu2O+AfV T+TYUnBQ==";

      static void Main(string[] args) {
         try {
            CreateDocumentClient().Wait();
         } catch (Exception e) {
            Exception baseException = e.GetBaseException();
            Console.WriteLine("Error: {0}, Message: {1}", e.Message, baseException.Message);
         }
         Console.ReadKey();
      }

      private static async Task CreateDocumentClient() {
         // Create a new instance of the DocumentClient
         using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)) {
            await CreateDatabase(client);
         }
      }

      private async static Task CreateDatabase(DocumentClient client) {
         Console.WriteLine();
         Console.WriteLine("******** Create Database *******");

         var databaseDefinition = new Database { Id = "mynewdb" };
         var result = await client.CreateDatabaseAsync(databaseDefinition);
         var database = result.Resource;

         Console.WriteLine(" Database Id: {0}; Rid: {1}", database.Id, database.ResourceId);
         Console.WriteLine("******** Database Created *******");
      }

   }
}

    When the above code is compiled and executed, you will receive the following output which contains the Database and Resources IDs.

    ******** Create Database *******
 Database Id: mynewdb; Rid: ltpJAA==
******** Database Created *******

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí DocumentDB – Introduction nhận dự án làm có lương

    DocumentDB – Introduction

    In this chapter, we will briefly discuss the major concepts around NoSQL and document databases. We will also have a quick overview of DocumentDB.

    NoSQL Document Database

    DocumentDB is Microsoft”s newest NoSQL document database, so when you say NoSQL document database then, what precisely do we mean by NoSQL, and document database?

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

    • It”s 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.

    Azure DocumentDB

    Microsoft officially launched Azure DocumentDB on April 8th, 2015, and it certainly can be characterized as a typical NoSQL document database. It”s massively scalable, and it works with schema-free JSON documents.

    • DocumentDB is a true schema-free NoSQL document database service designed for modern mobile and web applications.

    • It also delivers consistently fast reads and writes, schema flexibility, and the ability to easily scale a database up and down on demand.

    • It does not assume or require any schema for the JSON documents it indexes.

    • DocumentDB automatically indexes every property in a document as soon as the document is added to the database.

    • DocumentDB enables complex ad-hoc queries using a SQL language, and every document is instantly queryable the moment it”s created, and you can search on any property anywhere within the document hierarchy.

    DocumentDB – Pricing

    DocumentDB is billed based on the number of collections contained in a database account. Each account can have one or more databases and each database can have a virtually unlimited number of collections, although there is an initial default quota of 100. This quota can be lifted by contacting Azure support.

    • A collection is not only a unit of scale, but also a unit of cost, so in DocumentDB you pay per collection, which has a storage capacity of up to 10 GB.

    • At a minimum, you”ll need one S1 collection to store documents in a database that will cost roughly $25 per month, which gets billed against your Azure subscription.

    • As your database grows in size and exceeds 10 GB, you”ll need to purchase another collection to contain the additional data.

    • Each S1 collection will give you 250 request units per second, and if that”s not enough, then you can scale the collection up to an S2 and get a 1000 request units per second for about $50 a month.

    • You can also turn it all the way up to an S3 and pay around $100 a month.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí DocumentDB – Create Account nhận dự án làm có lương

    DocumentDB – Create Account

    To use Microsoft Azure DocumentDB, you must create a DocumentDB account. In this chapter, we will create a DocumentDB account using Azure portal.

    Step 1 − Log in to the online if you already have an Azure subscription otherwise you need to sign in first.

    You will see the main Dashboard. It is fully customizable so you can arrange these tiles any way you like, resize them, add and remove tiles for things you frequently use or no longer do.

    Dashboard

    Step 2 − Select the ‘New’ option on the top left side of the page.

    Select New Option

    Step 3 − Now select Data + Storage > Azure DocumentDB option and you see the following New DocumentDB account section.

    Azure DocumentDB

    We need to come up with a globally unique name (ID), which combined with .documents.azure.com is the publicly addressable endpoint to our DocumentDB account. All the databases we create beneath that account can be accessed over the internet using this endpoint.

    Step 4 − Let’s name it azuredocdbdemo and click on Resource Group → new_resource.

    Resource Group

    Step 5 − Choose the location i.e., which Microsoft data center you want this account to be hosted. Select the location and choose your region.

    Select Location

    Step 6 − Check Pin to dashboard checkbox and just go ahead and click Create button.

    Create Button

    You can see that the tile has already been added to the Dashboard, and it”s letting us know that the account is being created. It can actually take a few minutes to set things up for a new account while DocumentDB allocates the endpoint, provisions replicas, and performs other work in the background.

    Once it is done, you will see the dashboard.

    Account Dashboard

    Step 7 − Now click on the created DocumentDB account and you will see a detailed screen as the following image.

    Created Documentdb

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí DocumentDB – Advantages nhận dự án làm có lương

    DocumentDB – Advantages

    DocumentDB stands out with some very unique capabilities. Azure DocumentDB offers the following key capabilities and benefits.

    Schema Free

    In a relational database, every table has a schema that defines the columns and data types that each row in the table must conform to.

    In contrast, a document database has no defined schema, and every document can be structured differently.

    SQL Syntax

    DocumentDB enables complex ad-hoc queries using SQL language, and every document is instantly queryable the moment it”s created. You can search on any property anywhere within the document hierarchy.

    Tunable Consistency

    It provides some granular, well-defined consistency levels, which allows you to make sound trade-offs between consistency, availability, and latency.

    You can select from four well-defined consistency levels to achieve optimal trade-off between consistency and performance. For queries and read operations, DocumentDB offers four distinct consistency levels −

    • Strong
    • Bounded-staleness
    • Session
    • Eventual

    Elastic Scale

    Scalability is the name of the game with NoSQL, and DocumentDB delivers. DocumentDB has already been proven its scale.

    • Major services like Office OneNote and Xbox are already backed by DocumentDB with databases containing tens of terabytes of JSON documents, over a million active users, and operating consistently with 99.95% availability.

    • You can elastically scale DocumentDB with predictable performance by creating more units as your application grows.

    Fully Managed

    DocumentDB is available as a fully managed cloud-based platform as a service running on Azure.

    • There is simply nothing for you to install or manage.

    • There are no servers, cables, no operating systems or updates to deal with, no replicas to set up.

    • Microsoft does all that work and keeps the service running.

    • Within literally minutes, you can get started working with DocumentDB using just a browser and an Azure subscription.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí DocumentDB – List Databases nhận dự án làm có lương

    DocumentDB – List Databases

    So far, we have created two databases in our DocumentDB account, first one is created using Azure portal while the second database is created using .Net SDK. Now to view these databases, you can use Azure portal.

    Go to your DocumentDB account on Azure portal and you will see two databases now.

    Two Databases

    You can also view or list the databases from your code using .Net SDK. Following are the steps involved.

    Step 1 − Issue a database Query with no parameters which returns a complete list, but you can also pass in a query to look for a specific database or specific databases.

    private static void GetDatabases(DocumentClient client) {
   Console.WriteLine();
   Console.WriteLine();
   Console.WriteLine("******** Get Databases List ********");

   var databases = client.CreateDatabaseQuery().ToList();

   foreach (var database in databases) {
      Console.WriteLine(" Database Id: {0}; Rid: {1}", database.Id, database.ResourceId);
   }

   Console.WriteLine();
   Console.WriteLine("Total databases: {0}", databases.Count);
}

    You will see that there are a bunch of these CreateQuery methods for locating collections, documents, users, and other resources. These methods don”t actually execute the query, they just define the query and return an iterateable object.

    It”s the call to ToList() that actually executes the query, iterates the results, and returns them in a list.

    Step 2 − Call GetDatabases method from the CreateDocumentClient task after DocumentClient is instantiated.

    Step 3 − You also need to comment the CreateDatabase task or change the database id, otherwise you will get an error message that the database exists.

    using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)) {
   //await CreateDatabase(client);
   GetDatabases(client);
}

    Following is the complete Program.cs file so far.

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

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

namespace DocumentDBDemo {

   class Program {
      private const string EndpointUrl = "https://azuredocdbdemo.documents.azure.com:443/";

      private const string AuthorizationKey = "BBhjI0gxdVPdDbS4diTjdloJq7Fp4L5RO/
         StTt6UtEufDM78qM2CtBZWbyVwFPSJIm8AcfDu2O+AfV T+TYUnBQ==";

      static void Main(string[] args) {
         try {
            CreateDocumentClient().Wait();
         } catch (Exception e) {
            Exception baseException = e.GetBaseException();
            Console.WriteLine("Error: {0}, Message: {1}", e.Message, baseException.Message);
         }
         Console.ReadKey();
      }

      private static async Task CreateDocumentClient() {
         // Create a new instance of the DocumentClient
         using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)) {
            await CreateDatabase(client);
            GetDatabases(client);
         }
      }

      private async static Task CreateDatabase(DocumentClient client) {
         Console.WriteLine();
         Console.WriteLine("******** Create Database *******");

         var databaseDefinition = new Database { Id = "mynewdb" };
         var result = await client.CreateDatabaseAsync(databaseDefinition);
         var database = result.Resource;

         Console.WriteLine(" Database Id: {0}; Rid: {1}", database.Id, database.ResourceId);
         Console.WriteLine("******** Database Created *******");
      }

      private static void GetDatabases(DocumentClient client) {
         Console.WriteLine();
         Console.WriteLine();
         Console.WriteLine("******** Get Databases List ********");

         var databases = client.CreateDatabaseQuery().ToList();

         foreach (var database in databases) {
            Console.WriteLine(" Database Id: {0}; Rid: {1}",
               database.Id, database.ResourceId);
         }

         Console.WriteLine();
         Console.WriteLine("Total databases: {0}", databases.Count);
      }

   }
}

    When the above code is compiled and executed you will receive the following output which contains the Database and Resources IDs of both the databases. In the end you will also see the total number of databases.

    ******** Get Databases List ********
 Database Id: myfirstdb; Rid: Ic8LAA==
 Database Id: mynewdb; Rid: ltpJAA==
Total databases: 2

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí DocumentDB – Home nhận dự án làm có lương

    DocumentDB tutorial

    DocumentDB Tutorial







    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 explains the basics of DocumentDB with illustrative examples.

    Audience

    This tutorial is designed for beginners, i.e., for developers who want to get acquainted with how DocumentDB works.

    Prerequisites

    It is an elementary tutorial that explains the basics of DocumentDB and there are no prerequisites as such. However, it will certainly help if you have some prior exposure to NoSQL technologies.

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí DocumentDB SQL – Useful Resources nhận dự án làm có lương

    DocumentDB SQL – Useful Resources

    The following resources contain additional information on DocumentDB SQL. Please use them to get more in-depth knowledge on this topic.

    Useful Links on DocumentDB SQL

    • − Official Site of DocumentDB SQL

    Useful Books on DocumentDB SQL

    To enlist your site on this page, please drop an email to contact@tutorialspoint.com


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí DocumentDB SQL – Quick Guide nhận dự án làm có lương

    DocumentDB SQL – Quick Guide

    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.

    DocumentDB SQL – Select Clause

    The Azure portal has a Query Explorer that lets us run any SQL query against our DocumentDB database. We will use the Query Explorer to demonstrate the many different capabilities and features of the query language starting with the simplest possible query.

    Step 1 − Open the Azure Portal, and in the database blade, click the Query Explorer blade.

    Query Explorer Blade

    Remember that queries run within the scope of a collection, and so the Query Explorer lets us choose the collection in this dropdown. We will leave it set to our Families collection that contains the three documents. Let’s consider these three documents in this example.

    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
}

    The Query Explorer opens up with this simple query SELECT * FROM c, which simply retrieves all documents from the collection. Although it is simple, it”s still quite different than the equivalent query in a relational database.

    Step 2 − In relational databases, SELECT * means return all columns while in DocumentDB. It means that you want each document in your result to be returned exactly as it”s stored in the database.

    But when you select specific properties and expressions instead of simply issuing a SELECT *, then you are projecting a new shape that you want for each document in the result.

    Step 3 − Click ‘Run’ to execute query and open the Results blade.

    Open Results Blade

    As can be seen the WakefieldFamily, the SmithFamily, and the AndersonFamily are retrieved.

    Following are the three documents which are retrieved as a result of the SELECT * FROM c query.

    [
   {
      "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": "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,
      "_rid": "Ic8LAJFujgEDAAAAAAAAAA==",
      "_ts": 1450541623,
      "_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgEDAAAAAAAAAA==/",
      "_etag": ""00000600-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/"
   }
]

    However, these results also include the system-generated properties that are all prefixed with the underscore character.

    DocumentDB SQL – From Clause

    In this chapter, we will cover the FROM clause, which works nothing like a standard FROM clause in regular SQL.

    Queries always run within the context of a specific collection and cannot join across documents within the collection, which makes us wonder why we need a FROM clause. In fact, we don”t, but if we don”t include it, then we won”t be querying documents in the collection.

    The purpose of this clause is to specify the data source upon which the query must operate. Commonly the whole collection is the source, but one can specify a subset of the collection instead. The FROM <from_specification> clause is optional unless the source is filtered or projected later in the query.

    Let’s take a look at the same example again. 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
}
    WakefieldFamily

    In the above query, “SELECT * FROM c” indicates that the entire Families collection is the source over which to enumerate.

    Sub-documents

    The source can also be reduced to a smaller subset. When we want to retrieve only a subtree in each document, the sub-root could then become the source, as shown in the following example.

    Sub-documents

    When we run the following query −

    SELECT * FROM Families.parents

    The following sub-documents will be retrieved.

    [
   [
      {
         "familyName": "Wakefield",
         "givenName": "Robin"
      },

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

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

      {
         "familyName": "Curtis",
         "givenName": "Helen"
      }
   ],

   [
      {
         "firstName": "Thomas",
         "relationship": "father"
      },

      {
         "firstName": "Mary Kay",
         "relationship": "mother"
      }
   ]
]

    As a result of this query, we can see that only the parents sub-documents are retrieved.

    DocumentDB SQL – Where Clause

    In this chapter, we will cover the WHERE clause, which is also optional like FROM clause. It is used to specify a condition while fetching the data in the form of JSON documents provided by the source. Any JSON document must evaluate the specified conditions to be “true” to be considered for the result. If the given condition is satisfied, only then it returns specific data in the form of JSON document(s). We can use WHERE clause to filter the records and fetch only necessary records.

    We will consider the same three documents in this example. 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 WHERE clause is used.

    WHERE Clause

    In this query, in WHERE clause, the (WHERE f.id = “WakefieldFamily”) condition is specified.

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

    DocumentDB SQL – Operators

    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.

    S.No. Operators & Description
    1

    =

    Checks if the values of two operands are equal or not. If yes, then condition becomes true.
    2

    !=

    Checks if the values of two operands are equal or not. If values are not equal then condition becomes true.
    3

    <>

    Checks if the values of two operands are equal or not. If values are not equal then condition becomes true.
    4

    >

    Checks if the value of left operand is greater than the value of right operand. If yes, then condition becomes true.
    5

    <

    Checks if the value of left operand is less than the value of right operand. If yes, then condition becomes true.
    6

    >=

    Checks if the value of left operand is greater than or equal to the value of right operand. If yes, then condition becomes true.
    7

    <=

    Checks if the value of left operand is less than or equal to the value of right operand. If yes, then condition becomes true.

    SQL Logical Operators

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

    S.No. Operators & Description
    1

    AND

    The AND operator allows the existence of multiple conditions in an SQL statement”s WHERE clause.
    2

    BETWEEN

    The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
    3

    IN

    The IN operator is used to compare a value to a list of literal values that have been specified.
    4

    OR

    The OR operator is used to combine multiple conditions in an SQL statement”s WHERE clause.
    5

    NOT

    The NOT operator reverses the meaning of the logical operator with which it is used. For example, NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.

    SQL Arithmetic Operators

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

    S.No. Operators & Description
    1

    +

    Addition − Adds values on either side of the operator.
    2

    –

    Subtraction − Subtracts the right hand operand from the left hand operand.
    3

    *

    Multiplication − Multiplies values on either side of the operator.
    4

    /

    Division − Divides the left hand operand by the right hand operand.
    5

    %

    Modulus − Divides the left hand operand by the right hand operand and returns the remainder.

    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.

    Comparison Operator

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

    DocumentDB SQL – Between Keyword

    The BETWEEN keyword is used to express queries against ranges of values like in SQL. BETWEEN can be used against strings or numbers. The main difference between using BETWEEN in DocumentDB and ANSI SQL is that you can express range queries against properties of mixed types.

    For example, in some document it is possible that you might have “grade” as a number and in other documents it might be strings. In these cases, a comparison between two different types of results is “undefined”, and the document will be skipped.

    Let us consider the three documents from the previous example. 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, where the query returns all family documents in which the first child”s grade is between 1-5 (both inclusive).

    Returns Family Documents

    Following is the query in which BETWEEN keyword is used and then AND logical operator.

    SELECT *
FROM Families.children[0] c
WHERE c.grade BETWEEN 1 AND 5

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

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

   {
      "firstName": "Henriette Thaulow",
      "gender": "female",
      "grade": 5,

      "pets": [
         {
            "givenName": "Fluffy",
            "type": "Rabbit"
         }
      ]
   }
]

    To display the grades outside the range of the previous example, use NOT BETWEEN as shown in the following query.

    SELECT *
FROM Families.children[0] c
WHERE c.grade NOT BETWEEN 1 AND 5

    When this query is executed. It produces the following output.

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

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

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

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

    DocumentDB SQL – In Keyword

    The IN keyword can be used to check whether a specified value matches any value in a list. The IN operator allows you to specify multiple values in a WHERE clause. IN is equivalent to chaining multiple OR clauses.

    The similar three documents are considered as done in earlier 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.

    In KeyWord

    Following is the query which will retrieve the data whose familyName is either “Smith” or Wakefield.

    SELECT *
FROM Families.parents[0] f
WHERE f.familyName IN (''Smith'', ''Wakefield'')

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

    [
   {
      "familyName": "Wakefield",
      "givenName": "Robin"
   },

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

    Let’s consider another simple example in which all family documents will be retrieved where the id is one of “SmithFamily” or “AndersenFamily”. Following is the query.

    SELECT *
FROM Families
WHERE Families.id IN (''SmithFamily'', ''AndersenFamily'')

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

    [
   {
      "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,
      "_rid": "Ic8LAJFujgEDAAAAAAAAAA==",
      "_ts": 1450541623,
      "_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgEDAAAAAAAAAA==/",
      "_etag": ""00000600-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/"
   }
]

    DocumentDB SQL – Value Keyword

    When you know you”re only returning a single value, then the VALUE keyword can help produce a leaner result set by avoiding the overhead of creating a full-blown object. The VALUE keyword provides a way to return JSON value.

    Let’s take a look at a simple example.

    Value KeyWord

    Following is the query with VALUE keyword.

    SELECT VALUE "Hello World, this is DocumentDB SQL Tutorial"

    When this query is executed, it returns the scalar “Hello World, this is DocumentDB SQL Tutorial”.

    [
   "Hello World, this is DocumentDB SQL Tutorial"
]

    In another example, let’s consider the three documents from 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
}

    Following is the query.

    SELECT VALUE f.location
FROM Families f

    When this query is executed, it return the returns the address without the location label.

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

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

   {
      "state": "WA",
      "county": "King",
      "city": "Seattle"
   }
]

    If we now specify the same query without VALUE Keyword, then it will return the address with location label. Following is the query.

    SELECT f.location
FROM Families f

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

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

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

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

    DocumentDB SQL – Order By Clause

    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.

    Order By Clause

    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.

    Order By Clauses

    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 SQL – Iteration

    In DocumentDB SQL, Microsoft has added a new construct which can be used with IN keyword to provide support for iterating over JSON arrays. The support for iteration is provided in the FROM clause.

    We will consider similar three documents from the previous examples again.

    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 without IN keyword in FROM clause.

    Iteration

    Following is the query which will return all the parents from the Families collection.

    SELECT *
FROM Families.parents

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

    [
   [
      {
         "familyName": "Wakefield",
         "givenName": "Robin"
      },

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

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

      {
         "familyName": "Curtis",
         "givenName": "Helen"
      }
   ],

   [
      {
         "firstName": "Thomas",
         "relationship": "father"
      },

      {
         "firstName": "Mary Kay",
         "relationship": "mother"
      }
   ]
]

    As can be seen in the above output, the parents of each family is displayed in a separate JSON array.

    Let’s take a look at the same example, however this time we will use the IN keyword in FROM clause.

    Iterations

    Following is the query which contains the IN keyword.

    SELECT *
FROM c IN Families.parents

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

    [
   {
      "familyName": "Wakefield",
      "givenName": "Robin"
   },

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

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

   {
      "familyName": "Curtis",
      "givenName": "Helen"
   },

   {
      "firstName": "Thomas",
      "relationship": "father"
   },

   {
      "firstName": "Mary Kay",
      "relationship": "mother"
   }

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

    In the above example, it can be seen that with iteration, the query that performs iteration over parents in the collection has different output array. Hence, all the parents from each family are added into a single array.

    DocumentDB SQL – Joins

    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.

    SQL Join

    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.

    SQL Joins

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

    DocumentDB SQL – Aliasing

    In relational databases, SQL aliases are used to temporarily rename a table or a column heading. Similarly, in DocumentDB, aliases are used to temporarily rename a JSON document, sub-document, object or any field.

    The renaming is a temporary change and the actual document does not change. Basically, aliases are created to make field/document names more readable. For aliasing, AS keyword is used which is optional.

    Let’s consider three similar documents from the ones used in 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 discuss the aliases.

    Aliase

    Following is the query which will join the root to children subdocument. We have aliases such as f.id AS familyName, c.givenName AS childGivenName, and c.firstName AS childFirstName.

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

    The above output shows that the filed names are changed, but it is a temporary change and the original documents are not modified.

    DocumentDB SQL – Array Creation

    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.

    Array Creation

    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 – Scalar Expressions

    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.

    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 SQL – Parameterized

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

    DocumentDB SQL – Built-in Function

    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 −

    S.No. Built-in Functions & Description
    1

    The mathematical functions perform a calculation, usually based on input values that are provided as arguments, and return a numeric value.
    2

    The type checking functions allow you to check the type of an expression within SQL queries.
    3

    The string functions perform an operation on a string input value and return a string, numeric or Boolean value.
    4

    The array functions perform an operation on an array input value and return in the form of numeric, Boolean or array value.
    5

    DocumentDB also supports the Open Geospatial Consortium (OGC) built-in functions for geospatial querying.

    DocumentDB SQL – Linq to SQL Translation

    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

    DocumentDB SQL – JavaScript Integration

    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

    JavaScript Stored Procedure

    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 – User-Defined Functions

    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

    DocumentDB SQL – Composite SQL Queries

    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.

    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.

    Concatenated Queries

    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.

    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.

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

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc