DocumentDB – Useful Resources ”; Previous Next The following resources contain additional information on DocumentDB. Please use them to get more in-depth knowledge on this topic. Useful Links on DocumentDB DocumentDB − Reference for DocumentDB. Introduction − Introduction Reference for DocumentDB. DocumentDB Wiki − Wikipedia Reference for DocumentDB. Useful Books on DocumentDB To enlist your site on this page, please drop an email to [email protected] Print Page Previous Next Advertisements ”;
Category: documentdb
DocumentDB – Discussion
Discuss DocumentDB ”; Previous Next 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. Print Page Previous Next Advertisements ”;
DocumentDB – Visualize Data
DocumentDB – Visualize Data ”; Previous Next In this chapter, we will learn how to visualize data which is stored in DocumentDB. Microsoft provided Power BI Desktop tool which transforms your data into rich visuals. It also enables you to retrieve data from various data sources, merge and transform the data, create powerful reports and visualizations, and publish the reports to Power BI. In the latest version of Power BI Desktop, Microsoft has added support for DocumentDB as well in which you can now connect to your DocumentDB account. You can download this tool from the link, https://powerbi.microsoft.com Let’s take a look at an example in which we will visualize the earthquakes data imported in the last chapter. Step 1 − Once the tool is downloaded, launch the Power BI desktop. Step 2 − Click ‘Get Data’ option which is on the Home tab under External Data group and it will display the Get Data page. Step 3 − Select the Microsoft Azure DocumentDB (Beta) option and click ‘Connect’ button. Step 4 − Enter the URL of your Azure DocumentDB account, Database and Collection from which you want visualize data and press Ok. If you are connecting to this endpoint for the first time, you will be prompted for the account key. Step 5 − Enter the account key (primary key) which is unique for each DocumentDB account available on Azure portal, and then click Connect. When the account is successfully connected, it will retrieve the data from specified database. The Preview pane shows a list of Record items, a Document is represented as a Record type in Power BI. Step 6 − Click ‘Edit’ button which will launch the Query Editor. Step 7 − In the Power BI Query Editor, you should see a Document column in the center pane, click on the expander at the right side of the Document column header and select the columns which you want display. As you can see that we have latitude and longitude as separate column but we visualize data in latitude, longitude coordinates form. Step 8 − To do that, click ‘Add Column’ tab. Step 9 − Select the Add Custom Column which will display the following page. Step 10 − Specify the new column name, let’s say LatLong and also the formula which will combine the latitude and longitude in one column separated by a comma. Following is the formula. Text.From([latitude])&”, “&Text.From([longitude]) Step 11 − Click OK to continue and you will see that the new column is added. Step 12 − Go to the Home tab and click ‘Close & Apply’ option. Step 13 − You can create reports by dragging and dropping fields into the Report canvas. You can see on the right, there are two panes − one Visualizations pane and the other is Fields pane. Let’s create a map view showing the location of each earthquake. Step 14 − Drag the map visual type from the Visualizations pane. Step 15 − Now, drag and drop the LatLong field from the Fields pane to the Location property in Visualizations pane. Then, drag and drop the magnitude field to the Values property. Step 16 − Drag and drop the depth field to the Color saturation property. You will now see the Map visual showing a set of bubbles indicating the location of each earthquake. Print Page Previous Next Advertisements ”;
DocumentDB – Quick Guide
DocumentDB – Quick Guide ”; Previous Next 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. 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. DocumentDB – Environment Setup Microsoft provides a free version of Visual Studio which also contains SQL Server and it can be downloaded from https://www.visualstudio.com Installation Step 1 − Once downloading is completed, run the installer. The following dialog will be displayed. Step 2 − Click on the Install button and it will start the installation process. Step 3 − Once the installation process is completed successfully, you will see the following dialog. 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. Once all is done, you will see the main window of Visual Studio. Step 6 − Let’s create a new project from File → 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. 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. Step 10 − Install the latest version by clicking the install button. Step 11 − Click “I Accept”. Once installation is done you will see the message in your output window. You are now ready to start your application. DocumentDB – Create Account To use Microsoft Azure DocumentDB, you must create a DocumentDB
DocumentDB – Geospatial Data
DocumentDB – Geospatial Data ”; Previous Next Microsoft added geospatial support, which lets you store location data in your documents and perform spatial calculations for distance and intersections between points and polygons. Spatial data describes the position and shape of objects in space. Typically, it can be used to represent the location of a person, a place of interest, or the boundary of a city, or a lake. Common use cases often involve proximity queries. For e.g., “find all universities near my current location”. A Point denotes a single position in space which represents the exact location, e.g. street address of particular university. A point is represented in DocumentDB using its coordinate pair (longitude and latitude). Following is an example of JSON point. { “type”:”Point”, “coordinates”:[ 28.3, -10.7 ] } Let’s take a look at a simple example which contains the location of a university. { “id”:”case-university”, “name”:”CASE: Center For Advanced Studies In Engineering”, “city”:”Islamabad”, “location”: { “type”:”Point”, “coordinates”:[ 33.7194136, -73.0964862 ] } } To retrieve the university name based on the location, you can use the following query. SELECT c.name FROM c WHERE c.id = “case-university” AND ST_ISVALID({ “type”:”Point”, “coordinates”:[ 33.7194136, -73.0964862 ] }) When the above query is executed you will receive the following output. [ { “name”: “CASE: Center For Advanced Studies In Engineering” } ] Create Document with Geospatial Data in .NET You can create a document with geospatial data, let’s take a look at a simple example in which a university document is created. private async static Task CreateDocuments(DocumentClient client) { Console.WriteLine(); Console.WriteLine(“**** Create Documents ****”); Console.WriteLine(); var uniDocument = new UniversityProfile { Id = “nust”, Name = “National University of Sciences and Technology”, City = “Islamabad”, Loc = new Point(33.6455715, 72.9903447) }; Document document = await CreateDocument(client, uniDocument); Console.WriteLine(“Created document {0} from typed object”, document.Id); Console.WriteLine(); } Following is the implementation for the UniversityProfile class. public class UniversityProfile { [JsonProperty(PropertyName = “id”)] public string Id { get; set; } [JsonProperty(“name”)] public string Name { get; set; } [JsonProperty(“city”)] public string City { get; set; } [JsonProperty(“location”)] public Point Loc { get; set; } } When the above code is compiled and executed, you will receive the following output. **** Create Documents **** Created new document: nust { “id”: “nust”, “name”: “National University of Sciences and Technology”, “city”: “Islamabad”, “location”: { “type”: “Point”, “coordinates”: [ 33.6455715, 72.9903447 ] }, “_rid”: “Ic8LAMEUVgANAAAAAAAAAA==”, “_ts”: 1450200910, “_self”: “dbs/Ic8LAA==/colls/Ic8LAMEUVgA=/docs/Ic8LAMEUVgANAAAAAAAAAA==/”, “_etag”: “”00004100-0000-0000-0000-56704f4e0000″”, “_attachments”: “attachments/” } Created document nust from typed object Print Page Previous Next Advertisements ”;
DocumentDB – Data Migration
DocumentDB – Data Migration ”; Previous Next With the DocumentDB Data Migration tool, you can easily migrate data to DocumentDB. The DocumentDB Data Migration Tool is a free and open source utility you can download from the Microsoft Download Center https://www.microsoft.com/ The Migration Tool supports many data sources, some of them are listed below − SQL Server JSON files Flat files of Comma-separated Values (CSV) MongoDB Azure Table Storage Amazon DynamoDB HBase, and even other DocumentDB databases After downloading the DocumentDB Data Migration tool, extract the zip file. You can see two executables in this folder as shown in the following screenshot. First, there is dt.exe, which is the console version with a command line interface, and then there is dtui.exe, which is the desktop version with a graphical user interface. Let”s launch the GUI version. You can see the Welcome page. Click ‘Next’ for the Source Information page. Here”s where you configure your data source, and you can see the many supported choices from the dropdown menu. When you make a selection, the rest of the Source Information page changes accordingly. It is very easy to import data to DocumentDB using the DocumentDB Data Migration Tool. We recommend you exercise the above examples and use the other data files as well. Print Page Previous Next Advertisements ”;
DocumentDB – Query Document
DocumentDB – Query Document ”; Previous Next In DocumentDB, we actually use SQL to query for documents, so this chapter is all about querying using the special SQL syntax in DocumentDB. Although if you are doing .NET development, there is also a LINQ provider that can be used and which can generate appropriate SQL from a LINQ query. Querying Document using Portal The Azure portal has a Query Explorer that lets you run any SQL query against your 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 − In the database blade, click to open the Query Explorer blade. Remember that queries run within the scope of a collection, and so the Query Explorer lets you choose the collection in this dropdown. Step 2 − Select Families collection which is created earlier using the portal. The Query Explorer opens up with this simple query SELECT * FROM c, which simply retrieves all documents from the collection. Step 3 − Execute this query by clicking the ‘Run query’ button. Then you will see that the complete document is retrieved in the Results blade. Querying Document using .Net SDK Following are the steps to run some document queries using .Net SDK. In this example, we want to query for the newly created documents that we just added. Step 1 − Call CreateDocumentQuery, passing in the collection to run the query against by its SelfLink and the query text. private async static Task QueryDocumentsWithPaging(DocumentClient client) { Console.WriteLine(); Console.WriteLine(“**** Query Documents (paged results) ****”); Console.WriteLine(); Console.WriteLine(“Quering for all documents”); var sql = “SELECT * FROM c”; var query = client.CreateDocumentQuery(collection.SelfLink, sql).AsDocumentQuery(); while (query.HasMoreResults) { var documents = await query.ExecuteNextAsync(); foreach (var document in documents) { Console.WriteLine(” Id: {0}; Name: {1};”, document.id, document.name); } } Console.WriteLine(); } This query is also returning all documents in the entire collection, but we”re not calling .ToList on CreateDocumentQuery as before, which would issue as many requests as necessary to pull down all the results in one line of code. Step 2 − Instead, call AsDocumentQuery and this method returns a query object with a HasMoreResults property. Step 3 − If HasMoreResults is true, then call ExecuteNextAsync to get the next chunk and then dump all the contents of that chunk. Step 4 − You can also query using LINQ instead of SQL if you prefer. Here 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} UK 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 Step 5 − Now 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(); //await CreateDocuments(client); await QueryDocumentsWithPaging(client); QueryDocumentsWithLinq(client); } } When the above code is executed, you will receive the following output. **** Query Documents (paged results) **** Quering for all documents Id: 7e9ad4fa-c432-4d1a-b120-58fd7113609f; Name: New Customer 1; Id: 34e9873a-94c8-4720-9146-d63fb7840fad; Name: New Customer 1; **** Query Documents (LINQ) **** Quering for US customers (LINQ) Found 2 UK 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 Print Page Previous Next Advertisements ”;
DocumentDB – Delete Collection ”; Previous Next To drop collection or collections you can do the same from the portal as well as from the code by using .Net SDK. Step 1 − Go to your DocumentDB account on Azure portal. For the purpose of demo, I have added two more collections as seen in the following screenshot. Step 2 − To drop any collection, you need to click on that collection. Let’s select TempCollection1. You will see the following page, select the ‘Delete Collection’ option. Step 3 − It will display the confirmation message. Now click ‘Yes’ button. You will see that the TempCollection1 is no more available on your dashboard. You can also delete collections from your code using .Net SDK. To do that, following are the following steps. Step 1 − Let”s delete the collection by specifying the ID of the collection we want to delete. It”s the usual pattern of querying by Id to obtain the selfLinks needed to delete a resource. 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); } Here we see the preferred way of constructing a parameterized query. We”re not hardcoding the collectionId so this method can be used to delete any collection. 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. Then the SDK does the work of constructing the final query string for DocumentDB with the collectionId embedded inside of it. Step 2 − Run the query and then use its SelfLink to delete the collection 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(); await DeleteCollection(client, “TempCollection”); } } Following is the complete implementation of Program.cs file. 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 DeleteCollection(client, “TempCollection”); //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); } 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); } } } When the above code is compiled and executed, you will receive the following output. **** Delete Collection TempCollection in myfirstdb **** Deleted collection TempCollection from database myfirstdb Print Page Previous Next Advertisements ”;
DocumentDB – Indexing Records ”; Previous Next By default, DocumentDB automatically indexes every property in a document as soon as the document is added to the database. However, you can take control and fine tune your own indexing policy that reduces storage and processing overhead when there are specific documents and/or properties that never needs to be indexed. The default indexing policy that tells DocumentDB to index every property automatically is suitable for many common scenarios. But you can also implement a custom policy that exercises fine control over exactly what gets indexed and what doesn”t and other functionality with regards to indexing. DocumentDB supports the following types of indexing − Hash Range Hash Hash index enables efficient querying for equality, i.e., while searching for documents where a given property equals an exact value, rather than matching on a range of values like less than, greater than or between. You can perform range queries with a hash index, but DocumentDB will not be able to use the hash index to find matching documents and will instead need to sequentially scan each document to determine if it should be selected by the range query. You won”t be able to sort your documents with an ORDER BY clause on a property that has just a hash index. Range Range index defined for the property, DocumentDB allows to efficiently query for documents against a range of values. It also allows you to sort the query results on that property, using ORDER BY. DocumentDB allows you to define both a hash and a range index on any or all properties, which enables efficient equality and range queries, as well as ORDER BY. Indexing Policy Every collection has an indexing policy that dictates which types of indexes are used for numbers and strings in every property of every document. You can also control whether or not documents get indexed automatically as they are added to the collection. Automatic indexing is enabled by default, but you can override that behavior when adding a document, telling DocumentDB not to index that particular document. You can disable automatic indexing so that by default, documents are not indexed when added to the collection. Similarly, you can override this at the document level and instruct DocumentDB to index a particular document when adding it to the collection. This is known as manual indexing. Include / Exclude Indexing An indexing policy can also define which path or paths should be included or excluded from the index. This is useful if you know that there are certain parts of a document that you never query against and certain parts that you do. In these cases, you can reduce indexing overhead by telling DocumentDB to index just those particular portions of each document added to the collection. Automatic Indexing Let’s take a look at a simple example of automatic indexing. Step 1 − First we create a collection called autoindexing and without explicitly supplying a policy, this collection uses the default indexing policy, which means that automatic indexing is enabled on this collection. Here we are using ID-based routing for the database self-link so we don”t need to know its resource ID or query for it before creating the collection. We can just use the database ID, which is mydb. Step 2 − Now let’s create two documents, both with the last name of Upston. private async static Task AutomaticIndexing(DocumentClient client) { Console.WriteLine(); Console.WriteLine(“**** Override Automatic Indexing ****”); // Create collection with automatic indexing var collectionDefinition = new DocumentCollection { Id = “autoindexing” }; var collection = await client.CreateDocumentCollectionAsync(“dbs/mydb”, collectionDefinition); // Add a document (indexed) dynamic indexedDocumentDefinition = new { id = “MARK”, firstName = “Mark”, lastName = “Upston”, addressLine = “123 Main Street”, city = “Brooklyn”, state = “New York”, zip = “11229”, }; Document indexedDocument = await client .CreateDocumentAsync(“dbs/mydb/colls/autoindexing”, indexedDocumentDefinition); // Add another document (request no indexing) dynamic unindexedDocumentDefinition = new { id = “JANE”, firstName = “Jane”, lastName = “Upston”, addressLine = “123 Main Street”, city = “Brooklyn”, state = “New York”, zip = “11229”, }; Document unindexedDocument = await client .CreateDocumentAsync(“dbs/mydb/colls/autoindexing”, unindexedDocumentDefinition, new RequestOptions { IndexingDirective = IndexingDirective.Exclude }); //Unindexed document won”t get returned when querying on non-ID (or selflink) property var doeDocs = client.CreateDocumentQuery(“dbs/mydb/colls/autoindexing”, “SELECT * FROM c WHERE c.lastName = ”Doe””).ToList(); Console.WriteLine(“Documents WHERE lastName = ”Doe”: {0}”, doeDocs.Count); // Unindexed document will get returned when using no WHERE clause var allDocs = client.CreateDocumentQuery(“dbs/mydb/colls/autoindexing”, “SELECT * FROM c”).ToList(); Console.WriteLine(“All documents: {0}”, allDocs.Count); // Unindexed document will get returned when querying by ID (or self-link) property Document janeDoc = client.CreateDocumentQuery(“dbs/mydb/colls/autoindexing”, “SELECT * FROM c WHERE c.id = ”JANE””).AsEnumerable().FirstOrDefault(); Console.WriteLine(“Unindexed document self-link: {0}”, janeDoc.SelfLink); // Delete the collection await client.DeleteDocumentCollectionAsync(“dbs/mydb/colls/autoindexing”); } This first one, for Mark Upston, gets added to the collection and is then immediately indexed automatically based on the default indexing policy. But when the second document for Mark Upston is added, we have passed the request options with IndexingDirective.Exclude which explicitly instructs DocumentDB not to index this document, despite the collection”s indexing policy. We have different types of queries for both the documents at the end. Step 3 − Let’s call the AutomaticIndexing task from CreateDocumentClient. private static async Task CreateDocumentClient() { // Create a new instance of the DocumentClient using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)) { await AutomaticIndexing(client); } } When the above code is compiled and executed, you will receive the following output. **** Override Automatic Indexing **** Documents WHERE lastName = ”Upston”: 1 All documents: 2 Unindexed document self-link: dbs/kV5oAA==/colls/kV5oAOEkfQA=/docs/kV5oAOEkfQACA AAAAAAAAA==/ As you can see we have two such documents, but the query returns only the one for Mark because the one for Mark isn”t indexed. If we query again, without a WHERE clause to retrieve all the documents in the collection, then we get a result set with both documents and this is because unindexed documents are always returned by queries that have no WHERE clause. We can also retrieve unindexed documents by their ID or self-link.
DocumentDB – Sorting Records
DocumentDB – Sorting Records ”; Previous Next 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. Let’s take a look at the following example in which we have a JSON document. { “id”: “Food Menu”, “description”: “Grapes, red or green (European type, such as Thompson seedless), raw”, “tags”: [ { “name”: “grapes” }, { “name”: “red or green (european type” }, { “name”: “such as thompson seedless)” }, { “name”: “raw” } ], “foodGroup”: “Fruits and Fruit Juices”, “servings”: [ { “amount”: 1, “description”: “cup”, “weightInGrams”: 151 }, { “amount”: 10, “description”: “grapes”, “weightInGrams”: 49 }, { “amount”: 1, “description”: “NLEA serving”, “weightInGrams”: 126 } ] } Following is the SQL query to sort the result in a descending order. SELECT f.description, f.foodGroup, f.servings[2].description AS servingDescription, f.servings[2].weightInGrams AS servingWeight FROM f ORDER BY f.servings[2].weightInGrams DESC When the above query is executed, you will receive the following output. [ { “description”: “Grapes, red or green (European type, such as Thompson seedless), raw”, “foodGroup”: “Fruits and Fruit Juices”, “servingDescription”: “NLEA serving”, “servingWeight”: 126 } ] Print Page Previous Next Advertisements ”;