The JSON_MODIFY is a JSON functions that allows you to update the value of a property in a JSON string. It introduced in SQL Server 2016.
JSON_MODIFY function returns the updated JSON string.
Using JSON_MODIFY function you can update an existing property value, delete a property value from JSON string, delete a property, and add a new Key and it’s value to the JSON string.
It returns an error when string is not in valid JSON format.
SYNTAX
JSON_MODIFY (expression, path, new_value)
expression: is the column or a variable that contains the JSON data string that you want to update.
Path:Â is a JSON path that specify the property that requires an update in the JSON string.
Path requires the following arguments:
[append] [ lax | strict ] $.<json path>
-
- Specifies that the property referenced by <json path> does not have to exist. If the property is not present, JSON_MODIFY tries to insert the new value on the specified path. Insertion may fail if the property can’t be inserted on the path. If you don’t specify lax or strict, lax is the default mode.
- append
It is an optional modifier, that specifies that the new value should be appended to the array referenced by <json path>. - lax
It specifies that the property referenced by <json path> does not have to exist. If the property is not present, JSON_MODIFY tries to insert the new value on the specified path. Insertion may fail if the property can’t be inserted on the path. Lax is the default mode, If you don’t specify lax or strict. - strict
It specifies that the property referenced by <json path>Â must be in the JSON expression. If the property is not present, JSON_MODIFY returns an error. - <json path>
It specifies the path for the property that you want to update.
new_value: is the new value that you require to update in the JSON.
Lets look at an example of using JSON_MODIFY function in SQL Server.
Here we have a simple JSON string as shown Below.
{"Item":"Iphone 5S", "Price":25000}
Updating the property value using JSON_MODIFY function
Following T-SQL uses JSON_MODIFY function that update the Item property value from ‘Iphone 5S’ to ‘Iphone 7’.
DECLARE @string_Json VARCHAR(150)
= '{"Item":"Iphone 5S", "Price":25000}'
SELECT
  JSON_MODIFY(@string_Json,
             '$.Item', 'IPhone 7') AS [Updated Property Value];
As you can see, It returns the updated JSON string there you can see Item property value is updated from ‘Iphone 5S’ to ‘Iphone 7’.
Inserting a new property and value in JSON string using JSON_MODIFY function
Following T-SQL uses the JSON_MODIFY function which adds a new property named ‘Color’ and it’s value ‘Black’ to the JSON string.
DECLARE @string_Json VARCHAR(150)
= '{"Item":"Iphone 5S","Price":25000}'
DECLARE @path VARCHAR(100) = '$.Color'
DECLARE @newvalue VARCHAR(50) = 'Black'
SELECT
JSON_MODIFY(@string_Json,
@Path, @newvalue) AS [Updated Property Value];
Using JSON_MODIFY function appending a value to the JSON Array
You can append a new value to the JSON array, following T-SQL uses a JSON_MODIFY functions and appends a new value ‘Silver’ to the JSON array.
DECLARE @string_Json VARCHAR(150)
     = '{"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]} '
DECLARE @path VARCHAR(100) = 'append$.Color'
DECLARE @newvalue VARCHAR(50) = 'Silver'
SELECT
  JSON_MODIFY(@string_Json,
        @Path, @newvalue) AS [Updated Property Value];
As you can see, it returns an updated JSON string appending a new value ‘Silver’ to the JSON string.
Using a strict path mode with JSON_MODIFY function
If you specified the strict path mode in JSON then it does not allow you to inserting a new property in JSON string as it specifies that the property referenced by Path must be in the JSON string, if it does not exist it returns an error.
Obviously, the property that you are going to insert, does not exists in JSON String.
Lets try to execute above T-SQL with strict path mode while adding a new property in JSON String.
DECLARE @string_Json VARCHAR(150)
= '{"Item":"Iphone 5S","Price":25000}'
DECLARE @path VARCHAR(100) = 'strict$.Color'
DECLARE @newvalue VARCHAR(50) = 'Black'
SELECT
JSON_MODIFY(@string_Json,
@Path, @newvalue) AS [Updated Property Value];
As you can see, it returns an error indicating that ‘Property cannot be found on the specified JSON path.’
So, you can not add new property when strict path mode is specified in JSON_MODIFY function. If strict path is specified then you can only modify an existing property in JSON string.
Using a lax path mode with JSON_MODIFY function
lax is the default path mode, It specifies that the property referenced by path does not have to exist. If the property is not present that why using JSON_MODIFY you can insert the new value on the specified path.
Lets use a lax path mode in above T-SQL, and you will see it adds a property Color and it’s value in JSON string.
DECLARE @string_Json VARCHAR(150)
      = '{"Item":"Iphone 5S","Price":25000}'
DECLARE @path VARCHAR(100) = 'lax$.Color'
DECLARE @newvalue VARCHAR(50) = 'Black'
SELECT
  JSON_MODIFY(@string_Json,
             @Path, @newvalue) AS [Updated Property Value];
Deleting value from Property using JSON_MODIFY function
Lets delete the value from JSON array, to delete the value from JSON array you can do it either replacing that value with null or update the JSON array with new set of values.
Suppose you want to delete the value ‘Grey’ from JSON array, JSON string as given below.
'{"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]}'
To delete the value ‘Grey’ from above JSON array you need to specify the position of element in JSON array, the path of value in JSON array is $Color.[1].
DECLARE @string_Json VARCHAR(150)
      = '{"Item":"Iphone 5S",
        "Price":25000,
         "Color":["Black", "Grey", "Gold"]}'
DECLARE @path VARCHAR(100) = '$.Color[1]'
DECLARE @newvalue VARCHAR(50) = null
SELECT
      JSON_MODIFY(@string_Json,
                 @Path, @newvalue) AS [Updated Property Value];
As you can see, it replaces the value with null.
Lets see the another way to deleting the value from JSON array, in this way we will update the JSON array values with new set of values as shown below.
Removed the ‘Grey’ from new values, only kept ‘Black’ and ‘Gold’ in JSON array.
As you can see, returns the updated JSON string but adds the backslash for each element in the array, it happens as JSON_MODIFY function is considering the JSON object as a normal text instead of valid JSON.
It can be removed using JSON_QUERY() function, Lets use the JSON_QUERY() function to insert a set of values using JSON_MODIFY() function.
DECLARE @string_Json VARCHAR(150)
      = '{"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]}'
DECLARE @path VARCHAR(100) = '$.Color'
DECLARE @newvalue VARCHAR(50) = '["Black", "Gold"]'
SELECT
  JSON_MODIFY(@string_Json,
         @Path, JSON_QUERY(@newvalue)) AS [Updated Property Value];
You can see, now value Grey is deleted from JSON array also there is no backslash.
Deleting a Property from JSON string
To deleting a property from JSON string, just pass a third argument value as null as shown below.
DECLARE @string_Json VARCHAR(150)
      = '{"Item":"Iphone 5S",
         "Price":25000,
         "Color":["Black", "Grey", "Gold"]}'
DECLARE @path VARCHAR(100) = '$.Color'
DECLARE @newvalue VARCHAR(50) = null
SELECT
   JSON_MODIFY(@string_Json,
           @Path, JSON_QUERY(@newvalue)) AS [Updated Property Value];
As you can see, Property color is deleted from JSON string.
Also Read..
