Transact-SQL Syntax ConventionsUpdates the value of a property in a JSON string and returns the updated JSON string.
Transact-SQL Syntax Conventions
expression
An expression. Typically the name of a variable or a column that contains JSON text.
JSON_MODIFY returns an error if expression doesn’t contain valid JSON.
path
A JSON path expression that specifies the property to update.
path has the following syntax:
[append] [ lax | strict ] $.<json path>
append
Optional modifier that specifies that the new value should be appended to the array referenced by <json path>.
lax
Specifies that the property referenced by <json path> does not have to exist. If the property is not present, JSON_MODIFY tries to insert the new value on the specified path. Insertion may fail if the property can’t be inserted on the path. If you don’t specify lax or strict, lax is the default mode.
strict
Specifies that the property referenced by <json path> must be in the JSON expression. If the property is not present, JSON_MODIFY returns an error.
<json path>
Specifies the path for the property to update. For more info, see JSON Path Expressions (SQL Server).
In SQL Server 2017 (14.x) and in SQL Server 2017 (14.x) Azure SQL Database you can provide a variable as the value of path.
JSON_MODIFY returns an error if the format of path isn’t valid.
newValue
The new value for the property specified by path.
In lax mode, JSON_MODIFY deletes the specified key if the new value is NULL.
JSON_MODIFY escapes all special characters in the new value if the type of the value is NVARCHAR or VARCHAR. A text value is not escaped if it is properly formatted JSON produced by FOR JSON, JSON_QUERY, or JSON_MODIFY.
Returns the updated value of expression as properly formatted JSON text.
The JSON_MODIFY function lets you either update the value of an existing property, insert a new key:value pair, or delete a key based on a combination of modes and provided values.
The following table compares the behavior of JSON_MODIFY in lax mode and in strict mode. For more info about the optional path mode specification (lax or strict), see JSON Path Expressions (SQL Server).
| Existing value | Path exists | Lax mode | Strict mode |
|---|---|---|---|
| Not NULL | Yes | Update the existing value. | Update the existing value. |
| Not NULL | No | Try to create a new key:value pair on the specified path. This may fail. For example, if you specify the path $.user.setting.theme, JSON_MODIFY does not insert the key theme if the $.user or $.user.settings objects do not exist, or if settings is an array or a scalar value. |
Error – INVALID_PROPERTY |
| NULL | Yes | Delete the existing property. | Set the existing value to null. |
| NULL | No | No action. The first argument is returned as the result. | Error – INVALID_PROPERTY |
In lax mode, JSON_MODIFY tries to create a new key:value pair, but in some cases it might fail.
The following example shows basic operations that can be done with JSON text.
Query
DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
PRINT @info
-- Update name
SET @info=JSON_MODIFY(@info,'$.name','Mike')
PRINT @info
-- Insert surname
SET @info=JSON_MODIFY(@info,'$.surname','Smith')
PRINT @info
-- Delete name
SET @info=JSON_MODIFY(@info,'$.name',NULL)
PRINT @info
-- Add skill
SET @info=JSON_MODIFY(@info,'append $.skills','Azure')
PRINT @infoResults
{
"name": "John",
"skills": ["C#", "SQL"]
} {
"name": "Mike",
"skills": ["C#", "SQL"]
} {
"name": "Mike",
"skills": ["C#", "SQL"],
"surname": "Smith"
} {
"skills": ["C#", "SQL"],
"surname": "Smith"
} {
"skills": ["C#", "SQL", "Azure"],
"surname": "Smith"
}With JSON_MODIFY you can update only one property. If you have to do multiple updates, you can use multiple JSON_MODIFY calls.
Query
DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
PRINT @info
-- Multiple updates
SET @info=JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(@info,'$.name','Mike'),'$.surname','Smith'),'append $.skills','Azure')
PRINT @infoResults
{
"name": "John",
"skills": ["C#", "SQL"]
} {
"name": "Mike",
"skills": ["C#", "SQL", "Azure"],
"surname": "Smith"
}The following example shows how to rename a property in JSON text with the JSON_MODIFY function. First you can take the value of an existing property and insert it as a new key:value pair. Then you can delete the old key by setting the value of the old property to NULL.
Query
DECLARE @product NVARCHAR(100)='{"price":49.99}'
PRINT @product
-- Rename property
SET @product=
JSON_MODIFY(
JSON_MODIFY(@product,'$.Price',CAST(JSON_VALUE(@product,'$.price') AS NUMERIC(4,2))),
'$.price',
NULL
)
PRINT @productResults
If you don’t cast the new value to a numeric type, JSON_MODIFY treats it as text and surrounds it with double quotes.
The following example shows how to increment the value of a property in JSON text with the JSON_MODIFY function. First you can take the value of the existing property and insert it as a new key:value pair. Then you can delete the old key by setting the value of the old property to NULL.
Query
DECLARE @stats NVARCHAR(100)='{"click_count": 173}'
PRINT @stats
-- Increment value
SET @stats=JSON_MODIFY(@stats,'$.click_count',
CAST(JSON_VALUE(@stats,'$.click_count') AS INT)+1)
PRINT @statsResults
JSON_MODIFY treats the newValue argument as plain text even if it contains properly formatted JSON text. As a result, the JSON output of the function is surrounded with double quotes and all special characters are escaped, as shown in the following example.
Query
DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
PRINT @info
-- Update skills array
SET @info=JSON_MODIFY(@info,'$.skills','["C#","T-SQL","Azure"]')
PRINT @infoResults
To avoid automatic escaping, provide newValue by using the JSON_QUERY function. JSON_MODIFY knows that the value returned by JSON_MODIFY is properly formatted JSON, so it doesn’t escape the value.
Query
DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
PRINT @info
-- Update skills array
SET @info=JSON_MODIFY(@info,'$.skills',JSON_QUERY('["C#","T-SQL","Azure"]'))
PRINT @infoResults
The following example updates the value of a property in a table column that contains JSON.