Category: documentdb Sql

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 – From Clause ”; Previous Next 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 } 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. 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. Print Page Previous Next Advertisements ”;

DocumentDB SQL – In Keyword ”; Previous Next 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. 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/” } ] Print Page Previous Next Advertisements ”;

DocumentDB SQL – Iteration ”; Previous Next 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. 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. 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. Print Page Previous Next Advertisements ”;

DocumentDB SQL – Operators ”; Previous Next 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. 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” } ] } ] Print Page Previous Next Advertisements ”;

DocumentDB SQL – Select Clause ”; Previous Next 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 − Click ‘Run’ to execute query and open the 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. Print Page Previous Next Advertisements ”;

DocumentDB SQL – Order By Clause ”; 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. 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. 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. 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” } ] Print Page Previous Next Advertisements ”;

DocumentDB SQL Tutorial PDF Version Quick Guide Resources Job Search Discussion 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. Audience This tutorial is designed for developers who want to get acquainted with how to query DocumentDB using a familiar Structured Query Language (SQL). 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. Print Page Previous Next Advertisements ”;