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', CAST(1 AS BIT...
