JSON_MODIFY is one of the new JSON function introduced in Sql Server 2016. This function can be used to update the value of the property in a JSON string and returns the updated JSON string.
SYNTAX:
JSON_MODIFY (json_string, json_path , new_value)
WHERE
json_string : Is the JSON String which needs to be updated
json_path : Is the location of the property in the json_string, whose value needs to be updated
new_value : The new value for the property in the json_string
Let us understand this function with extensive list of examples.
EXAMPLE 1: Updating JSON Property Value
In this example the JSON_MODIFY function is used to update the FirstName property value from Basavaraj to Basav
SELECT
JSON_MODIFY('{"FirstName":"Basavaraj","Last Name":"Biradar"}',
'$.FirstName','Basav') AS 'Updated JSON'
RESULT:
[ALSO READ] ISJSON Function in Sql Server 2016
EXAMPLE 2: Updating JSON property value where JSON_MODIFY function json_string and new_value are variables
This example is same as Example 1, the only difference here is instead of passing JSON string as a constant assigning it to the variable @json_string and passing this variable to the function. And the new value is set to the variable @new_value and passed it to the function.
DECLARE @json_string VARCHAR(100)
= '{"FirstName":"Basavaraj","Last Name":"Biradar"}',
@new_value VARCHAR(50) = 'Basav'
SELECT JSON_MODIFY(@json_string,'$.FirstName',
@new_value) AS 'Updated JSON'
RESULT:
In the above example we can see that only the json_path is a string literal. Let us assign this also to a variable and pass it to the JSON_MODIFY function.
DECLARE @json_string VARCHAR(100)
= '{"FirstName":"Basavaraj","Last Name":"Biradar"}',
@json_path VARCHAR(50) = '$.FirstName',
@new_value VARCHAR(50) = 'Basav'
SELECT JSON_MODIFY(@json_string, @json_path,
@new_value) AS 'Updated JSON'
RESULT:
Msg 13610, Level 16, State 2, Line 5
The argument 2 of the “JSON_MODIFY” must be a string literal.
From the result we can see that the JSON PATH expression should always be a string literal
[ALSO READ] JSON_VALUE Function in Sql Server 2016
EXAMPLE 3: Adding Id property and it’s value to the JSON string
The below script adds the Id property to the JSON string
SELECT JSON_MODIFY(
'{"FirstName":"Basavaraj","Last Name":"Biradar"}'
, '$.Id', 1) AS 'Updated JSON'
RESULT:
[ALSO READ] lax and strict JSON Path modes in Sql Server 2016
EXAMPLE 4: Impact of JSON Path mode on adding a new property to the existing JSON string
Execute the following statement which is same as the example 3 script, the only difference is in the json_path the JSON path mode strict is specified.
SELECT JSON_MODIFY(
'{"FirstName":"Basavaraj","Last Name":"Biradar"}'
, '$.Id', 1) AS 'Updated JSON'
RESULT:
Msg 13608, Level 16, State 2, Line 1
Property cannot be found on the specified JSON path.
From the result we can see that the strict path mode will not allow adding of a new property to the JSON string
Execute the following statement which is same as the above example script, the only difference is in the JSON path mode. Here in this statement the JSON path mode is lax and it is the default path mode when it is not specified.
SELECT JSON_MODIFY(
'{"FirstName":"Basavaraj","Last Name":"Biradar"}'
,'lax$.Id', 1 ) AS 'Updated JSON'
RESULT:
From the result we can see that lax JSON Path mode allows adding a new JSON property. The JSON Path mode lax is the default path mode and if path mode is not specified it will consider it as lax. We can see that in example 3 when path mode is not specified we are able to add the new property.
[ALSO READ] JSON_QUERY Function in Sql Server 2016
EXAMPLE 5: This example shows how setting a NULL value for the JSON property will remove that property from the JSON string
SELECT JSON_MODIFY(
'{"Id":1,"FirstName":"Basavaraj","Last Name":"Biradar"}'
,'$.Id', NULL) AS 'Updated JSON'
RESULT:
[ALSO READ] FOR JSON Clause in Sql Server 2016
Example 6: This example explains how we can set a NULL value for a JSON property
In the example 5 we saw that setting a NULL value for a JSON property resulted in that property getting deleted from the JSON string. But if we have requirement where we need to set the NULL value for a JSON property value then in the JSON path we have to specify strict JSON path mode as in the below script:
SELECT JSON_MODIFY(
'{"Id":1,"FirstName":"Basavaraj","Last Name":"Biradar"}'
,'strict$.Id', NULL) AS 'Updated JSON'
RESULT:
[ALSO READ] OPENJSON Function in Sql Server 2016
EXAMPLE 7: This example shows how we can append a value to the JSON Array.
Execute the below statement to add the hobby Tennis to the Hobbies array:
SELECT JSON_MODIFY(
'{"Id":1,"Name":"Basavaraj",
"Hobbies":["Blogging","Cricket"]}',
'append $.Hobbies','Tennis') AS 'Updated JSON'
RESULT:
[ALSO READ] STRING_SPLIT function in Sql Server 2016
EXAMPLE 8: This example shows how we can add a JSON object to the JSON string.
Execute the following statement to add Address which is of type JSON object to the JSON string:
SELECT JSON_MODIFY(
'{"Id":1,"Name":"Basavaraj"}'
,'$.Address'
,'{"State":"KA","Country":"India"}') AS 'Updated JSON'
RESULT:
From the result we can see that JSON_MODIFY function is escaping the double quote in the JSON object which is getting added. The reason for this is JSON_MODIFY function is treating the JSON object as a normal text instead of valid JSON.
But if you want to avoid the escaping the JSON object which is getting added, use the JSON_QUERY function as shown below:
SELECT JSON_MODIFY(
'{"Id":1,"Name":"Basavaraj"}'
,'$.Address'
,JSON_QUERY('{"State":"KA","Country":"India"}'))
AS 'Updated JSON'
RESULT:
[ALSO READ] DATEDIFF_BIG Function in Sql Server 2016
EXAMPLE 9: This example shows how we can modify the property value of JSON string stored in the Table Column.
Create a Customer table as shown in the below image with sample data by the following script
Script:
CREATE DATABASE SqlHintsJSONModify
GO
USE SqlHintsJSONModify
GO
CREATE TABLE dbo.Customer(
Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Name NVARCHAR(50), Detail NVARCHAR(MAX))
GO
INSERT INTO dbo.Customer ( Name, Detail )
VALUES
('Basavaraj','{"Address":{"State":"KA","Country":"India"}}'),
('Kalpana','{"Address":{"State":"MH","Country":"India"}}')
Execute the following statement and observe that the customer Kalpana’s state property value in the Detail column is MH.
SELECT * FROM dbo.Customer WHERE Name = 'Kalpana'
RESULT:
Execute the following statement to update the Customer Kalpana’s State to KA from MH.
UPDATE Customer SET Detail = JSON_MODIFY(Detail , '$.Address.State','KA') WHERE Name = 'Kalpana'
Now execute the following statement to check whether the customer Kalpana’s state value is updated to KA from MH.
SELECT * FROM dbo.Customer WHERE Name = 'Kalpana'
RESULT:
[ALSO READ]
2 thoughts on “JSON_MODIFY Function in Sql Server 2016”