Modifying JSON data
You might sometimes need to update only a part of JSON data. In SQL Server 2016, you can modify JSON data using the JSON_MODIFY function. It allows you to:
Update the value of an existing property
Add a new element to an existing array
Insert a new property and its value
Delete a property based on a combination of modes and provided values
The function accepts three mandatory input arguments:
Expression: This is a variable or column name containing JSON text
Path: This is the JSON path expression with an optional modifier append
new_value: This is the new value for the property specified in the path expression
The JSON_MODIFY function returns the updated JSON string. In the next subsections, you will see this function in action.
Adding a new JSON property
In the following code example, you add a new property named IsVinyl with the value true:
DECLARE @json NVARCHAR(MAX) = N'{
"Album":"Wish You Were Here",
"Year":1975
}';
PRINT JSON_MODIFY(@json, '$.IsVinyl...
