In the next examples, you will update the value of an existing property. You will start by updating the Year property from 1973 to 1975. Here is the code:
DECLARE @json NVARCHAR(MAX) = N'{ "Album":"Wish You Were Here", "Year":1973 }'; PRINT JSON_MODIFY(@json, '$.Year', 1975); PRINT JSON_MODIFY(@json, 'strict $.Year', 1975);
You invoked the function twice to demonstrate using both JSON path modes: lax and strict. Here are the output strings:
{ "Album":"Wish You Were Here", "Year":1975 } { "Album":"Wish You Were Here", "Year":1975 }
You can see that there is no difference between lax and strict mode if the property specified with the path exists.
The following example demonstrates how to update a value of an array element within a JSON text. Assume you want to replace the first element of the Members array (Gilmour) with the value (Barrett):
DECLARE @json NVARCHAR(MAX) = N'{ "Album":"Wish You Were Here", "Year":1975, "Members":["Gilmour","Waters","Wright","Mason"] }'; PRINT JSON_MODIFY(@json, '$.Members[0]', 'Barrett');
Here is the expected result:
{ "Album":"Wish You Were Here", "Year":1975, "Members":["Barrett","Waters","Wright","Mason"] }
If you want to add a new element to an array, you have to use append. In the following example, you simply add another element in the Members array:
DECLARE @json NVARCHAR(MAX) = N'{ "Album":"Wish You Were Here", "Year":1975, "Members":["Gilmour","Waters","Wright","Mason"] }'; PRINT JSON_MODIFY(@json, 'append $.Members', 'Barrett');
Here is the result:
{ "Album":"Wish You Were Here", "Year":1975, "Members":["Gilmour","Waters","Wright","Mason","Barrett"] }
If you specify an index that is out of range or if the array does not exist, you will get:
- Strict mode: This shows an error message and no return value (batch-level exception).
- Lax mode: This shows no error; the original input string is returned.
To update a value of a JSON property to NULL, you have to use a JSON path in strict mode. Use the following code to update the Year property from the input JSON string to a NULL value:
DECLARE @json NVARCHAR(MAX) = N'{ "Album":"Wish You Were Here", "Year":1975, "Members":["Gilmour","Waters","Wright","Mason"] }'; PRINT JSON_MODIFY(@json, 'strict $.Year', NULL);
Here is the output.
{ "Album":"Wish You Were Here", "Year":null, "Members":["Gilmour","Waters","Wright","Mason"] }