In SQL Server, you can use the T-SQL JSON_MODIFY() function to modify the value of a property in a JSON string. The function returns the updated JSON string.
Syntax
The syntax goes like this:
JSON_MODIFY ( expression , path , newValue )
Where expression is the JSON string expression, path is the path to the property you want to update, and newValue is the new value to apply to that property.
Example 1 – Basic Usage
Here’s an example to demonstrate.
SELECT JSON_MODIFY('{"Name": "Homer"}', '$.Name', 'Bart') AS 'Result';
Result:
+------------------+
| Result |
|------------------|
| {"Name": "Bart"} |
+------------------+
In this example:
{"Name": "Homer"}is the original JSON string$.Nameis the path (this begins with$.followed by the path to the property we want to update).Bartis the new value we want to assign toName(i.e. to replace the current value)
Example 2 – Return the Original and Modified JSON
Note that JSON_MODIFY() doesn’t modify the original JSON. It takes a copy, then modifies and returns the copy.
Here’s an example to demonstrate this:
DECLARE @suspect NVARCHAR(4000)
SET @suspect= '{"Name": "Homer"}'
SELECT
@suspect AS 'Original String',
JSON_MODIFY(@suspect, '$.Name', 'Bart') AS 'Modified String',
@suspect AS 'Original String';
Result:
+-------------------+-------------------+-------------------+
| Original String | Modified String | Original String |
|-------------------+-------------------+-------------------|
| {"Name": "Homer"} | {"Name": "Bart"} | {"Name": "Homer"} |
+-------------------+-------------------+-------------------+
Example 3 – Nested Properties
The path can use dot-notation to reference nested properties. Here’s an example.
DECLARE @data NVARCHAR(4000)
SET @data=N'{
"Suspect": {
"Name": "Homer Simpson",
"Address": {
"City": "Dunedin",
"Region": "Otago",
"Country": "New Zealand"
},
"Hobbies": ["Eating", "Sleeping", "Base Jumping"]
}
}'
SELECT
JSON_MODIFY(@data,'$.Suspect.Address.City', 'Timaru') AS 'Modified Array';
Result:
+------------------+
| Modified Array |
|------------------|
| {
"Suspect": {
"Name": "Homer Simpson",
"Address": {
"City": "Timaru",
"Region": "Otago",
"Country": "New Zealand"
},
"Hobbies": ["Eating", "Sleeping", "Base Jumping"]
}
} |
+------------------+
So we can see that the city has been changed from Dunedin to Timaru.
Example 4 – Update Values in an Array
You can also update values within an array. In this example, we update a value in the Hobbies array.
DECLARE @data NVARCHAR(4000)
SET @data=N'{
"Suspect": {
"Name": "Homer Simpson",
"Address": {
"City": "Dunedin",
"Region": "Otago",
"Country": "New Zealand"
},
"Hobbies": ["Eating", "Sleeping", "Base Jumping"]
}
}'
SELECT
JSON_MODIFY(@data,'$.Suspect.Hobbies[2]', 'Brain Surgery') AS 'Updated Hobbies';
Result:
+-------------------+
| Updated Hobbies |
|-------------------|
| {
"Suspect": {
"Name": "Homer Simpson",
"Address": {
"City": "Dunedin",
"Region": "Otago",
"Country": "New Zealand"
},
"Hobbies": ["Eating", "Sleeping", "Brain Surgery"]
}
} |
+-------------------+
Seeing as arrays use zero-based numbering, we update the third item by referencing Hobbies[2].
Example 5 – Append a Value to an Array
In this example, we append a value to the Hobbies array. We do this by adding append at the start of the path argument.
DECLARE @data NVARCHAR(4000)
SET @data=N'{
"Suspect": {
"Name": "Homer Simpson",
"Address": {
"City": "Dunedin",
"Region": "Otago",
"Country": "New Zealand"
},
"Hobbies": ["Eating", "Sleeping", "Base Jumping"]
}
}'
SELECT
JSON_MODIFY(@data,'append $.Suspect.Hobbies', 'Brain Surgery') AS 'Updated Hobbies';
Result:
+-------------------+
| Updated Hobbies |
|-------------------|
| {
"Suspect": {
"Name": "Homer Simpson",
"Address": {
"City": "Dunedin",
"Region": "Otago",
"Country": "New Zealand"
},
"Hobbies": ["Eating", "Sleeping", "Base Jumping","Brain Surgery"]
}
} |
+-------------------+
Example 6 – Update a Whole Array
In this example, I update the whole array.
DECLARE @data NVARCHAR(4000)
SET @data=N'{
"Suspect": {
"Name": "Homer Simpson",
"Address": {
"City": "Dunedin",
"Region": "Otago",
"Country": "New Zealand"
},
"Hobbies": ["Eating", "Sleeping", "Base Jumping"]
}
}'
SELECT
JSON_MODIFY(@data,'$.Suspect.Hobbies', JSON_QUERY('["Chess", "Brain Surgery"]')) AS 'Updated Hobbies';
Result:
+-------------------+
| Updated Hobbies |
|-------------------|
| {
"Suspect": {
"Name": "Homer Simpson",
"Address": {
"City": "Dunedin",
"Region": "Otago",
"Country": "New Zealand"
},
"Hobbies": ["Chess", "Brain Surgery"]
}
} |
+-------------------+
Note that in this example, the third argument is passed to the JSON_QUERY() function. If I hadn’t done this, SQL Server would have escaped the double quotes and square brackets using the backslash (\) character (and therefore messing up the array). It would’ve done this because it wouldn’t have known whether the updated value was an actual array, or a string literal.
So to get around this, we can use JSON_QUERY(). This function returns valid JSON, and SQL Server will then assume that the new value is an array.
Here’s what would’ve happened if we hadn’t used JSON_QUERY():
DECLARE @data NVARCHAR(4000)
SET @data=N'{
"Suspect": {
"Name": "Homer Simpson",
"Address": {
"City": "Dunedin",
"Region": "Otago",
"Country": "New Zealand"
},
"Hobbies": ["Eating", "Sleeping", "Base Jumping"]
}
}'
SELECT
JSON_MODIFY(@data,'$.Suspect.Hobbies', '["Chess", "Brain Surgery"]') AS 'Updated Hobbies';
Result:
+-------------------+
| Updated Hobbies |
|-------------------|
| {
"Suspect": {
"Name": "Homer Simpson",
"Address": {
"City": "Dunedin",
"Region": "Otago",
"Country": "New Zealand"
},
"Hobbies": "[\"Chess\", \"Brain Surgery\"]"
}
} |
+-------------------+
So SQL Server has escaped the square brackets and double quotes.
Example 7 – Update a Whole Object
Here’s an example of updating a whole object.
DECLARE @data NVARCHAR(4000)
SET @data=N'{
"Suspect": {
"Name": "Homer Simpson",
"Hobbies": ["Eating", "Sleeping", "Base Jumping"]
}
}'
SELECT
JSON_MODIFY(@data,'$.Suspect', JSON_QUERY('{"Name": "Peter Griffin", "Hobbies": "None"}')) AS 'Updated Object';
Result:
+------------------+
| Updated Object |
|------------------|
| {
"Suspect": {"Name": "Peter Griffin", "Hobbies": "None"}
} |
+------------------+
Again, if we hadn’t used JSON_QUERY(), we would’ve received an escaped string:
DECLARE @data NVARCHAR(4000)
SET @data=N'{
"Suspect": {
"Name": "Homer Simpson",
"Hobbies": ["Eating", "Sleeping", "Base Jumping"]
}
}'
SELECT
JSON_MODIFY(@data,'$.Suspect', '{"Name": "Peter Griffin", "Hobbies": "None"}') AS 'Updated Object';
Result:
+------------------+
| Updated Object |
|------------------|
| {
"Suspect": "{\"Name\": \"Peter Griffin\", \"Hobbies\": \"None\"}"
} |
+------------------+
Example 8 – Rename a Key
You’re not just limited to updating a property’s value, you can also rename its key. Here’s an example.
DECLARE @data NVARCHAR(50)='{"Name":"Homer"}'
PRINT @data
-- Rename the key
SET @data=
JSON_MODIFY(
JSON_MODIFY(@data,'$.Handle', JSON_VALUE(@data,'$.Name')),
'$.Name',
NULL
)
PRINT @data
Result:
{"Name":"Homer"}
{"Handle":"Homer"}
Here, we take the value from the existing property and assign it to a new key/value pair. We then set the value of the original key to NULL (which automatically deletes it).
For more examples of renaming a key, see How to Rename a JSON Key in SQL Server.