Click here to Skip to main content
13,705,376 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

9.6K views
13 bookmarked
Posted 23 Apr 2018
Licenced CPOL

Everywhere JSON so why not in SQL SERVER–New feature in SQL SERVER 2016

, 23 Apr 2018
Rate this:
Please Sign up or sign in to vote.
New feature in SQL Server 2016

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

If you see, nowadays most of the NOSQL database like Microsoft Azure Document DB, MONGODB, etc. are also using JSON ecosystem and some of them are based on JSON.

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

In SQL SERVER 2016, JSON was introduced. This we can say is a step or bridge between NON-relational database and relational database 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 query
  3. Store JSON in database

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

The good thing in SQL SERVER 2016 for JSON 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 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 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 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.

JSON_Feature_Indiandotnet_1

When you run the above command, the result will be as shown in the below figure:

JSON_Feature_Indiandotnet_2

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 AdventureWorks Sales order table and joining that with product table to get sub-node. If you see in below image, we have added Root node as well. This root Node can be added in JSON auto as well if required.

JSON_Feature_Indiandotnet_3

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

JSON_Feature_Indiandotnet_4

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.

JSON_Feature_Indiandotnet_5

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.

JSON_Feature_Indiandotnet_6

5) OPENJSON Function 

This is a very beautiful function which you can use to parse external schema. Suppose, you got a JSON string from a mobile service which you will directly pass to SQL Sever and 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).

JSON_Feature_Indiandotnet_7

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

Please provide your inputs.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Rajat-Indiandotnet
Technical Lead
India India
No Biography provided

You may also be interested in...

Comments and Discussions

 
QuestionGreat document and a realy good read... Pin
Catelyn Hearne14-May-18 23:03
memberCatelyn Hearne14-May-18 23:03 
QuestionHow to insert / update data in table which is json Pin
Mou_kol23-Apr-18 22:58
memberMou_kol23-Apr-18 22:58 
AnswerRe: How to insert / update data in table which is json Pin
Rajat-Indiandotnet24-Apr-18 1:41
memberRajat-Indiandotnet24-Apr-18 1:41 
GeneralRe: How to insert / update data in table which is json Pin
Mike DiRenzo24-Apr-18 3:29
memberMike DiRenzo24-Apr-18 3:29 
GeneralRe: How to insert / update data in table which is json Pin
Mou_kol25-Apr-18 22:51
memberMou_kol25-Apr-18 22:51 
GeneralMy vote of 5 Pin
Mou_kol23-Apr-18 22:57
memberMou_kol23-Apr-18 22:57 
QuestionThank you Pin
Mike DiRenzo23-Apr-18 9:47
memberMike DiRenzo23-Apr-18 9:47 
AnswerRe: Thank you Pin
Rajat-Indiandotnet24-Apr-18 1:42
memberRajat-Indiandotnet24-Apr-18 1:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.180920.1 | Last Updated 23 Apr 2018
Article Copyright 2018 by Rajat-Indiandotnet
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid