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"]
}