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

Introduction

If you are a developer, you must have used JSON (JavaScript Object Notation), but if not, don't; you'll use it sooner rather than later. JSON is the ecosystem 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 developers.

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

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

In SQL SERVER, JSON was introduced. This was a step or bridge between NON-relational 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 for JSON 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. Then, you can parse and store it in a table structure (if req. Ined). In another case, an external source requires data in JSON format while data in SQL SERVER is in tabular format, so both purposes can easily be solved 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 the JSON object of the selected column where the column name is treated as a Key. In other words, we can say it will format the query result in JSON.

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

2) FOR JSON PATH

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

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 in the image, we added a Root node. This root Node can be added in JSON auto as well if required.

When you run the above query, we can get complex JSON objects 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, 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 wonderful function that you can use to parse external schema. Suppose you get 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. OPENJSON can easily handle parsing and another process. The only tweak here is that it required database compatibility level 130, which you must do (if not compatible with level 130).

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

Please, provide your input.