SQL Tips - Everywhere JSON So Why Not In SQL Server - New Feature In SQL Server

If you are a developer then surely you must have used JSON (JavaScript Object Notation) but if not, then don’t worry, you'll use it sooner rather than later. JSON is a kind of ecosystem that is most popular in various areas for exchanging data. If you talk about charting solutions, AJAX, Mobile services, or any 3rd party integration then generally JSON is the first choice of the developers.

Nowadays most of the NoSQL databases like Microsoft Azure Document DB, MongoDB, etc. also use JSON ecosystem and some of them are based on JSON.

As it is such a popular growing system, why not in SQL SERVER?

In SQL SERVER 2016 JSON was introduced. This was step or bridge between NON-relational databases and relational databases by Microsoft SQL SERVER.

SQL Server 2016 provides the following capabilities when you are using JSON

  1. Parse JSON by relation query
  2. Insert & update JSON using the query
  3. Store JSON in the database

If you see it then conceptually it is similar to an XML data type which you might use in SQL SERVER.

The good thing in SQL SERVER 2016 for JSON is there is no Native data type. This will help in migration from any NoSQL to SQL SERVER.

SQL Server provides bidirectional JSON formatting which you can utilize in various ways. Suppose data is coming from the external source in the JSON format then you can parse it and store it in table structure (if required) in another case external source requires data in JSON format while data in SQL SERVER in tabular format so both the purpose can easily solve with SQL SERVER’s JSON feature.

Now, let’s jump directly to the practical to check JSON capabilities in SQL SERVER

1) FOR JSON AUTO

It is similar to FOR XML AUTO. It will return JSON object of selected column where column name is treated as a Key or in other words we can say it will format the query result in JSON.

SQL Tips - Everywhere JSON So Why Not In SQL Server - New Feature In SQL Server

when you run the above command the result will be like as shown in the below figure.

SQL Tips - Everywhere JSON So Why Not In SQL Server - New Feature In SQL Server

2) FOR JSON PATH

It’s exactly like JSON auto, the only difference is instead of SQL SERVER we have full control over the format. JSON Auto takes predefined column schema while with JSON path we can create a complex object.

For example, we are using the AdventureWorks Sales order table and joining that with the product table to get a sub-node. As you see below the image we have added a Root node as well. This root Node can be added in JSON auto as well if required.

SQL Tips - Everywhere JSON So Why Not In SQL Server - New Feature In SQL Server

Now, when you run the above query we can get complex JSON object as follows

SQL Tips - Everywhere JSON So Why Not In SQL Server - New Feature In SQL Server

3) IsJSON function

By the name, it is clear that this is a validating function.

To cross-check whether the provided string is a valid JSON or not we can run ISJSON.

SQL Tips - Everywhere JSON So Why Not In SQL Server - New Feature In SQL Server

4) JSON_VALUE

By the name, it is clear that if you want to get the value of the particular key of JSON then you can use this beautiful function which is JSON_VALUE.

SQL Tips - Everywhere JSON So Why Not In SQL Server - New Feature In SQL Server

5) OPENJSON function

This is a very beautiful function that you can use to parse external schema. Suppose, you got a JSON string from a mobile service that you will directly pass to SQL Server, and the SQL SERVER stored procedure will do the rest of the operation to parse it. The parsing and other operation can be easily handled by OPENJSON. The only tweak here is that it required database compatibility level 130 which you need to do (if not compatible with level 130)

SQL Tips - Everywhere JSON So Why Not In SQL Server - New Feature In SQL Server

There are many other interesting things which we will cover later.

Please, provide your input.