Everywhere JSON so why not in SQL SERVER–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 the developers.

 

If you see nowadays most of the NOSQL database like Microsoft Azure Document DB, MONGODB etc. 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 introduced. This we can say a step or bridge between NON-relation database and relational database by Microsoft SQL SERVER

 

SQL Server 2016 providing 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 a various way. 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 require 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.

 

JSON_Feature_Indiandotnet_1

when you run above command the result will be like as shown in 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 take 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 rest of the operation to parse it. The parsing and other operation can be easily handled by OPENJSON. The only tweak here 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.

RJ

Here Comes New Idea of Split String in SQL SERVER 2016 #3

In the Series of SQL SERVER 2016, this is another post. Before Jumping in detail just think if you have a comma or other separator string and if you have to split it by separator field then for such task  in previous SQL SERVER versions either you will write a function which split the string and return desire values in a column  or

you will use XML function or  might be different custom functions.

Let me explain this with below example. Suppose you have a string like below

DECLARE @FriendList AS VARCHAR(1000)

SET @FriendList =’Ravi,Suyash,Vaibhav,Shyam,Pankaj,Rajul,Javed’

 

Now you want output like below

output_of

 

Then in such cases, you will  follow 2 approaches (their might be other as well)

Approach 1:- Write  a function like below  and use it.

Traditional_way_Split_String_Indiandotnet

And once this function is created you can use like below

use_of_Split_Function_in_SQL_Indiandotnet

Approach 2 :- You can use XML option in SQL SERVER as  shown in below

String_split_by_XML_SQL_SERVER

So, the good news is now in SQL SERVER 2016 you don’t need to write  so many lines to split any string. In SQL SERVER 2016 a new string function is Introduced which is

STRING_SPLIT

The use of this function is very easy and below is the syntax

STRING_SPLIT (string, separator)

Now, let me show you same output using STRING_SPLIT function

string_Split_Function_sql_server_2016_Indiandotnet

Isn’t it easy ?

I hope you will like this easy way to split the string.

Provide your feedback.

RJ !!!