SQL - JSON_MODIFY() Function

You can change JSON data kept in a column of a SQL Server table using the SQL JSON_MODIFY() function. This function, which was added to the JSON functions family to facilitate the storing, processing, and querying of JSON data in SQL Server, was first made available in SQL Server 2016.

JSON_MODIFY() function can be used to update the JSON string. It can be used to update the following items:

  • To update the existing property value

  • To add a new element in an existing array

  • To delete a property from JSON string

  • To delete a property

Syntax

Following is the syntax of the SQL JSON_MODIFY() function −

JSON_MODIFY ( expression , path , newValue )

Parameters

  • expression − It is typically the name of tzhe column or a variable that contains JSON text.

  • path − The JSON path expression tha specifies the property to update.path has the following syntax −

[append] [lax | strict] $.<json path>

append − It was an optional modifier that specify the new value should be append to the array specified with <json path>.

lax − It specifies the property referenced by <json path> does not have to exist.if the property was not present,JSON_MODIFY tries to insert the new value on the specified path.

strict − It speifies the property referenced by <json path> must be in JSON expression.if property was unable it will return an error.

<json path> − It speifies the path of the property to get update.

  • newvalue − The new value for the property indicated by path. the value must be varchar or text.

Example

Let's try to update the property value of JSON string by using the following query −

DECLARE @work VARCHAR(150) = '{"car":"RX100", "Price":45000}'
SELECT 
   JSON_MODIFY(@work,'$.Item', 'AUDI') AS UpdatedValue;

Output

When we execute the above query, the output is obtained as follows −

+---------------------------------------------------------+
|                                           UpdatedValue  |
+---------------------------------------------------------+
|            {"car":"RX100", "Price":45000,"Item":"AUDI"} |
+---------------------------------------------------------+

Example

Let's look into the another scenario where we are going to update the JSON string value by using the following query−

SELECT JSON_MODIFY('{"Place": "INDIA"}', '$.Place', 'DUBAI') AS 'Result';

Output

On executing the above query, the output is displayed as follows −

+----------------------------------------------+
|                                       Result |
+----------------------------------------------+
|                           {"Place": "DUBAI"} |
+----------------------------------------------+

Example

In the following example we are going to insert new property and value in JSON string by using the following query −

DECLARE @work VARCHAR(150) = '{"car":"BMW","Price":2500000}'
DECLARE @path VARCHAR(100) = '$.Color'
DECLARE @newone VARCHAR(50) = 'Green'
SELECT JSON_MODIFY(@work,@Path, @newone) AS UpdatedValue;

Output

When we execute the above query, the output is obtained as follows −

+---------------------------------------------------------+
|                                           UpdatedValue  |
+---------------------------------------------------------+
|           {"car":"BMW","Price":2500000,"Color":"Green"} |
+---------------------------------------------------------+

Example

Let's look into the another scenario where we are going to add new property value that containing an array by using the following query−

DECLARE @work VARCHAR(4000)
DECLARE @new VARCHAR(256) = N'["Engine","Wipers","DieselTank"]';
Set @work='{"Car":"BMW","Price":"2000000"}'
Select JSON_MODIFY(@work,'$.SpareParts',@new) AS 'UpdatedValue';

Output

On executing the above query, the output is displayed as follows −

+--------------------------------------------------------------------------------------+
|                                                            UpdatedValue              | 
+--------------------------------------------------------------------------------------+
| {"Car":"BMW","Price":"2000000","SpareParts":"[\"Engine\",\"Wipers\",\"DieselTank\"]"}|
+--------------------------------------------------------------------------------------+

Example

Let's look into the following example, where we are going to update the JSON data and retrive output as both original and updated JSON by using the following query −

DECLARE @work VARCHAR(4000)
SET @work= '{"Beach": "ANDAMAN"}'
SELECT @work AS 'OriginalValue',
   JSON_MODIFY(@work, '$.Beach', 'GOA') AS 'ModifiedValue';

Output

When we execute the above query, the output is obtained as follows −

+--------------------------+------------------------+
|          OriginalValue   |          ModifiedValue | 
+--------------------------+------------------------+
|    {"Beach": "ANDAMAN"}  |       {"Beach": "GOA"} |
+--------------------------+------------------------+

Example

Let's look into the following example where we are going to rename the key by using the following query−

DECLARE @work VARCHAR(4000)
SET @work = '{"Brand":"HP"}';
SELECT @work AS 'OriginalValue', 
   JSON_MODIFY(
   JSON_MODIFY(@work, '$.Company', JSON_VALUE(@work,'$.Brand')),
   '$.Brand',NULL) AS UpdatedValue;

Output

On executing the above query, the output is displayed as follows −

+----------------+--------------------+
|OriginalValue   | UpdatedValue       |
+----------------+--------------------+
| {"Brand":"HP"} | {"Company":"HP"}   |
+----------------+--------------------+

Example

Let's consider another scenario, where we are going to make multiple changes to the JSON data by using the following query −

DECLARE @work VARCHAR(4000), @new VARCHAR(100);
SET @work = '{"Location":"Himalayas","Place":"Mountains"}';
SET @new = '["Sheep","SnowBear"]';
SELECT @work AS 'OriginalValue', 
   JSON_MODIFY(JSON_MODIFY(@work, '$.Animals', JSON_QUERY(@new)), '$.Location', 'Shimla') AS 'UpdatedValue';

Output

On executing the above query, the output is displayed as follows −

+----------------------------------------------+----------------------------------------------------------------------------+
|                              OriginalValue   |                                                         UpdatedValue       |
+----------------------------------------------+----------------------------------------------------------------------------+
| {"Location":"Himalayas","Place":"Mountains"} | {"Location":"Shimla","Place":"Mountains","Animals":["Sheep","SnowBear"]}   |
+----------------------------------------------+----------------------------------------------------------------------------+

Example

let's look into the following example, where we are going to increment the JSON data by using the following query −

DECLARE @work VARCHAR(100)='{"click_count": 140}'
PRINT @work
SET @work=JSON_MODIFY(@work,'$.click_count',
   CAST(JSON_VALUE(@work,'$.click_count') AS INT)+3)
PRINT @work

Output

On executing the above query, the output is displayed as follows −

+----------------------+
| {"click_count": 140} |
+----------------------+
| {"click_count": 143} |
+----------------------+
sql-json-functions.htm
Advertisements