Using JSON_MODIFY to append new JSON strings

    JSON_MODIFY is available in Sql Server 2016 and Azure SQL Database. It updates a value of any property in a JSON object and returns the updated value. Let's say I have the following JSON string in my table.

declare @jsonVal nvarchar(max) = '
{
"Products":[
  {"Product":{
      "ProductId":1,"Name":"60W Light Bulb","Price":"10.00",
      "Alike": { "AlikeProducts" : ["2"]}}},
    {"Product":{
      "ProductId":2,"Name":"100W Light Bulb","Price":"7.00"}},
    {"Product":{
     "ProductId":3,"Name":"HDMI Cable","Price":"7.00"}},
    {"Product":{
     "ProductId":4,"Name":"USB Cable","Price":"2.50"}},
    {"Product":{
      "ProductId":5,"Name":"Power Cable","Price":"5.00"}},
    {"Product":{
      "ProductId":6,"Name":"Monitor","Price":"160.00"}},
    {"Product":{
      "ProductId":7,"Name":"Keyboard","Price":"27.00"}}
]
}
'

    As you can see, I have Products array, and it contains bunch of product objects in it. To demo the JSON_MODIFY, I am going to add a new product to my Products array.Let's look at the JSON_MODIFY syntax first.

JSON_MODIFY (expressions, path, newvalue)

• expression is the JSON column name in a table or the name of the variable.
• path can have the following options
• append : this option specifies that the new value should append to the original value.
• lax : It specifies that the path ($.json_path) does not have to exist. JSON_MODIFY will try to insert the value to the specified path. lax and strict is optional and if you don't specif any of them, SQL Server will take the lax as default.
• strict : Itn specifies that the path must be exist in the json string to continue.
• $.json_path: It specifies the path of a JSON property to modify.

    Now, i am going to add a new Product to my Json string by using JSON_MODIFY. Let's see how tricky that can get.

DECLARE @product nvarchar(1000)
set @product = N'{"Product":{"ProductId":8,"Name":"LED 60W Light Buld","Price":"16.00"}}'
SELECT JSON_MODIFY(@jsonval, 'append $.Products', @product)

    Alright, this looks good right? I declared my new json string and I am using append option to add this to my source json object.When I run this query, JSON_MODIFY is going to return the updated JSON string. Let's see the result.

{
  "Products":[
    {"Product":{"ProductId":1,"Name":"60W Light Bulb","Price":"10.00", 
     "Alike": { "AlikeProducts" : ["2"]}}},
    {"Product":{"ProductId":2,"Name":"100W Light Bulb","Price":"7.00"}},      
    {"Product":{"ProductId":3,"Name":"HDMI Cable","Price":"7.00"}},
    {"Product":{"ProductId":4,"Name":"USB Cable","Price":"2.50"}},  
    {"Product":{"ProductId":5,"Name":"Power Cable","Price":"5.00"}},  
    {"Product":{"ProductId":6,"Name":"Monitor","Price":"160.00"}},  
    {"Product":{"ProductId":7,"Name":"Keyboard","Price":"27.00"}}  ,
"{\"Product\":{\"ProductId\":8,\"Name\":\"LED 60W Light Buld\",\"Price\":\"16.00\"}}"]  } 

    Okay, This doesn't look right at all. My new Product is there but it is not in json format. It looks like it's added as string. This is not going to work, JSON_MODIFY did append the json string but not as a JSON object. To fix this problem we need to use JSON_QUERY function.

JSON_QUERY (expressions, path)

   JSON_QUERY extracts an object or an array from a JSON string.I am not going to use path in this example since I want to select the whole new Product object.

DECLARE @product nvarchar(1000)
set @product = N'{"Product":{"ProductId":8,"Name":"LED 60W Light Buld","Price":"16.00"}}'
SELECT JSON_MODIFY(@jsonval, 'append $.Products', JSON_QUERY(@product))

  {  "Products":[    
     {"Product":{"ProductId":1,"Name":"60W Light Bulb","Price":"10.00",        
                 "Alike": { "AlikeProducts" : ["2"]}}},      
     {"Product":{"ProductId":2,"Name":"100W Light Bulb","Price":"7.00"}},      
     {"Product":{"ProductId":3,"Name":"HDMI Cable","Price":"7.00"}},      
     {"Product":{"ProductId":4,"Name":"USB Cable","Price":"2.50"}},     
     {"Product":{"ProductId":5,"Name":"Power Cable","Price":"5.00"}},      
     {"Product":{"ProductId":6,"Name":"Monitor","Price":"160.00"}},      
     {"Product":{"ProductId":7,"Name":"Keyboard","Price":"27.00"}}  ,
     {"Product":{"ProductId":8,"Name":"LED 60W Light Buld","Price":"16.00"}}]
  }  

As you can see, we have to use JSON_QUERY to parse json string before we append or add it.