JSON_MODIFY (Transact-SQL)

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

Updates the value of a property in a JSON string and returns the updated JSON string.

Transact-SQL syntax conventions

Syntax

JSON_MODIFY ( expression , path , newValue )  

Arguments

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 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.
The new value must be a [n]varchar or text.

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.

Return Value

Returns the updated value of expression as properly formatted JSON text.

Remarks

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).

In lax mode, JSON_MODIFY tries to create a new key:value pair, but in some cases it might fail.

Examples

Example - Basic operations

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

-- Set name NULL 

SET @info=JSON_MODIFY(@info,'strict $.name',NULL)

PRINT @info

-- Delete name  

SET @info=JSON_MODIFY(@info,'$.name',NULL)

PRINT @info

-- Add skill  

SET @info=JSON_MODIFY(@info,'append $.skills','Azure')

PRINT @info

Results

{
    "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"
}

Example - Multiple updates

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 @info

Results

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

Example - Rename a key

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 @product

Results

{
    "price": 49.99
} {
    "Price": 49.99
}

If you don't cast the new value to a numeric type, JSON_MODIFY treats it as text and surrounds it with double quotes.

Example - Increment a value

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 @stats

Results

{
    "click_count": 173
} {
    "click_count": 174
}

Example - Modify a JSON object

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 @info

Results

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "John",
    "skills": "[\"C#\",\"T-SQL\",\"Azure\"]"
}

To avoid automatic escaping, provide newValue by using the JSON_QUERY function. JSON_MODIFY knows that the value returned by JSON_QUERY 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 @info

Results

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "John",
    "skills": ["C#", "T-SQL", "Azure"]
}

Example - Update a JSON column

The following example updates the value of a property in a table column that contains JSON.

UPDATE Employee
SET jsonCol=JSON_MODIFY(jsonCol,'$.info.address.town','London')
WHERE EmployeeID=17

See Also

• JSON Path Expressions (SQL Server)
• JSON Data (SQL Server)