SQL Server 2016: JSON, Part 4 – Data Modification

Total: 1 Average: 2

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.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":30,"skills":
["SQL Server 2014","T-SQL","JSON"]}';
 
set @json = json_modify(@json, 'lax$.age', json_value(@json, '$.age') + 2);
set @json = json_modify(@json, '$.skills[0]', 'SQL Server 2016');
set @json = json_modify(@json, 'append strict$.skills', 'In-Memory OLTP');
 
select * from openjson(@json);

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.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":30,"skills":
["SQL Server 2014","T-SQL","JSON"]}';
 
select * from openjson(json_modify(@json, 'lax$.age', null));
select * from openjson(json_modify(@json, 'strict$.age', null));

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.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":30,"skills":
["SQL Server 2014","T-SQL","JSON"]}';
 
select * from openjson(json_modify(@json, 'strict$.hobby', null));

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