SQL Server JSON_MODIFY(): Update JSON Values

JSON_MODIFY(expression, path, newValue) returns a JSON document with one property updated, inserted, or deleted. Use the append path modifier to add an item to an array. The function was introduced in SQL Server 2016 (13.x). See Microsoft’s JSON_MODIFY reference.

Syntax

JSON_MODIFY(expression, path, newValue)

The input must contain valid JSON. The path can use lax or strict mode; lax is the default. In SQL Server 2017 (14.x) and Azure SQL Database, path can also be a variable.

Update, insert, and append

In lax mode, JSON_MODIFY updates a property if it exists. If the final property does not exist, it tries to add it; the intermediate parent objects must already exist. Use append when the path points to an existing array:

DECLARE @doc nvarchar(max) = N'{"id":17,"status":"queued","tags":["sql"]}';

SET @doc = JSON_MODIFY(@doc, '$.status', 'ready');
SET @doc = JSON_MODIFY(@doc, '$.source', 'api');
SET @doc = JSON_MODIFY(@doc, 'append $.tags', 'database');
SET @doc = JSON_MODIFY(@doc, '$.metadata', JSON_QUERY(N'{"region":"eu"}'));

SELECT @doc AS updated_json;

The result represents:

{"id":17,"status":"ready","tags":["sql","database"],"source":"api","metadata":{"region":"eu"}}

Each call changes only one property. For multiple changes, nest calls or assign the result of each call back to a variable as above.

Delete a property or set it to JSON null

The effect of newValue = NULL depends on path mode:

DECLARE @doc nvarchar(max) = N'{"id":17,"status":"ready"}';

-- Delete status in lax mode.
SELECT JSON_MODIFY(@doc, '$.status', NULL) AS status_removed;

-- Keep status but set its JSON value to null.
SELECT JSON_MODIFY(@doc, 'strict $.status', NULL) AS status_set_to_json_null;

These results are different: the first document has no status key; the second has "status": null.

Insert an object or array as JSON

Text values are escaped and inserted as JSON strings, even when the text itself looks like JSON:

DECLARE @doc nvarchar(max) = N'{"id":17}';

SELECT JSON_MODIFY(@doc, '$.metadata', N'{"region":"eu"}') AS text_value;

The metadata value in this result is a quoted string, not an object. Wrap valid JSON text in JSON_QUERY() to insert it as an object or array:

SELECT JSON_MODIFY(@doc, '$.metadata', JSON_QUERY(N'{"region":"eu"}')) AS json_object;

JSON_QUERY() marks the value as a JSON fragment so that JSON_MODIFY() doesn’t escape it. The same approach works for arrays.

Strict mode and missing parent objects

In strict mode, the property must already exist; a missing property raises an error. Lax mode can add the final key, but it can’t create missing intermediate objects automatically. For example, updating $.customer.address.city fails if customer or address is absent or isn’t an object.

For path syntax shared across SQL Server JSON functions, see the SQL Server JSON path guide. For scalar extraction, see JSON_VALUE(). For object and array fragments, see JSON_QUERY(). To convert array items into relational rows, use OPENJSON(). Browse the SQL Server JSON function guide for the full task-based list.