This article explores JSON_MODIFY() function to modify JSON Data in the SQL Server.
Introduction
Java Script Object Notation is a popular language in major NoSQL databases and applications for mobile development. SQL Server 2016 introduced native support for JSON. Suppose you define a variable in SQL Server and it holds JSON key-value pairs.
We use JSON_MODIFY() function to update the JSON string. It can update the following items:
- Update existing property value
- Add a new element in an existing array
- Delete a property from JSON string
- Delete a property
Syntax of JSON_MODIFY() function
JSON_MODIFY (expression, path, newValue)
- Expression: It is the JSON Data string that we want to update. It might be a variable or a column containing JSON
Path: We specify the property that requires an update in the JSON string. It requires the following arguments:
[append] [lax | strict] $.<json path>
- Append: It is an optional argument, and it specifies a new value that should be appended to the array
- Lax: it is the default mode in the path argument. Suppose we specify a property in the path argument that does not exist, in this case, the JSON_MODIFY function tries to insert the specified new value. It might give you an error message in case it cannot insert the value
- Strict: In the strict mode, if the property we specified does not exist, it does not try to insert the value. You get an error message
- Json_path: it contains the property path that we wish to update
- New value: It is the new value that we require to update in the JSON
Let’s understand the usage of the JSON_MODIFY() function using examples.
Example 1: Update JSON property value
In the below example, we have key-value pair for Brand and Product key. Suppose you wish to update the product value in this JSON, we can use the following code using JSON_MODIFY().
|
1 |
SELECT JSON_MODIFY('{"Brand":"HP","Product":"Laptop"}', '$.Product', 'Laptop') AS 'Updated JSON'; |
It returns the updated JSON string in the output.
In this example, note the following things.
- First argument expression contains original JSON {“Brand”:”HP”,”Product”:”Laptop”}
- $.Product is the property path that we want to update
- A laptop is a new value that we want to update in the $.Product key
Example 2: Get original and updated JSON data
Suppose for comparison, we want both original (before the update) and updated (after update) JSON data in the output. In this query, we declared a variable and stored JSON into it. Later, we used JSON_MODIFY() function to get the updated JSON.
|
1 2 3 4 5 |
DECLARE @OriginalJSON NVARCHAR(4000) Set @OriginalJSON='{"Brand":"HP","Product":"Laptop"}' Select @OriginalJSON as 'Before Update', JSON_MODIFY(@OriginalJSON,'$.Product', 'Laptop') AS 'Updated JSON'; |
In the output, we get both original and updated JSON.
In the below query, note the following:
- Variable @path contains the required value for the update
- Specify variable in the JSON_MODIFY() function
|
1 2 3 4 5 6 7 |
DECLARE @OriginalJSON NVARCHAR(4000), @newvalue varchar(30),@path varchar(20) Set @OriginalJSON='{"Brand":"HP","Product":"Laptp"}' Set @newvalue='Laptop' set @path='$.Product' Select @OriginalJSON as 'Before Update', JSON_MODIFY(@OriginalJSON,@path, @newvalue) AS 'Updated JSON'; |
Example 3: Add a new JSON property
In this example, we specify a new property $.Quantity along with its value. As we know, by default, JSON_MODIFY() function uses lax path mode, so it inserts this new property because it does not exist in the original JSON string.
|
1 2 3 4 5 |
DECLARE @OriginalJSON NVARCHAR(4000) Set @OriginalJSON='{"Brand":"HP","Product":"Laptop"}' Select @OriginalJSON as 'Before Update', JSON_MODIFY(@OriginalJSON,'$.Quantity',10) AS 'Updated JSON'; |
You can see it inserts the property using JSON_MODIFY() function if the property does not exist.
Let’s specify strict path mode for the above example, and it does not insert the property in JSON as it does not exist.
|
1 2 3 4 5 |
DECLARE @OriginalJSON NVARCHAR(4000) Set @OriginalJSON='{"Brand":"HP","Product":"Laptop"}' Select @OriginalJSON as 'Before Update', JSON_MODIFY(@OriginalJSON,'strict$.Quantity',10) AS 'Updated JSON'; |
Example 4: Add a new JSON property containing an Array
In the previous example, we added a new property that contains a value. We can have an array in the JSON as well.
In the below query, we defined a variable @DataArray and defined an array to have multiple values.
|
1 2 3 4 5 6 |
DECLARE @OriginalJSON NVARCHAR(4000) DECLARE @DataArray NVARCHAR(256) = N'["Keyboard","Mouse","Monitor"]'; Set @OriginalJSON='{"Brand":"HP","Product":"Laptop"}' Select @OriginalJSON as 'Before Update', JSON_MODIFY(@OriginalJSON,'$.Accessories',@DataArray) AS 'Updated JSON'; |
In the output, we verify that it adds a new node in JSON data, but we see a backslash symbol for each element in the array. It is not the desired JSON array. We need to use another JSON function JSON_QUERY() to insert an array using the JSON_MODIFY() function.
|
1 2 3 4 5 6 |
DECLARE @OriginalJSON NVARCHAR(4000) DECLARE @DataArray NVARCHAR(256) = N'["Keyboard","Mouse","Monitor"]'; Set @OriginalJSON='{"Brand":"HP","Product":"Laptop"}' Select @OriginalJSON as 'Before Update', JSON_MODIFY(@OriginalJSON,'$.Accessories',JSON_Query(@DataArray)) AS 'Updated JSON'; |
We get the property array in the output after using the JSON_QUERY() function.
Example 5: Append a value to JSON array
In the previous example, we inserted a new array in the JSON string. Suppose we want to insert a new value in the existing JSON array. We can use JSON_MODIFY() function for this as well.
In the below query, we insert Printer in the accessories array. For this purpose, we use argument Append before the array property.
|
1 2 3 4 5 |
DECLARE @OriginalJSON NVARCHAR(4000) Set @OriginalJSON='{"Brand":"HP","Product":"Laptop","Accessories":["Keyboard","Mouse","Monitor"]}' Select @OriginalJSON as 'Before Update', JSON_MODIFY(@OriginalJSON,'append $.Accessories','Printer') AS 'Updated JSON'; |
You can look for additional value in the updated JSON.
Example 6: Append a JSON object in the JSON data
We can have a nested JSON object as well inside a JSON. For example, a JSON string can contain another JSON string in its property.
For example, suppose we want to add a JSON object that contains seller information in the existing JSON. We need to specify the new JSON in the third parameter. For simplicity purposes, it’s better to declare a variable and contain JSON into it similar to the below code.
|
1 2 3 4 5 |
DECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100); SET @OriginalJSON = '{"Brand":"HP","Product":"Laptop","Accessories":["Keyboard","Mouse","Monitor"]}'; SET @newjson = '{"Seller":"ABC corp","Buyer":"XYZ corp"}'; SELECT @OriginalJSON AS 'Before Update', JSON_MODIFY(@OriginalJSON, '$.SellerBuyer', @newjson) AS 'Updated JSON'; |
In the above query output, we see that SQL Server escaped double-quotes and we get backslash in the new JSON. As highlighted earlier, we can use JSON_QUERY() function and get the valid JSON output, as shown below.
|
1 2 3 4 5 |
DECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100); SET @OriginalJSON = '{"Brand":"HP","Product":"Laptop","Accessories":["Keyboard","Mouse","Monitor"]}'; SET @newjson = '{"Seller":"ABC corp","Buyer":"XYZ corp"}'; SELECT @OriginalJSON AS 'Before Update', JSON_MODIFY(@OriginalJSON, '$.SellerBuyer', JSON_QUERY(@newjson)) AS 'Updated JSON'; |
In the below code, we have an existing array [Accessories] with few values. Let’s add the new JSON into this Accessories array. For this purpose, we use append argument along with the [$. Accessories ] in the second argument. You can view the output shows JSON in the array.
|
1 2 3 4 5 |
DECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100); SET @OriginalJSON = '{"Brand":"HP","Product":"Laptop","Accessories":["Keyboard","Mouse","Monitor"]}'; SET @newjson = '{"Seller":"ABC corp","Buyer":"XYZ corp"}'; SELECT @OriginalJSON AS 'Before Update', JSON_MODIFY(@OriginalJSON, 'append $.Accessories', JSON_QUERY(@newjson)) AS 'Updated JSON'; |
Example 7: Removing a JSON property
Sometimes, we may want to remove an existing JSON property. To remove a JSON node or property, pass the NULL value in the third argument. We can also remove a specific value from the JSON array.
In the below query, we want to remove the keyboard from the Accessories array. We specify the following in the query.
- Specify the position of an element in the JSON array. The first element exists at zero position. For the keyboard, the value specifies the path as $.Accessories[0]
- Specify a NULL value in the third argument
|
1 2 3 4 |
DECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100); SET @OriginalJSON = '{"Brand":"HP","Product":"Laptop","Accessories":["Keyboard","Mouse","Monitor"]}'; SELECT @OriginalJSON AS 'Before Update', JSON_MODIFY(@OriginalJSON, '$.Accessories[0]', NULL) AS 'Updated JSON'; |
Once we execute the above JSON script, it replaced the array element with NULL. Are we ok with this NULL value? No, Right!
- JSON_MODIFY() should not replace a value with a NULL value
- It should obliterate the element
We can replace the array with a new set of values. It helps to eliminate NULL values in the output.
In the below query, we replaced the existing array with a new array that does not contain the element we do not require.
|
1 2 3 4 5 |
DECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100); SET @OriginalJSON = '{"Brand":"HP","Product":"Laptop","Accessories":["Keyboard","Mouse","Monitor"]}'; set @newjson='["Mouse","Monitor"]' SELECT @OriginalJSON AS 'Before Update', JSON_MODIFY(@OriginalJSON, '$.Accessories', JSON_Query(@newjson)) AS 'Updated JSON'; |
In the output, we can verify that replacing an array solved the problem.
In the below query, we remove Products property from the JSON data. We do not get NULL value in the output in this case, and it removed the node as shown below.
|
1 2 3 4 |
DECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100); SET @OriginalJSON = '{"Brand":"HP","Product":"Laptop","Accessories":["Keyboard","Mouse","Monitor"]}'; SELECT @OriginalJSON AS 'Before Update', JSON_MODIFY(@OriginalJSON, '$.Product', NULL) AS 'Updated JSON'; |
Example 8: Update a JSON property
We can update an existing property using the JSON_MODIFY() function as well. For example, suppose someone entered the wrong value in the Accessories array in the zero position. The below code updates the element with a new specified value.
|
1 2 3 4 5 |
DECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100); SET @OriginalJSON = '{"Brand":"HP","Product":"Laptop","Accessories":["Keyboard","Mouse","Monitor"]}'; set @newjson='Printer' SELECT @OriginalJSON AS 'Before Update', JSON_MODIFY(@OriginalJSON, '$.Accessories[0]', @newjson) AS 'Updated JSON'; |
Imagine what is the output of the below code. Here, we try to update an array element, but the array does not exist in the JSON data.
|
1 2 3 4 5 |
DECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100); SET @OriginalJSON = '{"Brand":"HP","Product":"Laptop","Accessories":["Keyboard","Mouse","Monitor"]}'; set @newjson='Printer' SELECT @OriginalJSON AS 'Before Update', JSON_MODIFY(@OriginalJSON, '$.Company[0]', @newjson) AS 'Updated JSON'; |
We do not get any error message. It shows similar JSON in both original and updated JSON. You should recall here that by default, JSON_MODIFY() function uses lax path mode.
If we rerun the previous code with a small change. We modified path mode from lax to strict. This time it does not work and complains about invalid property in the JSON path.
|
1 2 3 4 5 |
DECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100); SET @OriginalJSON = '{"Brand":"HP","Product":"Laptop","Accessories":["Keyboard","Mouse","Monitor"]}'; set @newjson='Printer' SELECT @OriginalJSON AS 'Before Update', JSON_MODIFY(@OriginalJSON, '$.Company[0]', @newjson) AS 'Updated JSON'; |
Example 9: Rename a Key
We have seen many use cases of JSON_MODIFY() function in the previous examples. Suppose, you want to rename a key available in the JSON Data. It is like rename an existing column in a relational database table.
In the below code, we use Nested JSON_MODIFY() functions along with a combination of JSON_VALUE function. Look at the query carefully. We created a new key and dropped the existing key after copying the value in it. It is a little complicated, but JSON_MODIFY() does not have a direct option to rename a key.
|
1 2 3 4 5 6 |
DECLARE @OriginalJSON NVARCHAR(4000) SET @OriginalJSON = '{"Brand":"HP","Product":"Laptop"}'; SELECT @OriginalJSON AS 'Before Update', JSON_MODIFY( JSON_MODIFY(@OriginalJSON, '$.Parts', JSON_VALUE(@OriginalJSON,'$.Product')), '$.Product',NULL); |
Example 10: Multiple changes
We can perform multiple changes in the JSON data. JSON_MODIFY() does not support multiple updates in a single function call. To overcome this problem, we can use nested JSON_MODIFY() functions.
In the below query, we update the following values.
- It updates the contents of the JSON array
- It updates the value for the Product key
|
1 2 3 4 5 |
DECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100); SET @OriginalJSON = '{"Brand":"HP","Product":"Laptop","Accessories":["Keyboard","Mouse","Monitor"]}'; SET @newjson = '["HDMI","USB"]'; SELECT @OriginalJSON AS 'Source JSON', JSON_MODIFY(JSON_MODIFY(@OriginalJSON, '$.Accessories', JSON_QUERY(@newjson)), '$.Brand', 'Lenovo') AS 'Updated JSON'; |
Conclusion
In this article, we overviewed JSON_MODIFY() function and its usage to update JSON data using various examples. I hope it should be a knowledge article for you to understand the JSON function – JSON_MODIFY().
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023