The Database Engine supports the json_modify function to update the value of a property in a JSON document and to return the updated document. (The json_modify function returns an error if the given JSON document does not contain valid JSON.)
The syntax of this function is
json_modify(expression, path, new_value)
expression specifies the name of a variable or a column that contains the JSON document. path is a JSON path expression that specifies the property to update. new_value is the new value for the property specified by path.
To explain the functionality of the json_modify function, Example 29.9 creates the json_update_table table and inserts a JSON document into it.
Example 29.9
CREATE TABLE json_update_table
(id INT PRIMARY KEY IDENTITY, person_and_friends NVARCHAR(2000));
— Insert a row
INSERT INTO json_update_table (person_and_friends) VALUES
(N'{“info”:{“who”: “Fred” , “friends”:[“Lili”, “Hank”]}}’);
The content of the json_update_table table after the execution of the INSERT statement is
The following examples show the supported forms of updates that you can use with the json_modify function.
The UPDATE statement in Example 29.10 modifies the value of the object specified by the following path: ‘$.info.who’. The existing name/value pair (“who”: “Fred”) will be changed in (“who”: “Peter”).
Example 29.10
— Update the value of the object UPDATE json_update_table
SET person_and_friends = json_modify(person_and_friends,
‘$.info.who’, ‘Peter’)
WHERE id = 1;
The UPDATE statement in Example 29.11 shows how you can append an element to the existing array.
Example 29.11
UPDATE json_update_table
SET person_and_friends= json_modify(person_and_friends,
‘append $.info.friends’, ‘Wendy’)
WHERE id = 1;
The modified JSON document is as follows:
{“info”:{“who”: “Peter” , “friends”:[“Lili”, “Hank”,”Wendy”]}}
Example 29.12 inserts a new object (name/value pair) in the existing document. Note that the new object will be appended to the content of the existing document.
Example 29.12
— INSERT a new object
UPDATE json_update_table
SET person_and_friends = json_modify(person_and_friends,
‘$.info.surname’, ‘Birch’)
WHERE id = 1;
The result is
Example 29.13 shows how you can delete an object in the existing document.
Example 29.13
UPDATE json_update_table
SET person_and_friends = json_modify(person_and_friends,
‘$.info.surname’, NULL)
WHERE id = 1;
The UPDATE statement in Example 29.13 uses the NULL value to delete the existing object, the name/value pair “info.surname”: “Birch”. Note that this is valid only in the lax mode.
NOTE JSON functions, such as json_value and json_query, can be in either of two modes: lax or strict. The lax mode generally means that an error will not be raised with an invalid operation, and a NULL value will be returned instead. If strict mode is specified, an error will be raised with an invalid operation. In this chapter, the lax mode, which is the default value, is implicitly used for all examples. (For particular differences between the lax mode and strict mode, see Microsoft Docs.)
Note that the json_modify function currently does not support the insertion of an array member before or after another array member. The only way you can do it is to delete the document first and then insert the modified one, as shown in Example 29.14.
Example 29.14
UPDATE json_update_table
SET person_and_friends = json_modify(person_and_friends, ‘$.info.friends’, NULL)
WHERE id = 1;
UPDATE json_update_table
SET person_and_friends = json_modify (person_and_friends, ‘$.info.friends’, ‘Lili, Wendy,Hank’)
WHERE id = 1;
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.