In this article I will show you similarities and differences between SQl Server JSON and DocumentDB query syntax.
SQL Server 2016/Azure SQL Db are relational databases that will have support for handling JSON data. JSON support in SQL Server uses standard T-SQL syntax for querying JSON data with some additional built-in functions that enables you to read values of objects from JSON data. JSON can be used with all components/features in SQL Server such as In-memory OLTP, Column store, temporal, etc.
Both SQL Server 2016 and DocumentDB enable you to query JSON documents.DocumentDB has nice syntax for querying JSON documents – you can find some good examples on DocumentDB site . Sql Server provides built-in functions for accessing JSON fields (JSON_VALUE), fragments (JSON_QUERY) and opening JSON documents (OPENJSON). In this post, I will show you some queries that can be executed in DocumentDB and equivalent Sql Server queries.
I have used DocumentDB as a reference because it has easy-to-understand syntax, so I believe that you will easily understand how to use equivalent Sql Server queries.
DocumentDB stores JSON documents in collections. Since Sql Server does not have collections we will create simple table that would contain documents same as in the DocumentDB site :
You can optionally add a primary key or any other index if you want; however, this is not required for this tutorial. The same collection is created in DocumentDB examples , so you can easily compare results of execution in both systems.
We can start with the simplest queries. For example, the following query will return the documents where the id field matches AndersenFamily:
Equivalent query in Sql Server would be:
In Sql Server you can use JSON_VALUE function to access value of JSON property on some path.
In DocumentDB you can select all states in address objects in family documents:
Equivalent Sql Server query would be:
In DocumentDB If some families don’t have an address.state value, they will be excluded in the query result. Therefore we need to add WHERE clause to exclude them because in SQL Server they will be returned as NULL values.
This query projects a Name and City, when the address' city has the same name as the state. In this case, "NY, NY" matches.
Equivalent Sql Server query is:
SQL Server provides you separate function OPENJSON that can open json document in the doc column and you can specify what fields you want to use as well as the types of the fields:
Beside the fact that fields are strongly types you can directly use their aliases in the queries. This might help if you have a lot of properties in the same query.
If you don’t want to use path you can name you property following path syntax:
Sql Server will use path syntax in the property names and lookup properties on the same paths.
In DocumentDB the IN keyword can be used to check whether a specified value matches any value in a list. For example, this query returns all family documents where the id is one of "WakefieldFamily" or "AndersenFamily".
Equivalent Sql Server query would use standard T-Sql IN predicate:
If you have complex JSON documents you might want to access various sub-entities such as children or pets in the example above. The following DocumentDB query returns all children objects from family documents:
Equivalent Sql server query would look like:
JSON_QUERY function is similar to JSON_VALUE. The main difference is that it returns JSON fragments (e.g. entire JSON sub-objects or sub-arrays within the documents), while JSON_VALUE returns scalars (i.e. number, strings true/false values). In this case we are using this function to return child objects at $.children path.
Now let's look at another query that performs iteration over children in the collection. Note the difference in the output array. This example splits children and flattens the results into a single array.
Equivalent Sql server query is:
OPENJSON will open all JSON objects in the $.children array and return them as dynamically created table rows. Each JSON element in the array is returned as value column.
In DocumentDB we can combine fragments and scalar values in the same query:
Equivalent Sql server query would combine JSON_VALUE and JSON_QUERY functions:
Equivalent query with OPENJSON function is:
Specifying columns in WITH clause is equivalent to JSON_VALUE function. If you want to reference sub-object (like in JSON_QUERY) you would need to specify NVARCHAR(max) type and add AS JSON clause. Without AS JSON clause, OPENJSON will assume that you want scalar property with the same name and since it will not find it, it will return NULL.
The following query returns all family documents in which the first child's grade is between 1-5 (both inclusive):
In Sql Server we can use one the following query:
If you want to avoid CAST function, you can use OPENJSON with WITH schema:
Following query returns names of children at eight grades:
Equivalent Sql Server query is:
One of the important functionalities is ability to join parent object with their child entities. In DocumentDB you use JOIN clause to join families and their children the same way as yo would do in Sql Server with relational tables:
Joins between parent and child objects are similar to joins between parent and child tables in Sql server. If you want to join parent and child JSON entities in Sql Server, you can open it using CROSS APPLY operator:
You can also format results as JSON text using FOR JSON clause:
Behavior of JOIN in DocumentDB is similar to the behavior of CROSS APPLY in Sql Server. In the following example, the result is empty since the cross product of each document from source and an empty set is empty:
In Sql server you will also not get any results if CROSS APPLY references non-existing array:
In the most complex case, you might join several parent/child objects to get something like the following pseudo-code:
In DocumentDB you can use multiple JOIN conditions:
In Sql Server you would use multiple CROSS APPLY operators, as it is shown in the following query:
As an alternative you can use OPENJSON with schema:
Although syntax in SQL Server and DocumentDB is slightly different, you might notice that most of the queries you can write in both systems.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.