Microsoft SQL Server Modify JSON text Modify value in JSON text on the specified path
Example
JSON_MODIFY function uses JSON text as input parameter, and modifies a value on the specified path using third argument:
declare @json nvarchar(4000) = N'{"Id":1,"Name":"Toy Car","Price":34.99}'
set @json = JSON_MODIFY(@json, '$.Price', 39.99)
print @json -- Output: {"Id":1,"Name":"Toy Car","Price":39.99}
As a result, we will have new JSON text with "Price":39.99 and other value will not be changed. If object on the specified path does not exists, JSON_MODIFY will insert key:value pair.
In order to delete key:value pair, put NULL as new value:
declare @json nvarchar(4000) = N'{"Id":1,"Name":"Toy Car","Price":34.99}'
set @json = JSON_MODIFY(@json, '$.Price', NULL)
print @json -- Output: {"Id":1,"Name":"Toy Car"}
JSON_MODIFY will by default delete key if it does not have value so you can use it to delete a key.
