SQL Server 2017 Developer's Guide

Multiple changes

You can change only one property at a time; for multiple changes you need multiple calls. In this example, you want to update the IsVinyl property to false, add a new property Recorded, and add another element called Barrett to the Members property:

DECLARE @json NVARCHAR(MAX) = N'{ 
"Album":"Wish You Were Here", 
"Year":1975, 
"IsVinyl":true, 
"Members":["Gilmour","Waters","Wright","Mason"] 
}'; 
PRINT JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(@json, '$.IsVinyl', CAST(0 AS BIT)), '$.Recorded', 'Abbey Road Studios'), 'append $.Members', 'Barrett'); 

Here is the output:

{
"Album":"Wish You Were Here",
"Year":1975,
"IsVinyl":false,
"Members":["Gilmour","Waters","Wright","Mason","Barrett"],
"Recorded":"Abbey Road Studios"
}