Category: documentdb Sql

DocumentDB SQL – Value Keyword ”; Previous Next When you know you”re only returning a single value, then the VALUE keyword can help produce a leaner result set by avoiding the overhead of creating a full-blown object. The VALUE keyword provides a way to return JSON value. Let’s take a look at a simple example. Following is the query with VALUE keyword. SELECT VALUE “Hello World, this is DocumentDB SQL Tutorial” When this query is executed, it returns the scalar “Hello World, this is DocumentDB SQL Tutorial”. [ “Hello World, this is DocumentDB SQL Tutorial” ] In another example, let’s consider the three documents from the previous examples. Following is the AndersenFamily document. { “id”: “AndersenFamily”, “lastName”: “Andersen”, “parents”: [ { “firstName”: “Thomas”, “relationship”: “father” }, { “firstName”: “Mary Kay”, “relationship”: “mother” } ], “children”: [ { “firstName”: “Henriette Thaulow”, “gender”: “female”, “grade”: 5, “pets”: [ { “givenName”: “Fluffy”, “type”: “Rabbit” } ] } ], “location”: { “state”: “WA”, “county”: “King”, “city”: “Seattle” }, “isRegistered”: true } Following is the SmithFamily document. { “id”: “SmithFamily”, “parents”: [ { “familyName”: “Smith”, “givenName”: “James” }, { “familyName”: “Curtis”, “givenName”: “Helen” } ], “children”: [ { “givenName”: “Michelle”, “gender”: “female”, “grade”: 1 }, { “givenName”: “John”, “gender”: “male”, “grade”: 7, “pets”: [ { “givenName”: “Tweetie”, “type”: “Bird” } ] } ], “location”: { “state”: “NY”, “county”: “Queens”, “city”: “Forest Hills” }, “isRegistered”: true } Following is the WakefieldFamily document. { “id”: “WakefieldFamily”, “parents”: [ { “familyName”: “Wakefield”, “givenName”: “Robin” }, { “familyName”: “Miller”, “givenName”: “Ben” } ], “children”: [ { “familyName”: “Merriam”, “givenName”: “Jesse”, “gender”: “female”, “grade”: 6, “pets”: [ { “givenName”: “Charlie Brown”, “type”: “Dog” }, { “givenName”: “Tiger”, “type”: “Cat” }, { “givenName”: “Princess”, “type”: “Cat” } ] }, { “familyName”: “Miller”, “givenName”: “Lisa”, “gender”: “female”, “grade”: 3, “pets”: [ { “givenName”: “Jake”, “type”: “Snake” } ] } ], “location”: { “state”: “NY”, “county”: “Manhattan”, “city”: “NY” }, “isRegistered”: false } Following is the query. SELECT VALUE f.location FROM Families f When this query is executed, it return the returns the address without the location label. [ { “state”: “NY”, “county”: “Manhattan”, “city”: “NY” }, { “state”: “NY”, “county”: “Queens”, “city”: “Forest Hills” }, { “state”: “WA”, “county”: “King”, “city”: “Seattle” } ] If we now specify the same query without VALUE Keyword, then it will return the address with location label. Following is the query. SELECT f.location FROM Families f When this query is executed, it produces the following output. [ { “location”: { “state”: “NY”, “county”: “Manhattan”, “city”: “NY” } }, { “location”: { “state”: “NY”, “county”: “Queens”, “city”: “Forest Hills” } }, { “location”: { “state”: “WA”, “county”: “King”, “city”: “Seattle” } } ] Print Page Previous Next Advertisements ”;

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

DocumentDB SQL – Where Clause ”; Previous Next In this chapter, we will cover the WHERE clause, which is also optional like FROM clause. It is used to specify a condition while fetching the data in the form of JSON documents provided by the source. Any JSON document must evaluate the specified conditions to be “true” to be considered for the result. If the given condition is satisfied, only then it returns specific data in the form of JSON document(s). We can use WHERE clause to filter the records and fetch only necessary records. We will consider the same three documents in this example. Following is the AndersenFamily document. { “id”: “AndersenFamily”, “lastName”: “Andersen”, “parents”: [ { “firstName”: “Thomas”, “relationship”: “father” }, { “firstName”: “Mary Kay”, “relationship”: “mother” } ], “children”: [ { “firstName”: “Henriette Thaulow”, “gender”: “female”, “grade”: 5, “pets”: [ { “givenName”: “Fluffy”, “type”: “Rabbit” } ] } ], “location”: { “state”: “WA”, “county”: “King”, “city”: “Seattle” }, “isRegistered”: true } Following is the SmithFamily document. { “id”: “SmithFamily”, “parents”: [ { “familyName”: “Smith”, “givenName”: “James” }, { “familyName”: “Curtis”, “givenName”: “Helen” } ], “children”: [ { “givenName”: “Michelle”, “gender”: “female”, “grade”: 1 }, { “givenName”: “John”, “gender”: “male”, “grade”: 7, “pets”: [ { “givenName”: “Tweetie”, “type”: “Bird” } ] } ], “location”: { “state”: “NY”, “county”: “Queens”, “city”: “Forest Hills” }, “isRegistered”: true } Following is the WakefieldFamily document. { “id”: “WakefieldFamily”, “parents”: [ { “familyName”: “Wakefield”, “givenName”: “Robin” }, { “familyName”: “Miller”, “givenName”: “Ben” } ], “children”: [ { “familyName”: “Merriam”, “givenName”: “Jesse”, “gender”: “female”, “grade”: 6, “pets”: [ { “givenName”: “Charlie Brown”, “type”: “Dog” }, { “givenName”: “Tiger”, “type”: “Cat” }, { “givenName”: “Princess”, “type”: “Cat” } ] }, { “familyName”: “Miller”, “givenName”: “Lisa”, “gender”: “female”, “grade”: 3, “pets”: [ { “givenName”: “Jake”, “type”: “Snake” } ] } ], “location”: { “state”: “NY”, “county”: “Manhattan”, “city”: “NY” }, “isRegistered”: false } Let’s take a look at a simple example in which WHERE clause is used. In this query, in WHERE clause, the (WHERE f.id = “WakefieldFamily”) condition is specified. SELECT * FROM f WHERE f.id = “WakefieldFamily” When the above query is executed, it will return the complete JSON document for WakefieldFamily as shown in the following output. [ { “id”: “WakefieldFamily”, “parents”: [ { “familyName”: “Wakefield”, “givenName”: “Robin” }, { “familyName”: “Miller”, “givenName”: “Ben” } ], “children”: [ { “familyName”: “Merriam”, “givenName”: “Jesse”, “gender”: “female”, “grade”: 6, “pets”: [ { “givenName”: “Charlie Brown”, “type”: “Dog” }, { “givenName”: “Tiger”, “type”: “Cat” }, { “givenName”: “Princess”, “type”: “Cat” } ] }, { “familyName”: “Miller”, “givenName”: “Lisa”, “gender”: “female”, “grade”: 3, “pets”: [ { “givenName”: “Jake”, “type”: “Snake” } ] } ], “location”: { “state”: “NY”, “county”: “Manhattan”, “city”: “NY” }, “isRegistered”: false, “_rid”: “Ic8LAJFujgECAAAAAAAAAA==”, “_ts”: 1450541623, “_self”: “dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgECAAAAAAAAAA==/”, “_etag”: “”00000500-0000-0000-0000-567582370000″”, “_attachments”: “attachments/” } ] Print Page Previous Next Advertisements ”;