SQL Server 2017 Developer???s Guide

Adding a new JSON property

In the following code example, you add a new property named IsVinyl with the value true:

DECLARE @json NVARCHAR(MAX) = N'{ 
"Album":"Wish You Were Here", 
"Year":1975 
}'; 
PRINT JSON_MODIFY(@json, '$.IsVinyl', CAST(1 AS BIT)); 

You need to cast the value explicitly to the BIT data type; otherwise it will be surrounded by double quotes and interpreted as a string. Here is the result of the modification:

{
"Album":"Wish You Were Here",
"Year":1975,
"IsVinyl":true
}

Note that the JSON path expression is in default lax mode. By specifying strict mode, the function will return an error:

DECLARE @json NVARCHAR(MAX) = N'{ "Album":"Wish You Were Here", "Year":1975 }'; PRINT JSON_MODIFY(@json, 'strict $.IsVinyl', CAST(1 AS ...