Using Json In SQL Server 2016

      JSON (JavaScript Object Notation) is a lightweight data interchange format and it is set of data with key value pairs. This is awesome feature introduced in SQL 2016. Some built in function help to handle Json data in SQL server 2016. We can use Json data instead of table type in procedures to pass set of data.

Below are the list of built in function introduced in SQL 2016.

  • ISJSON
  • JSON_VALUE
  • JSON_QUERY
  • JSON_MODIFY
  • FOR JSON
  • OPENJSON

ISJSON – This will check inputted string is Json formatted or not,If it is Json return 1 else 0.

DECLARE @JSONData AS NVARCHAR(MAX)


SET @JSONData = N'{ "FirstName":"Michael", "LastName":"Jackson" }'

SELECT ISJSON(@JSONData) AS IsJson

JSON_VALUE – This will help to extract each column value from Json String, Or we can extract Json data like a table.

DECLARE @JSONData AS NVARCHAR(MAX)
SET @JSONData = N'{ 
    "CustInfo":{ 
        "FirstName":"
Michael",
        "LastName":"
Jackson",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"}, 
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"} 
        ]
    }
}'

SELECT
JSON_VALUE(@JSONData,'$.CustInfo.FirstName') AS FirstName,
JSON_VALUE(@JSONData,'$.CustInfo.LastName') AS LastName,
JSON_VALUE(@JSONData,'$.CustInfo.CustId') AS CustomerId,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[0].AddressType') AS AddressType,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[0].City') AS City,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[0].State') AS State,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[1].AddressType') AS AddressType2,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[1].City') AS City2,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[1].State') AS State2







JSON_QUERY – This is used to extract array of Json data or array of Json string.

DECLARE @JSONData AS NVARCHAR(MAX)

SET @JSONData = N'{ 

    "CustInfo":{ 
       
"FirstName":"Michael",
        "LastName":"
Jackson", 
        "CustId":"10002569", 
        "Addresses":[ 
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"}, 
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"} 
        ] 
    } 
}' 

SELECT JSON_QUERY(@JSONData,'$.CustInfo.Addresses')

UNION ALL

SELECT JSON_QUERY(@JSONData,'$.CustInfo.Addresses[1]')








JSON_MODIFY – This help to modify,insert,update or append value to Json string.

DECLARE @JSONData AS NVARCHAR(MAX)

SET @JSONData = N'{ 
    "CustInfo":{ 
       
"FirstName":"Michael",
        "LastName":"
Jackson", 
        "CustId":"10002569", 
        "Addresses":[ 
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"}, 
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"} 
        ] 
    } 
}'


/*Inserting Json*/
SET @JSONData = JSON_MODIFY(@JSONData,'$.CustInfo.Gender', 'Male')
PRINT @JSONData

/*Updating Json*/
SET @JSONData = JSON_MODIFY(@JSONData,'$.CustInfo.Gender', '1')
PRINT @JSONData

/*Delete Json*/
SET @JSONData = JSON_MODIFY(@JSONData,'$.CustInfo.Gender', NULL)
PRINT @JSONData

/*Multiple Field Update*/
SET @JSONData = JSON_MODIFY(JSON_MODIFY(@JSONData,'$.CustInfo.FirstName', 'Jack'),'$.CustInfo.LastName','Dani')
PRINT @JSONData

/*Append Json*/
SET @JSONData = JSON_MODIFY(@JSONData,'append $.CustInfo.ContactNo',JSON_QUERY('{"Mobile" :"989500000","LandLine" :"04950008"}'))

{

    "CustInfo":{
       "FirstName":"Michael",
        "LastName":"Jackson",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"},
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"}
        ]
    ,"Gender":"Male"}  Inserted
}
{

    "CustInfo":{
       "FirstName":"Michael",
        "LastName":"Jackson",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"},
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"}
        ]
    ,"Gender":"1"} Updated
}
{

    "CustInfo":{
       "FirstName":"Michael",
        "LastName":"Jackson",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"},
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"}
        ]
  Deleted    
  } 
}
{

    "CustInfo":{
       "FirstName":"Jack", Multi Update
        "LastName":"Dani",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"},
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"}
        ]
    }
}
{

    "CustInfo":{
       "FirstName":"Jack",
        "LastName":"Dani",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"},
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"}
        ]
    ,"ContactNo":[{"Mobile" :"989500000","LandLine" :"04950008"}]} Appended
}
 

FOR JSON – Function FOR JSON help to export SQL table data to Json format.Its similar to FOR XML function.

DECLARE @TempData AS Table(Name VARCHAR(10),Age INT)
INSERT INTO @TempData
SELECT 'A',10
UNION ALL
SELECT 'B',25
UNION ALL
SELECT 'C',25

SELECT *
FROM @TempData
FOR JSON PATH







OPENJSON – Using open Json we can extract Json data in schema format. Below example extract the Json data and convert it to table schema as like SQL table.


DECLARE @JSONData AS NVARCHAR(MAX)
SET @JSONData = N'{ 
       
"FirstName":"Michael",
        "LastName":"
Jackson",
 
        "CustId":"10002569", 
        "Addresses":[ 
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"}, 
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"} 
        ] 
    }' 



SELECT
   CustId,FirstName, LastName, AddressType, City, State
FROM OPENJSON(@JSONData)
WITH
(
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  CustId INT,
  Addresses NVARCHAR(MAX) as json
) AS Cust
CROSS APPLY OPENJSON(Cust.Addresses)
WITH
(
  AddressType INT,
  City VARCHAR(50),
  State VARCHAR(50)
) AD













No comments:

Post a Comment