Whats New in SQL 2016 – Native JSON Function Support – Performing Data Manipulations in JSON Documents

This is the last and the final article in the series which discusses on the native JSON support in SQL 2016.
Previously I had blogged on how JSON documents can be generated from relational data using FOR JSON construct here

http://visakhm.blogspot.ae/2016/06/whats-new-in-sql-2016-native-json.html

and also how JSON documents can be parsed and relational data can shred from them using OPENJSON here
http://visakhm.blogspot.ae/2016/07/whats-new-in-sql-2016-native-json.html

The article explains on some of the JSON functions which is available in Transact SQL  and can be utilized for manipulating the data within JSON documents.

ISJSON()

The ISJSON function is used to check if a document passed has a valid JSON data. It accepts an expression which would be a string and validates if it contains aproper JSON data. The return value would be a boolean result (1 or 0) based on whether the string contains a valid JSON result or not.
Lets see the small illustration below to understand the ISJSON function


declare @x nvarchar(max)='{
  “Items”: [
    {
      “ItemID”: 101,
      “ItemDesc”: “Monitor CRT 18″”,
      “Unitprice”: 200
    },
    {
      “ItemID”: 110,
      “ItemDesc”: “Printer Catridge”,
      “Unitprice”: 112
    },
    {
      “ItemID”: 112,
      “ItemDesc”: “Copier Ink”,
      “Unitprice”: 20
    },
    {
      “ItemID”: 123,
      “ItemDesc”: “Wireless Mouse”,
      “Unitprice”: 30
    }
  ]
}’

SELECT ISJSON(@x)

The result will be 1 as @x contains valid JSON data

If at all the passed document can be considered as a valid JSON, ISJSON function returns 1 as seen from the below examples

SELECT *
FROM
(
VALUES (‘{}’),(‘[]’),(‘{“item”:1}’),(‘{“q”:[1]}’),(‘[0,1,2]’)
)t(v)
WHERE ISJSON(v)=1

and the result

The first two values represents blank JSON object and array documents whereas the next three represents JSON document with single object/array object and all of them are valid.
One more thing you may note from the above illustration is how we can use ISJSON function as a filter condition in the WHERE clause to check for the rows with valid JSON data in the column and then to do some manipulations on them.

JSON_VALUE()

JSON_VALUE is another very useful utility function which can be used to parse a JSON document and return a single element value. JSON_VALUE takes as argument a JSON expression and a path and traverses the JSON document as per the path to return a single scalar value.

Lets see a simple example of how JSON_VALUE can be applied to get values from a JSON document

declare @x nvarchar(max)='{
  “Item”: 
    {
      “ItemID”: 101,
      “ItemDesc”: “Monitor CRT 18″”,
      “Unitprice”: 200
    }
}’

The above shows a very simple JSON document with a single JSON element having three key value pairs.
The values from the above JSON document can be parsed out using JSON_VALUE function as per below code

SELECT JSON_VALUE(@x,’$.Item.ItemID’) AS ItemID,
JSON_VALUE(@x,’$.Item.ItemDesc’) AS ItemDesc,
JSON_VALUE(@x,’$.Item.Unitprice’) AS Unitprice
The result is as below

This is a simple document with all key values in the same level. Now lets see how we can use JSON_VALUE in the case where JSON document has key values in multiple levels

Consider the below JSON example

declare @x nvarchar(max) = ‘{
     “Order”: {
      “OrderDesc”: “Order 1”,
      “OrderDate”: “2013-02-12T00:00:00”,
      “ShippedDate”: “2013-02-20T00:00:00”,
      “WebOrder”: false,
      “OrderItems”: 
        {
          “OrderQty”: 10,
          “Item”: {
              “ItemDesc”: “Wireless Mouse”,
              “Unitprice”: 30,
              “SUbItem”: {
                  “SubItemDesc”: “SP17”
                }
            }
         }
}’

Now to get the data from the various levels within the document we can apply JSON_VALUE function as per below

SELECT JSON_VALUE(@x,’$.Order.OrderDesc’) AS OrderDesc,
JSON_VALUE(@x,’$.Order.OrderDate’) AS OrderDate,
JSON_VALUE(@x,’$.Order.ShippedDate’) AS ShippedDate,
JSON_VALUE(@x,’$.Order.WebOrder’) AS WebOrder,
JSON_VALUE(@x,’$.Order.OrderItems.OrderQty’) AS OrderQty,
JSON_VALUE(@x,’$.Order.OrderItems.Item.ItemDesc’) AS ItemDesc,
JSON_VALUE(@x,’$.Order.OrderItems.Item.Unitprice’) AS Unitprice,
JSON_VALUE(@x,’$.Order.OrderItems.Item.SUbItem.SubItemDesc’) AS SubItemDesc

The corresponding result is given below

One thing to note here is that JSON_VALUE can return only a scalar value from within a single key value pair in the JSON data. So in case you’re referring to a JSON array object which includes multiple key value pairs as per the path specified it will return NULL under the default mode.
This can be illustrated using the below example

declare @x nvarchar(max) = ‘{
  “Order”: 
    {
      “OrderDesc”: “Order 1”,
      “OrderDate”: “2013-02-12T00:00:00”,
      “ShippedDate”: “2013-02-20T00:00:00”,
      “WebOrder”: false,
      “OrderItems”:
        {
          “OrderQty”: 10,
          “Item”: [
            {
              “ItemDesc”: “Wireless Mouse”,
              “Unitprice”: 30,
              “SUbItem”: [
                {
                  “SubItemDesc”: “SP17”
                },
{
 “SubItemDesc”: “SP22”
}
    ]
            },
{
              “ItemDesc”: “Monitor”,
              “Unitprice”: 22,
              “SUbItem”: [
                {
                  “SubItemDesc”: “SP27”
                }
]
            }
]
        }
   }
}’

SELECT JSON_VALUE(@x,’$.Order.OrderDesc’) AS OrderDesc,
JSON_VALUE(@x,’$.Order.OrderDate’) AS OrderDate,
JSON_VALUE(@x,’$.Order.ShippedDate’) AS ShippedDate,
JSON_VALUE(@x,’$.Order.WebOrder’) AS WebOrder,
JSON_VALUE(@x,’$.Order.OrderItems.OrderQty’) AS OrderQty,
JSON_VALUE(@x,’$.Order.OrderItems.Item.ItemDesc’) AS ItemDesc,
JSON_VALUE(@x,’$.Order.OrderItems.Item.Unitprice’) AS Unitprice,
JSON_VALUE(@x,’$.Order.OrderItems.Item.SUbItem.SubItemDesc’) AS SubItemDesc

If you try the above illustration the result obtained will be something like below

Analyze the result and you will find that the code is not able to retrieve any keys at the Item and SUbtem level using JSON_VALUE function.
If you check the JSON document carefully you can see that Item and SUBItem consists of actually an array object (notice the [] around them) rather than a single JSON element. This is why JSON_VALUE cant extract value from them. As previously explained the JSON_VALUE can only extract a single scalar value which is why it fails in the above scenario.
For the above case if we want to get the values we need to tweak the query as below to get the Item and SUbItem level key values

The modified query would be like this

SELECT JSON_VALUE(@x,’$.Order.OrderDesc’) AS OrderDesc,
JSON_VALUE(@x,’$.Order.OrderDate’) AS OrderDate,
JSON_VALUE(@x,’$.Order.ShippedDate’) AS ShippedDate,
JSON_VALUE(@x,’$.Order.WebOrder’) AS WebOrder,
JSON_VALUE(@x,’$.Order.OrderItems.OrderQty’) AS OrderQty,
JSON_VALUE(@x,’$.Order.OrderItems.Item[0].ItemDesc’) AS ItemDesc1,
JSON_VALUE(@x,’$.Order.OrderItems.Item[0].Unitprice’) AS Unitprice1,
JSON_VALUE(@x,’$.Order.OrderItems.Item[0].SUbItem[0].SubItemDesc’) AS SubItemDesc11,
JSON_VALUE(@x,’$.Order.OrderItems.Item[0].SUbItem[1].SubItemDesc’) AS SubItemDesc12,
JSON_VALUE(@x,’$.Order.OrderItems.Item[1].ItemDesc’) AS ItemDesc2,
JSON_VALUE(@x,’$.Order.OrderItems.Item[1].Unitprice’) AS Unitprice2,
JSON_VALUE(@x,’$.Order.OrderItems.Item[1].SUbItem[0].SubItemDesc’) AS SubItemDesc21

And the result below

But this method is not scalable as we have to be certain on the number of elements at query time to apply the position number based static logic. In such cases we would require logic which will iterate through the JSON elements to return corresponding key values. This will be explained in detail later in this article.

strict and lax modes

There are two modes under which the path expression works in the case of JSON functions. 
The default is the lax mode where the parser doesn’t enforce strict checking for the JSON path specified. In case the path is valid it returns the value as per the path. Otherwise it will return a NULL value. lax mode being the default option you dont need to explicitly specify it within the path. So all the previous examples uses lax mode under the hood.

In contrast strict mode the JSON path passed is strictly checked against and in case it is an invalid path it returns an error.
To illustrate this lets use a modified version of the last query in the two modes and see the difference

lax mode

SELECT JSON_VALUE(@x,’lax $.Order.OrderDesc’) AS OrderDesc,
JSON_VALUE(@x,’lax $.Order.OrderDate’) AS OrderDate,
JSON_VALUE(@x,’lax $.Order.ShippedDate’) AS ShippedDate,
JSON_VALUE(@x,’lax $.Order.WebOrder’) AS WebOrder,
JSON_VALUE(@x,’lax $.Order.OrderItems.OrderQty’) AS OrderQty,
JSON_VALUE(@x,’lax $.Order.OrderItems.Item[0].ItemDesc’) AS ItemDesc1,
JSON_VALUE(@x,’lax $.Order.OrderItems.Item[0].Unitprice’) AS Unitprice1,
JSON_VALUE(@x,’lax $.Order.OrderItems.Item[0].SUbItem[0].SubItemDesc’) AS SubItemDesc11,
JSON_VALUE(@x,’lax $.Order.OrderItems.Item[0].SUbItem[1].SubItemDesc’) AS SubItemDesc12

 JSON_VALUE(@x,’lax $.Order.OrderItems.Item[1].ItemDesc’) AS ItemDesc2,
JSON_VALUE(@x,’lax $.Order.OrderItems.Item[1].Unitprice’) AS Unitprice2,
JSON_VALUE(@x,’lax $.Order.OrderItems.Item[1].SUbItem[0].SubItemDesc’) AS SubItemDesc21,
JSON_VALUE(@x,’lax $.Order.OrderItems.Item[1].SUbItem[1].SubItemDesc’) AS SubItemDesc22

and the result

Notice the NULL value for the nonexistent key coming at the end. This query works without any error because of lax mode
Now lets see what happens with the same query in the strict mode

SELECT JSON_VALUE(@x,’strict $.Order.OrderDesc’) AS OrderDesc,
JSON_VALUE(@x,’strict $.Order.OrderDate’) AS OrderDate,
JSON_VALUE(@x,’strict $.Order.ShippedDate’) AS ShippedDate,
JSON_VALUE(@x,’strict $.Order.WebOrder’) AS WebOrder,
JSON_VALUE(@x,’strict $.Order.OrderItems.OrderQty’) AS OrderQty,
JSON_VALUE(@x,’strict $.Order.OrderItems.Item[0].ItemDesc’) AS ItemDesc1,
JSON_VALUE(@x,’strict $.Order.OrderItems.Item[0].Unitprice’) AS Unitprice1,
JSON_VALUE(@x,’strict $.Order.OrderItems.Item[0].SUbItem[0].SubItemDesc’) AS SubItemDesc11,
JSON_VALUE(@x,’strict $.Order.OrderItems.Item[0].SUbItem[1].SubItemDesc’) AS SubItemDesc12,
JSON_VALUE(@x,’strict $.Order.OrderItems.Item[1].ItemDesc’) AS ItemDesc2,
JSON_VALUE(@x,’strict $.Order.OrderItems.Item[1].Unitprice’) AS Unitprice2,
JSON_VALUE(@x,’strict $.Order.OrderItems.Item[1].SUbItem[0].SubItemDesc’) AS SubItemDesc21,
JSON_VALUE(@x,’strict $.Order.OrderItems.Item[1].SUbItem[1].SubItemDesc’) AS SubItemDesc22

Now the result

As seen from the above example it throws an error when it finds an non existent key as per the specified path in the strict mode.

JSON_QUERY()

JSON_QUERY function works similar to JSON_VALUE with the exception that it accepts a JSON path and returns a JSON document as the result
See the below example to understand how JSON_QUERY works
declare @x nvarchar(max) = ‘{
  “Order”: 
    {
      “OrderDesc”: “Order 1”,
      “OrderDate”: “2013-02-12T00:00:00”,
      “ShippedDate”: “2013-02-20T00:00:00”,
      “WebOrder”: false,
      “OrderItems”:
        {
          “OrderQty”: 10,
          “Item”: 
            {
              “ItemDesc”: “Wireless Mouse”,
              “Unitprice”: 30,
              “SUbItem”: 
                {
                  “SubItemDesc”: “SP17”
                }
            }
        }
   }
}’

SELECT JSON_QUERY(@x,’$.Order.OrderItems’) AS OrderItems,
JSON_QUERY(@x,’$.Order.OrderItems.Item’) AS Item,
JSON_QUERY(@x,’$.Order.OrderItems.Item.SUbItem’) AS SubItem


The result will contain three JSON documents as shown below
JSON_QUERY function similar to JSON_VALUE works in two supported modes lax and strict with lax being the default. 
The functionality remains the same with strict mode throwing an error in case of passing an invalid path as compared to lax mode returning a NULL value
Here is an illustration on the same 
declare @x nvarchar(max) = ‘{
  “Order”: 
    {
      “OrderDesc”: “Order 1”,
      “OrderDate”: “2013-02-12T00:00:00”,
      “ShippedDate”: “2013-02-20T00:00:00”,
      “WebOrder”: false,
      “OrderItems”:
        {
          “OrderQty”: 10,
          “Item”: 
            {
              “ItemDesc”: “Wireless Mouse”,
              “Unitprice”: 30,
              “SUbItem”: 
                {
                  “SubItemDesc”: “SP17”
                }
            }
        }
   }
}’

SELECT JSON_QUERY(@x,’lax $.Order.OrderItems’) AS OrderItems,
JSON_QUERY(@x,’lax $.Order.OrderItems.Item’) AS Item,
JSON_QUERY(@x,’lax $.Order.OrderItems.Item.SUbItem’) AS SubItem,
JSON_QUERY(@x,’lax $.Order.Customer’) AS Customer

Result is as given below
Now strict mode will be as below

SELECT JSON_QUERY(@x,’strict $.Order.OrderItems’) AS OrderItems,
JSON_QUERY(@x,’strict $.Order.OrderItems.Item’) AS Item,
JSON_QUERY(@x,’strict $.Order.OrderItems.Item.SUbItem’) AS SubItem,
JSON_QUERY(@x,’strict $.Order.Customer’) AS Customer

with the result

Parsing JSON Array Objects And Nested Levels

Now lets look at how JSON array objects and JSON documents with nested levels can be parsed.
Both the functions JSON_QUERY and JSON_VALUE can only parse a single JSON element at a time. Hence when we have to have to use a construct which helps us to iterate through each object in the array before we apply JSON_VALUE or JSON_QUERY over it.
If you have read my earlier article on JSON support you would be able to make out that we can use OPENJSON construct to iterate through the JSON document array.
Once OPENJSON is applied it returns as a elements of JSON as a resultset over which we can apply the JSON_VALUE or the JSON_QUERY function to return us a single scalar value or a JSON element depending on the case.
Now lets see an illustration to understand this
Consider the example given below
declare @x nvarchar(max) = ‘{
  “Orders”: [
    {
      “OrderDesc”: “Order 1”,
      “OrderDate”: “2013-02-12T00:00:00”,
      “ShippedDate”: “2013-02-20T00:00:00”,
      “WebOrder”: false,
      “OrderItems”: [
        {
          “OrderQty”: 10,
          “Item”: [
            {
              “ItemDesc”: “Wireless Mouse”,
              “Unitprice”: 30,
              “SUbItem”: [
                {
                  “SubItemDesc”: “SP17”
                }
              ]
            }
          ]
        },
        {
          “OrderQty”: 8,
          “Item”: [
            {
              “ItemDesc”: “Copier Ink”,
              “Unitprice”: 20,
              “SUbItem”: [
                {
                  “SubItemDesc”: “SP5”
                },
                {
                  “SubItemDesc”: “SP12”
                }
              ]
            }
          ]
        }
      ],
      “Customer”: [
        {
          “CustomerDesc”: “KBC Associates”
        }
      ]
    },
    {
      “OrderDesc”: “Order 2”,
      “OrderDate”: “2015-06-24T00:00:00”,
      “ShippedDate”: “2015-06-30T00:00:00”,
      “WebOrder”: false,
      “OrderItems”: [
        {
          “OrderQty”: 12,
          “Item”: [
            {
              “ItemDesc”: “Printer Catridge”,
              “Unitprice”: 112,
              “SUbItem”: [
                {
                  “SubItemDesc”: “SP1”
                },
                {
                  “SubItemDesc”: “SP3”
                }
              ]
            }
          ]
        }
      ],
      “Customer”: [
        {
          “CustomerDesc”: “ABC inc”
        }
      ]
    },
    {
      “OrderDesc”: “Order 5”,
      “OrderDate”: “2016-05-17T00:00:00”,
      “ShippedDate”: “2016-05-22T00:00:00”,
      “WebOrder”: true,
      “OrderItems”: [
        {
          “OrderQty”: 28,
          “Item”: [
            {
              “ItemDesc”: “Monitor CRT 18″”,
              “Unitprice”: 200
            }
          ]
        }
      ],
      “Customer”: [
        {
          “CustomerDesc”: “AAM & Co”
        }
      ]
    }
  ]
}’

If you analyze the above example you can see that JSON document consist of array of JSON objects each with nested JSON array objects within them. Since it involved JSON array objects we would require OPENJSON function to parse them. Once we get individual JSON elements we can then apply JSON_VALUE or JSON_QUERY over them
So the query will look like this

SELECT JSON_VALUE(value,’$.OrderDesc’) AS OrderDesc,
TRY_CONVERT(datetime,JSON_VALUE(value,’$.OrderDate’)) AS OrderDate,
TRY_CONVERT(datetime,JSON_VALUE(value,’$.ShippedDate’)) AS ShippedDate,
JSON_VALUE(value,’$.WebOrder’) AS WebOrder,
JSON_VALUE(oi,’$.OrderQty’) AS OrderQty,
JSON_VALUE(i,’$.ItemDesc’) AS ItemDesc,
JSON_VALUE(i,’$.Unitprice’) AS UnitPrice,
JSON_VALUE(si,’$.SubItemDesc’) AS SubItemDesc,
JSON_VALUE(c,’$.CustomerDesc’) AS CustomerDesc
FROM OPENJSON(@x,’$.Orders’) t
CROSS APPLY (SELECT JSON_QUERY(t.value,’$.Customer[0]’)) m(c)
CROSS APPLY (SELECT value FROM OPENJSON(t.value,’$.OrderItems’))n(oi)
CROSS APPLY (SELECT value FROM OPENJSON(oi,’$.Item’))p(i)
OUTER APPLY (SELECT value FROM OPENJSON(i,’$.SUbItem’))q(si)

The resultset for the query is as given below
As seen from the above case, for iterating through JSON array we make use of OPENJSON. Within the array each  individual JSON document can be fetched using  JSON_QUERY function. In the above case since there is only single customer element we retrieve it using JSON_QUERY function and then apply series of JSON_VALUE function calls to return each key value within it. For the other documents which holds an array object we use further correlated OPENJSON calls by using APPLY operator and for each of the returned JSON documents we apply JSON_VALUE functions to return individual key element values.
One thing to note here is that path being passed is case sensitive so you need to make sure you pass path in the exact case as how it appears within the JSON document
If you want a filter to be applied on above query you can make use of JSON_VALUE to make sure you retrieve only the documents where scalar value returned matches a given condition
Like for example
SELECT JSON_VALUE(value,’$.OrderDesc’) AS OrderDesc,
TRY_CONVERT(datetime,JSON_VALUE(value,’$.OrderDate’)) AS OrderDate,
TRY_CONVERT(datetime,JSON_VALUE(value,’$.ShippedDate’)) AS ShippedDate,
JSON_VALUE(value,’$.WebOrder’) AS WebOrder,
JSON_VALUE(oi,’$.OrderQty’) AS OrderQty,
JSON_VALUE(i,’$.ItemDesc’) AS ItemDesc,
JSON_VALUE(i,’$.Unitprice’) AS UnitPrice,
JSON_VALUE(si,’$.SubItemDesc’) AS SubItemDesc,
JSON_VALUE(c,’$.CustomerDesc’) AS CustomerDesc
FROM OPENJSON(@x,’$.Orders’) t
CROSS APPLY (SELECT JSON_QUERY(t.value,’$.Customer[0]’)) m(c)
CROSS APPLY (SELECT value FROM OPENJSON(t.value,’$.OrderItems’))n(oi)
CROSS APPLY (SELECT value FROM OPENJSON(oi,’$.Item’))p(i)
OUTER APPLY (SELECT value FROM OPENJSON(i,’$.SUbItem’))q(si)
WHERE JSON_VALUE(oi,’$.OrderQty’) > 20


This will only retrieve the data for the case where specified key value condition is satisfies within the JSON element and we will get the below output

JSON_MODIFY()

JSON_MODIFY function as the name implies allows us to modify the data within an existing JSON document. This function can be used in cases where we need to replace an existing value or set a new value to a key within JSON document. We can also use it to rename a given JSON document key.
The syntax of JSON_MODIFY us as below
JSON_MODIFY(JSONexpression, JSON Path, value)
Lets now see JSON_MODIFY function in action 
Consider this simple example
declare @x nvarchar(max) = ‘{
     “Order”: {
      “OrderDesc”: “Order 1”,
      “OrderDate”: “2013-02-12T00:00:00”,
      “ShippedDate”: “2013-02-20T00:00:00”,
      “WebOrder”: false,
      “OrderItems”: 
        {
          “OrderQty”: 10,
          “Item”: {
              “ItemDesc”: “Wireless Mouse”,
              “Unitprice”: “30”,
              “SUbItem”: {
                  “SubItemDesc”: “SP17”
                }
            }
         }
     }
}’
Now lets try to update value of Unitprice key in Item to 50 using JSON_MODIFY
The code will look like this
SELECT JSON_VALUE(@x,’$.Order.OrderItems.Item.Unitprice’) AS Unitprice

   SET @x = JSON_MODIFY(@x,’$.Order.OrderItems.Item.Unitprice’,50)

   SELECT @x,JSON_VALUE(@x,’$.Order.OrderItems.Item.Unitprice’) AS Unitprice
The corresponding result will be as below
As seen from the result the key value got updated to 50
Now lets see someother applications of JSON_MODIFY. 
As specified earlier JSON_MODIFY can also be used to create a new key within JSON element, append a value to existing object array, rename a key within a JSON element or delete a key within JSON_MODIFY.
Lets see some illustrations below

1. Create a new key to JSON element

SET @x = JSON_MODIFY(@x,’$.Order.OrderItems.Item.ItemQty’,50)

SELECT @x

The result is as below


{  
   “Order”:{  
      “OrderDesc”:“Order 1”,
      “OrderDate”:“2013-02-12T00:00:00”,
      “ShippedDate”:“2013-02-20T00:00:00”,
      “WebOrder”:false,
      “OrderItems”:{  
         “OrderQty”:10,
         “Item”:{  
            “ItemDesc”:“Wireless Mouse”,
            “Unitprice”:“30”,
            “SUbItem”:{  
               “SubItemDesc”:“SP17”
            },
            “ItemQty”:50
         }
      }
   }
}

2. Append a value to JSON element

declare @x nvarchar(max) = ‘{
     “Order”: {
      “OrderDesc”: “Order 1”,
      “OrderDate”: “2013-02-12T00:00:00”,
      “ShippedDate”: “2013-02-20T00:00:00”,
      “WebOrder”: false,
      “OrderItems”: 
        {
          “OrderQty”: 10,
          “Item”: {
              “ItemDesc”: [“Wireless Mouse”],
              “Unitprice”: “30”,
              “SUbItem”: {
                  “SubItemDesc”: “SP17”
                }
            }
         }
}
}’


SET @x = JSON_MODIFY(@x,’append $.Order.OrderItems.Item.ItemDesc’,’Optical USB Enabled’)

SELECT @x

The resulting JSON document is as below
{  
   “Order”:{  
      “OrderDesc”:“Order 1”,
      “OrderDate”:“2013-02-12T00:00:00”,
      “ShippedDate”:“2013-02-20T00:00:00”,
      “WebOrder”:false,
      “OrderItems”:{  
         “OrderQty”:10,
         “Item”:{  
            “ItemDesc”:[  
               “Wireless Mouse”,
               “Optical USB Enabled”
            ],
            “Unitprice”:“30”,
            “SUbItem”:{  
               “SubItemDesc”:“SP17”
            }
         }
      }
   }
}
As seen above it appends the value passed to the ItemDesc array object.

3. Rename a key within JSON document

Now lets see an example of the case where we need to rename a key within a JSON array
SET @x = JSON_MODIFY(JSON_MODIFY(@x,’$.Order.OrderItems.Item.SubItem’,JSON_QUERY(@x,’$.Order.OrderItems.Item.SUbItem’)),’$.Order.OrderItems.Item.SUbItem’,NULL)

SELECT @x

The result as below
{  
   “Order”:{  
      “OrderDesc”:“Order 1”,
      “OrderDate”:“2013-02-12T00:00:00”,
      “ShippedDate”:“2013-02-20T00:00:00”,
      “WebOrder”:false,
      “OrderItems”:{  
         “OrderQty”:10,
         “Item”:{  
            “ItemDesc”:[  
               “Wireless Mouse”
            ],
            “Unitprice”:“30”,
            “SubItem”:{  
               “SubItemDesc”:“SP17”
            }
         }
      }
   }

Analyse the result and you can see that the JSON document key element has been renamed from SUbitem to SubItem.

4. Delete a key within JSON document

declare @x nvarchar(max) = ‘{  
   “Order”:{  
      “OrderDesc”:”Order 1″,
      “OrderDate”:”2013-02-12T00:00:00″,
      “ShippedDate”:”2013-02-20T00:00:00″,
      “WebOrder”:false,
      “OrderItems”:{  
         “OrderQty”:10,
         “Item”:{  
            “ItemDesc”:[  
               “Wireless Mouse”
            ],
            “Unitprice”:”30″,
            “ItemQty”:50,
            “SubItem”:{  
               “SubItemDesc”:”SP17″
            }
         }
      }
   }
}’

The code is as given below

SET @x = JSON_MODIFY(@x,’$.Order.OrderItems.Item.ItemQty’,NULL)

SELECT @x

The result will look like this

{  
   “Order”:{  
      “OrderDesc”:“Order 1”,
      “OrderDate”:“2013-02-12T00:00:00”,
      “ShippedDate”:“2013-02-20T00:00:00”,
      “WebOrder”:false,
      “OrderItems”:{  
         “OrderQty”:10,
         “Item”:{  
            “ItemDesc”:[  
               “Wireless Mouse”
            ],
            “Unitprice”:“30”,
            “SubItem”:{  
               “SubItemDesc”:“SP17”
            }
         }
      }
   }
}

If you notice the result you will see that the ItemQty key is missing in the document which indicates that key has been deleted from the document.

Please note that all the above queries are using the default lax mode. While using strict mode it will throw the standard error indicating JSON path not found if you pass a non existing key name.

Conclusion

The examples given above illustrate the options available in SQL 2016 using standard JSON functions to do the data manipulations within the JSON data.
One or more of these functions can be effectively used in cases where data within the JSON needs to be modified to get the desired result.

Published by dataplatformcentral