The JSON_MODIFY function in SQL Server is a powerful tool used to modify the value of a specified key in a JSON string or add new keys and values to a JSON object. It is particularly useful when working with JSON data stored in database tables.
Syntax
JSON_MODIFY ( expression , path , newValue )
expression: The JSON string you want to modify. It must be of type nvarchar(max) or varchar(max).
path: Specifies the location in the JSON document where the modification should occur. It uses JSON path syntax.
$ refers to the root element of the JSON document.
$.property accesses a specific property in the JSON object.
$.array[index] accesses an element in an array by index.
newValue: The new value to set at the specified path. It can be a string, number, boolean, null, or another JSON object or array.
Features
Modify an Existing Value: If the specified path points to an existing key, JSON_MODIFY updates its value.
Add a New Key-Value Pair: If the key specified in the path does not exist, JSON_MODIFY adds it.
Delete a Key: Passing NULL as the new value removes the key from the JSON object.
Examples
1. Modify an Existing Value
DECLARE @json NVARCHAR(MAX) = N'{"name": "John", "age": 30}';
SELECT JSON_MODIFY(@json, '$.age', 35) AS ModifiedJson;
Output:
{"name": "John", "age": 35}
2. Add a New Key-Value Pair
DECLARE @json NVARCHAR(MAX) = N'{"name": "John"}';
SELECT JSON_MODIFY(@json, '$.age', 30) AS ModifiedJson;
Output:
{"name": "John", "age": 30}
3. Delete a Key
DECLARE @json NVARCHAR(MAX) = N'{"name": "John", "age": 30}';
SELECT JSON_MODIFY(@json, '$.age', NULL) AS ModifiedJson;
Output:
{"name": "John"}
4. Modify an Array
DECLARE @json NVARCHAR(MAX) = N'{"colors": ["red", "green", "blue"]}';
SELECT JSON_MODIFY(@json, '$.colors[1]', 'yellow') AS ModifiedJson;
Output:
{"colors": ["red", "yellow", "blue"]}
Considerations
Data Type: The expression parameter must be a valid JSON string. If it is not valid JSON, SQL Server will return NULL.
Path Validation: If the path is invalid or points to a non-existent key in the JSON object, SQL Server will either add a new key (if possible) or return the original JSON.
Performance: Using JSON_MODIFY on large JSON strings can impact performance, so it’s best to use it judiciously.
Use Cases
Updating JSON columns in a database table.
Modifying configuration settings stored as JSON.
Dynamically adding or removing data from JSON objects in queries.
By enabling easy and dynamic manipulation of JSON data, JSON_MODIFY helps simplify tasks that involve JSON documents in SQL Server.