SQL Server 2016: JSON, Part 4 – Data Modification

In my previous article, we have explored how we can transform JSON into the relational data set. We have already analyzed main steps of working with JSON. Now, I am going to describe how you can modify JSON files built into SQL Server.

To modify data, it is necessary to use the JSON_MODIFY statement with the JSON file as an input, as well as a necessary property and a new value.

As you can see, everything seems to be easy. In addition, I would like to note that you can use the key word append if you want to add a new value to the data array.

To delete any property in the file, you need to specify the null value in the lax mode.

However, if you try to do this in the strict mode, then the null value will be specified for the property. In addition, the property will not be deleted from the file. If you try to assign null or any value to a non-existing element in the strict mode, you will receive an error.

Msg 13608, Level 16, State 2, Line 3
Property cannot be found on the specified JSON path.

That is all about the data modification in JSON files. In my next publication, we will analyze how it is possible to store JSON objects in a database and create indexes for them.

Sergey Olontsev

Software Expert at Kaspersky Lab
Sergey has been working with SQL Server for more than a decade as a database administrator, developer, consultant, and trainer, focusing on high availability and disaster recovery solutions, ETL, developing high-performance solutions, troubleshooting and performance tuning. He is an owner of prestigious Microsoft Certified Master certification on SQL Server and MVP Data Platform award and regularly speaks at various SQL Server user group meetings, SQL Saturday events and other conferences worldwide.
Sergey Olontsev