Mastering SQL Server 2017

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 ...