Implement equivalent of JSON_MODIFY

[EitanBlumin]

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 .

lax
Specifies that the property referenced by 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 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.

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

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

Source: JSON_MODIFY (Transact-SQL).