SQL Feathers: July 2019

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

Using Json In SQL Server 2016

Blogger Data