<< SQL Server Hosting - HostForLIFE :: Email Notification On SQL Job Failure | SQL Server Hosting - HostForLIFE :: SQL CAST And CONVERT Function >>

SQL Server Hosting - HostForLIFE :: Everywhere JSON So Why Not In SQL Server - New Feature In SQL Server

February 7, 2022 07:49 by author

Peter

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

    Parse JSON by relation query
    Insert & update JSON using the query
    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.

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

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.

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

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.

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.

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)