{"id":4808,"date":"2016-03-18T22:14:28","date_gmt":"2016-03-18T16:44:28","guid":{"rendered":"http:\/\/sqlhints.com\/?p=4808"},"modified":"2016-03-18T22:14:28","modified_gmt":"2016-03-18T16:44:28","slug":"json_modify-function-in-sql-server-2016","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2016\/03\/18\/json_modify-function-in-sql-server-2016\/","title":{"rendered":"JSON_MODIFY Function in Sql Server 2016"},"content":{"rendered":"

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.<\/p>\n

SYNTAX:<\/strong><\/p>\n

\r\nJSON_MODIFY (json_string, json_path , new_value)\r\n<\/pre>\n
\nWHERE<\/strong>
\njson_string :<\/strong> Is the JSON String which needs to be updated
\njson_path :<\/strong>  Is the location of the property in the json_string, whose value needs to be updated
\nnew_value :<\/strong> The new value for the property in the json_string\n<\/p>\n
Let us understand this function with extensive list of examples.<\/p>\n
EXAMPLE 1:<\/strong> Updating JSON Property Value<\/p>\n
In this example the JSON_MODIFY function is used to update the FirstName property value from Basavaraj to Basav<\/p>\n
\r\nSELECT\r\n JSON_MODIFY('{"FirstName":"Basavaraj","Last Name":"Biradar"}',\r\n                   '$.FirstName','Basav') AS 'Updated JSON'\r\n<\/pre>\n
RESULT:<\/strong>
\n\"JSON_MODIFY<\/a><\/p>\n
[ALSO READ] ISJSON Function in Sql Server 2016<\/a><\/strong><\/p>\n
EXAMPLE 2:<\/strong> Updating JSON property value where JSON_MODIFY function json_string and new_value are variables<\/p>\n
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.<\/p>\n
\r\nDECLARE @json_string VARCHAR(100) \r\n\t= '{"FirstName":"Basavaraj","Last Name":"Biradar"}',\r\n\t@new_value VARCHAR(50) = 'Basav'\r\nSELECT JSON_MODIFY(@json_string,'$.FirstName',\r\n                     @new_value) AS 'Updated JSON'\r\n<\/pre>\n
RESULT:<\/strong>
\n\"JSON_MODIFY<\/a><\/p>\n
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.<\/p>\n
\r\nDECLARE @json_string VARCHAR(100) \r\n\t\t= '{"FirstName":"Basavaraj","Last Name":"Biradar"}',\r\n\t@json_path VARCHAR(50) = '$.FirstName',\r\n@new_value VARCHAR(50) = 'Basav'\r\nSELECT JSON_MODIFY(@json_string, @json_path, \r\n                     @new_value) AS 'Updated JSON'\r\n<\/pre>\n
RESULT:<\/strong><\/p>\n
Msg 13610, Level 16, State 2, Line 5
\nThe argument 2 of the “JSON_MODIFY” must be a string literal.<\/p>\n
From the result we can see that the JSON PATH expression should always be a string literal<\/p>\n
[ALSO READ] JSON_VALUE Function in Sql Server 2016<\/a><\/strong><\/p>\n
EXAMPLE 3:<\/strong> Adding Id property and it\u2019s value to the JSON string<\/p>\n
The below script adds the Id property to the JSON string<\/p>\n
\r\nSELECT JSON_MODIFY(\r\n   '{"FirstName":"Basavaraj","Last Name":"Biradar"}'\r\n   , '$.Id', 1) AS 'Updated JSON'\r\n<\/pre>\n
RESULT:<\/strong>
\n\"JSON_MODIFY<\/a><\/p>\n
[ALSO READ] lax and strict JSON Path modes in Sql Server 2016<\/a><\/strong><\/p>\n
EXAMPLE 4:<\/strong> Impact of JSON Path mode on adding a new property to the existing JSON string<\/p>\n
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.<\/p>\n
\r\nSELECT JSON_MODIFY(\r\n      '{"FirstName":"Basavaraj","Last Name":"Biradar"}'\r\n      , '$.Id', 1) AS 'Updated JSON'\r\n<\/pre>\n
RESULT:<\/strong><\/p>\n
Msg 13608, Level 16, State 2, Line 1
\nProperty cannot be found on the specified JSON path.<\/p>\n
From the result we can see that the strict path mode will not allow adding of a new property to the JSON string<\/p>\n
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.<\/p>\n
\r\nSELECT JSON_MODIFY(\r\n        '{"FirstName":"Basavaraj","Last Name":"Biradar"}'\r\n\t\t,'lax$.Id', 1 )\t AS 'Updated JSON'\r\n<\/pre>\n
RESULT:<\/strong>
\n\"JSON_MODIFY<\/a><\/p>\n
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.<\/p>\n
[ALSO READ] JSON_QUERY Function in Sql Server 2016<\/a><\/strong><\/p>\n
EXAMPLE 5:<\/strong> This example shows how setting a NULL value for the JSON property will remove that property from the JSON string<\/p>\n
\r\nSELECT JSON_MODIFY(\r\n        '{"Id":1,"FirstName":"Basavaraj","Last Name":"Biradar"}'\r\n\t\t,'$.Id', NULL)\t AS 'Updated JSON'\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Removing<\/a><\/p>\n
[ALSO READ] FOR JSON Clause in Sql Server 2016<\/a><\/strong><\/p>\n
Example 6:<\/strong> This example explains how we can set a NULL value for a JSON property<\/p>\n
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:<\/p>\n
\r\nSELECT JSON_MODIFY(\r\n        '{"Id":1,"FirstName":"Basavaraj","Last Name":"Biradar"}'\r\n\t\t,'strict$.Id', NULL)\t AS 'Updated JSON'\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Setting<\/a><\/p>\n
[ALSO READ] OPENJSON Function in Sql Server 2016<\/a><\/strong><\/p>\n
EXAMPLE 7:<\/strong> This example shows how we can append a value to the JSON Array.<\/p>\n
Execute the below statement to add the hobby Tennis to the Hobbies array:<\/p>\n
\r\nSELECT JSON_MODIFY(\r\n            '{"Id":1,"Name":"Basavaraj",\r\n            "Hobbies":["Blogging","Cricket"]}',\r\n            'append $.Hobbies','Tennis') AS 'Updated JSON'\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Adding<\/a><\/p>\n
[ALSO READ] STRING_SPLIT function in Sql Server 2016<\/a><\/strong><\/p>\n
EXAMPLE 8:<\/strong> This example shows how we can add a JSON object to the JSON string.<\/p>\n
Execute the following statement to add Address which is of type JSON object to the JSON string:<\/p>\n
\r\nSELECT JSON_MODIFY(\r\n\t'{"Id":1,"Name":"Basavaraj"}'\r\n\t,'$.Address'\r\n\t,'{"State":"KA","Country":"India"}') AS 'Updated JSON'\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Adding<\/a><\/p>\n
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.<\/p>\n
But if you want to avoid the escaping the JSON object which is getting added, use the JSON_QUERY function as shown below:<\/p>\n
\r\nSELECT JSON_MODIFY(\r\n    '{"Id":1,"Name":"Basavaraj"}'\r\n    ,'$.Address'\r\n    ,JSON_QUERY('{"State":"KA","Country":"India"}')) \r\n           AS 'Updated JSON'\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Adding<\/a><\/p>\n
[ALSO READ] DATEDIFF_BIG Function in Sql Server 2016<\/a><\/strong><\/p>\n
EXAMPLE 9:<\/strong> This example shows how we can modify the property value of JSON string stored in the Table Column.<\/p>\n
Create a Customer table as shown in the below image with sample data by the following script<\/p>\n
\"Customer<\/a>
\nScript:<\/strong><\/p>\n
\r\nCREATE DATABASE SqlHintsJSONModify\r\nGO\r\nUSE SqlHintsJSONModify\r\nGO\r\nCREATE TABLE dbo.Customer( \r\n    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,\r\n    Name NVARCHAR(50), Detail NVARCHAR(MAX))\r\nGO\r\nINSERT INTO dbo.Customer ( Name, Detail )\r\nVALUES \r\n  ('Basavaraj','{"Address":{"State":"KA","Country":"India"}}'),\r\n  ('Kalpana','{"Address":{"State":"MH","Country":"India"}}')\r\n<\/pre>\n
Execute the following statement and observe that the customer Kalpana\u2019s state property value in the Detail column is MH.<\/p>\n
\r\nSELECT * FROM dbo.Customer WHERE Name = 'Kalpana'\r\n<\/pre>\n
RESULT:<\/strong>
\n\"DataBeforeUpdate\"<\/a><\/p>\n
Execute the following statement to update the Customer Kalpana\u2019s State to KA from MH.<\/p>\n
\r\nUPDATE Customer\r\nSET Detail = JSON_MODIFY(Detail , '$.Address.State','KA')\r\nWHERE Name = 'Kalpana'\r\n<\/pre>\n
Now execute the following statement to check whether the customer Kalpana\u2019s state value is updated to KA from MH.<\/p>\n
\r\nSELECT * FROM dbo.Customer WHERE Name = 'Kalpana'\r\n<\/pre>\n
RESULT:<\/strong>
\n\"DataAfterUpdate\"<\/a><\/p>\n
[ALSO READ]<\/strong><\/p>\n\n\n\n\n
New features in SQL SERVER 2016<\/strong><\/td>\n<\/tr>\n
\n