DocumentDB SQL – Useful Resources ”; Previous Next 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 DocumentDB SQL − Official Site of DocumentDB SQL Useful Books on DocumentDB SQL To enlist your site on this page, please drop an email to [email protected] Print Page Previous Next Advertisements ”;
DocumentDB SQL – Quick Guide ”; Previous Next 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. 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
DocumentDB SQL – Aliasing ”; Previous Next 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. 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. Print Page Previous Next Advertisements ”;
DocumentDB SQL – Between Keyword ”; Previous Next 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). 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” } ] } ] Print Page Previous Next Advertisements ”;
DocumentDB SQL – Scalar Expressions ”; Previous Next In DocumentDB SQL, the SELECT clause also supports scalar expressions like constants, arithmetic expressions, logical expressions, etc. Normally, scalar queries are rarely used, because they don”t actually query documents in the collection, they just evaluate expressions. But it”s still helpful to use scalar expression queries to learn the basics, how to use expressions and shape JSON in a query, and these concepts apply directly to the actual queries you”ll be running against documents in a collection. Let’s take a look at an example which contains multiple scalar queries. In the Query Explorer, select just the text to be executed and click ‘Run’. Let”s run this first one. SELECT “Hello” When the above query is executed, it produces the following output. [ { “$1”: “Hello” } ] This output may look a bit confusing, so let”s break it down. First, as we saw in the last demo, query results are always contained in square brackets because they are returned as a JSON array, even results from scalar expression queries like this one that only returns a single document. We have an array with one document in it, and that document has a single property in it for the single expression in the SELECT statement. The SELECT statement doesn”t provide a name for this property, thus DocumentDB auto generates one using $1. This is usually not what we want, which is why we can use AS to alias the expression in the query, which sets the property name in the generated document the way you”d like it to be, word, in this example. SELECT “Hello” AS word When the above query is executed, it produces the following output. [ { “word”: “Hello” } ] Similarly, following is another simple query. SELECT ((2 + 11 % 7)-2)/3 The query retrieves the following output. [ { “$1”: 1.3333333333333333 } ] Let’s take a look at another example of shaping nested arrays and embedded objects. SELECT { “words1”: [“Hello”, “World”], “words2”: [“How”, “Are”, “You?”] } AS allWords When the above query is executed, it produces the following output. [ { “allWords”: { “words1”: [ “Hello”, “World” ], “words2”: [ “How”, “Are”, “You?” ] } } ] Print Page Previous Next Advertisements ”;
DocumentDB SQL – Array Creation ”; Previous Next In DocumentDB SQL, Microsoft has added a key feature with the help of which we can easily create an array. It means when we run a query, then as a result it will create an array of collection similar to JSON object as a result of query. Let’s consider the same documents as in the previous examples. Following is the AndersenFamily document. { “id”: “AndersenFamily”, “lastName”: “Andersen”, “parents”: [ { “firstName”: “Thomas”, “relationship”: “father” }, { “firstName”: “Mary Kay”, “relationship”: “mother” } ], “children”: [ { “firstName”: “Henriette Thaulow”, “gender”: “female”, “grade”: 5, “pets”: [ { “givenName”: “Fluffy”, “type”: “Rabbit” } ] } ], “location”: { “state”: “WA”, “county”: “King”, “city”: “Seattle” }, “isRegistered”: true } Following is the SmithFamily document. { “id”: “SmithFamily”, “parents”: [ { “familyName”: “Smith”, “givenName”: “James” }, { “familyName”: “Curtis”, “givenName”: “Helen” } ], “children”: [ { “givenName”: “Michelle”, “gender”: “female”, “grade”: 1 }, { “givenName”: “John”, “gender”: “male”, “grade”: 7, “pets”: [ { “givenName”: “Tweetie”, “type”: “Bird” } ] } ], “location”: { “state”: “NY”, “county”: “Queens”, “city”: “Forest Hills” }, “isRegistered”: true } Following is the WakefieldFamily document. { “id”: “WakefieldFamily”, “parents”: [ { “familyName”: “Wakefield”, “givenName”: “Robin” }, { “familyName”: “Miller”, “givenName”: “Ben” } ], “children”: [ { “familyName”: “Merriam”, “givenName”: “Jesse”, “gender”: “female”, “grade”: 6, “pets”: [ { “givenName”: “Charlie Brown”, “type”: “Dog” }, { “givenName”: “Tiger”, “type”: “Cat” }, { “givenName”: “Princess”, “type”: “Cat” } ] }, { “familyName”: “Miller”, “givenName”: “Lisa”, “gender”: “female”, “grade”: 3, “pets”: [ { “givenName”: “Jake”, “type”: “Snake” } ] } ], “location”: { “state”: “NY”, “county”: “Manhattan”, “city”: “NY” }, “isRegistered”: false } Let’s take a look at an example. Following is the query which will return the family name and address of each family. SELECT f.id AS FamilyName, [f.location.city, f.location.county, f.location.state] AS Address FROM Families f As can be seen city, county and state fields are enclosed in square brackets, which will create an array and this array is named Address. When the above query is executed, it produces the following output. [ { “FamilyName”: “WakefieldFamily”, “Address”: [ “NY”, “Manhattan”, “NY” ] }, { “FamilyName”: “SmithFamily”, “Address”: [ “Forest Hills”, “Queens”, “NY” ] }, { “FamilyName”: “AndersenFamily”, “Address”: [ “Seattle”, “King”, “WA” ] } ] The city, county, and state information are added in the Address array in the above output. Print Page Previous Next Advertisements ”;
DocumentDB SQL – Composite SQL Queries ”; Previous Next Composite Query enables you to combine data from existing queries and then apply filters, aggregates, and so on before presenting the report results, which show the combined data set. Composite Query retrieves multiple levels of related information on existing queries and presents the combined data as a single and flattened query result. Using Composite Query, you also have the option to − Select the SQL pruning option to remove tables and fields that are not needed based on users’ attribute selections. Set the ORDER BY and GROUP BY clauses. Set the WHERE clause as a filter over the result set of a composite query. The above operators can be composed to form more powerful queries. Since DocumentDB supports nested collections, the composition can either be concatenated or nested. Let’s consider the following documents for this example. AndersenFamily document is as follows. { “id”: “AndersenFamily”, “lastName”: “Andersen”, “parents”: [ { “firstName”: “Thomas”, “relationship”: “father” }, { “firstName”: “Mary Kay”, “relationship”: “mother” } ], “children”: [ { “firstName”: “Henriette Thaulow”, “gender”: “female”, “grade”: 5, “pets”: [ { “givenName”: “Fluffy”, “type”: “Rabbit” } ] } ], “location”: { “state”: “WA”, “county”: “King”, “city”: “Seattle” }, “isRegistered”: true } SmithFamily document is as follows. { “id”: “SmithFamily”, “parents”: [ { “familyName”: “Smith”, “givenName”: “James” }, { “familyName”: “Curtis”, “givenName”: “Helen” } ], “children”: [ { “givenName”: “Michelle”, “gender”: “female”, “grade”: 1 }, { “givenName”: “John”, “gender”: “male”, “grade”: 7, “pets”: [ { “givenName”: “Tweetie”, “type”: “Bird” } ] } ], “location”: { “state”: “NY”, “county”: “Queens”, “city”: “Forest Hills” }, “isRegistered”: true } WakefieldFamily document is as follows. { “id”: “WakefieldFamily”, “parents”: [ { “familyName”: “Wakefield”, “givenName”: “Robin” }, { “familyName”: “Miller”, “givenName”: “Ben” } ], “children”: [ { “familyName”: “Merriam”, “givenName”: “Jesse”, “gender”: “female”, “grade”: 6, “pets”: [ { “givenName”: “Charlie Brown”, “type”: “Dog” }, { “givenName”: “Tiger”, “type”: “Cat” }, { “givenName”: “Princess”, “type”: “Cat” } ] }, { “familyName”: “Miller”, “givenName”: “Lisa”, “gender”: “female”, “grade”: 3, “pets”: [ { “givenName”: “Jake”, “type”: “Snake” } ] } ], “location”: { “state”: “NY”, “county”: “Manhattan”, “city”: “NY” }, “isRegistered”: false } Let’s take a look at an example of concatenated query. Following is the query which will retrieve the id and location of the family where the first child givenName is Michelle. SELECT f.id,f.location FROM Families f WHERE f.children[0].givenName = “Michelle” When the above query is executed, it produces the following output. [ { “id”: “SmithFamily”, “location”: { “state”: “NY”, “county”: “Queens”, “city”: “Forest Hills” } } ] Let’s consider another example of concatenated query. Following is the query which will return all the documents where the first child grade greater than 3. SELECT * FROM Families f WHERE ({grade: f.children[0].grade}.grade > 3) When the above query is executed, it produces the following output. [ { “id”: “WakefieldFamily”, “parents”: [ { “familyName”: “Wakefield”, “givenName”: “Robin” }, { “familyName”: “Miller”, “givenName”: “Ben” } ], “children”: [ { “familyName”: “Merriam”, “givenName”: “Jesse”, “gender”: “female”, “grade”: 6, “pets”: [ { “givenName”: “Charlie Brown”, “type”: “Dog” }, { “givenName”: “Tiger”, “type”: “Cat” }, { “givenName”: “Princess”, “type”: “Cat” } ] }, { “familyName”: “Miller”, “givenName”: “Lisa”, “gender”: “female”, “grade”: 3, “pets”: [ { “givenName”: “Jake”, “type”: “Snake” } ] } ], “location”: { “state”: “NY”, “county”: “Manhattan”, “city”: “NY” }, “isRegistered”: false, “_rid”: “Ic8LAJFujgECAAAAAAAAAA==”, “_ts”: 1450541623, “_self”: “dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgECAAAAAAAAAA==/”, “_etag”: “”00000500-0000-0000-0000-567582370000″”, “_attachments”: “attachments/” }, { “id”: “AndersenFamily”, “lastName”: “Andersen”, “parents”: [ { “firstName”: “Thomas”, “relationship”: “father” }, { “firstName”: “Mary Kay”, “relationship”: “mother” } ], “children”: [ { “firstName”: “Henriette Thaulow”, “gender”: “female”, “grade”: 5, “pets”: [ { “givenName”: “Fluffy”, “type”: “Rabbit” } ] } ], “location”: { “state”: “WA”, “county”: “King”, “city”: “Seattle” }, “isRegistered”: true, “_rid”: “Ic8LAJFujgEEAAAAAAAAAA==”, “_ts”: 1450541624, “_self”: “dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgEEAAAAAAAAAA==/”, “_etag”: “”00000700-0000-0000-0000-567582380000″”, “_attachments”: “attachments/” } ] Let’s take a look at an example of nested queries. Following is the query which will iterate all the parents and then return the document where familyName is Smith. SELECT * FROM p IN Families.parents WHERE p.familyName = “Smith” When the above query is executed, it produces the following output. [ { “familyName”: “Smith”, “givenName”: “James” } ] Let’s consider another example of nested query. Following is the query which will return all the familyName. SELECT VALUE p.familyName FROM Families f JOIN p IN f.parents When the above query is executed, it produces he following output. [ “Wakefield”, “Miller”, “Smith”, “Curtis” ] Print Page Previous Next Advertisements ”;
DocumentDB SQL – Linq to SQL Translation ”; Previous Next 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 Print Page Previous Next Advertisements ”;
DocumentDB SQL – JavaScript Integration ”; Previous Next These days JavaScript is everywhere, and not just in browsers. DocumentDB embraces JavaScript as a sort of modern day T-SQL and supports the transactional execution of JavaScript logic natively, right inside the database engine. DocumentDB provides a programming model for executing JavaScript-based application logic directly on the collections in terms of stored procedures and triggers. Let’s take a look at an example where we create a simple store procedure. Following are the steps − Step 1 − Create a new console applications. Step 2 − Add in the .NET SDK from NuGet. We are using the .NET SDK here, which means that we”ll be writing some C# code to create, execute, and then delete our stored procedure, but the stored procedure itself gets written in JavaScript. Step 3 − Right-click on the project in Solution explorer. Step 4 − Add a new JavaScript file for the stored procedure and call it HelloWorldStoreProce.js Every stored procedure is just a JavaScript function so we”ll create a new function and naturally we”ll also name this function HelloWorldStoreProce. It doesn”t matter if we give the function a name at all. DocumentDB will only refer to this stored procedure by the Id that we provide when we create it. function HelloWorldStoreProce() { var context = getContext(); var response = context.getResponse(); response.setBody(”Hello, and welcome to DocumentDB!”); } All the stored procedure does is obtain the response object from the context and call its setBody method to return a string to the caller. In C# code, we will create the stored procedure, execute it, and then delete it. Stored procedures are scoped per collection, therefore we will need the SelfLink of the collection to create the stored procedure. Step 5 − First query for the myfirstdb database and then for the MyCollection collection. Creating a stored procedure is just like creating any other resource in DocumentDB. private async static Task SimpleStoredProcDemo() { var endpoint = “https://azuredocdbdemo.documents.azure.com:443/”; var masterKey = “BBhjI0gxdVPdDbS4diTjdloJq7Fp4L5RO/StTt6UtEufDM78qM2CtBZWbyVwFPSJIm8AcfDu2O+AfV T+TYUnBQ==”; using (var client = new DocumentClient(new Uri(endpoint), masterKey)) { // Get database Database database = client .CreateDatabaseQuery(“SELECT * FROM c WHERE c.id = ”myfirstdb””) .AsEnumerable() .First(); // Get collection DocumentCollection collection = client .CreateDocumentCollectionQuery(database.CollectionsLink, “SELECT * FROM c WHERE c.id = ”MyCollection””) .AsEnumerable() .First(); // Create stored procedure var sprocBody = File.ReadAllText(@”….HelloWorldStoreProce.js”); var sprocDefinition = new StoredProcedure { Id = “HelloWorldStoreProce”, Body = sprocBody }; StoredProcedure sproc = await client. CreateStoredProcedureAsync(collection.SelfLink, sprocDefinition); Console.WriteLine(“Created stored procedure {0} ({1})”, sproc.Id, sproc.ResourceId); // Execute stored procedure var result = await client.ExecuteStoredProcedureAsync(sproc.SelfLink); Console.WriteLine(“Executed stored procedure; response = {0}”, result.Response); // Delete stored procedure await client.DeleteStoredProcedureAsync(sproc.SelfLink); Console.WriteLine(“Deleted stored procedure {0} ({1})”, sproc.Id, sproc.ResourceId); } } Step 6 − First create a definition object with the Id for the new resource and then call one of the Create methods on the DocumentClient object. In the case of a stored procedure, the definition includes the Id and the actual JavaScript code that you want to ship over to the server. Step 7 − Call File.ReadAllText to extract the stored procedure code out of the JS file. Step 8 − Assign the stored procedure code to the body property of the definition object. As far as DocumentDB is concerned, the Id we specify here, in the definition, is the name of the stored procedure, regardless of what we actually name the JavaScript function. Nevertheless when creating stored procedures and other server-side objects, it is recommended that we name JavaScript functions and that those function names do match the Id that we have set in the definition for DocumentDB. Step 9 − Call CreateStoredProcedureAsync, passing in the SelfLink for the MyCollection collection and the stored procedure definition. This creates the stored procedure and ResourceId that DocumentDB assigned to it. Step 10 − Call the stored procedure. ExecuteStoredProcedureAsync takes a type parameter that you set to the expected data type of the value returned by the stored procedure, which you can specify simply as an object if you want a dynamic object returned. That is an object whose properties will be bound at run-time. In this example we know that our stored procedure is just returning a string and so we call ExecuteStoredProcedureAsync<string>. Following is the complete implementation of Program.cs file. using Microsoft.Azure.Documents; using Microsoft.Azure.Documents.Client; using Microsoft.Azure.Documents.Linq; using System; using System.Collections.Generic; using System.Diagnostics; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; namespace DocumentDBStoreProce { class Program { private static void Main(string[] args) { Task.Run(async () => { await SimpleStoredProcDemo(); }).Wait(); } private async static Task SimpleStoredProcDemo() { var endpoint = “https://azuredocdbdemo.documents.azure.com:443/”; var masterKey = “BBhjI0gxdVPdDbS4diTjdloJq7Fp4L5RO/StTt6UtEufDM78qM2CtBZWbyVwFPSJIm8AcfDu2O+AfV T+TYUnBQ==”; using (var client = new DocumentClient(new Uri(endpoint), masterKey)) { // Get database Database database = client .CreateDatabaseQuery(“SELECT * FROM c WHERE c.id = ”myfirstdb””) .AsEnumerable() .First(); // Get collection DocumentCollection collection = client .CreateDocumentCollectionQuery(database.CollectionsLink, “SELECT * FROM c WHERE c.id = ”MyCollection””) .AsEnumerable() .First(); // Create stored procedure var sprocBody = File.ReadAllText(@”….HelloWorldStoreProce.js”); var sprocDefinition = new StoredProcedure { Id = “HelloWorldStoreProce”, Body = sprocBody }; StoredProcedure sproc = await client .CreateStoredProcedureAsync(collection.SelfLink, sprocDefinition); Console.WriteLine(“Created stored procedure {0} ({1})”, sproc .Id, sproc.ResourceId); // Execute stored procedure var result = await client .ExecuteStoredProcedureAsync<string>(sproc.SelfLink); Console.WriteLine(“Executed stored procedure; response = {0}”, result.Response); // Delete stored procedure await client.DeleteStoredProcedureAsync(sproc.SelfLink); Console.WriteLine(“Deleted stored procedure {0} ({1})”, sproc.Id, sproc.ResourceId); } } } } When the above code is executed, it produces the following output. Created stored procedure HelloWorldStoreProce (Ic8LAMEUVgACAAAAAAAAgA==) Executed stored procedure; response = Hello, and welcome to DocumentDB! As seen in the above output, the response property has the “Hello, and welcome to DocumentDB!” returned by our stored procedure. Print Page Previous Next Advertisements ”;
Discuss DocumentDB SQL ”; 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 talks about querying documents using the special version of SQL supported by DocumentDB with illustrative examples. Print Page Previous Next Advertisements ”;