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 BIT)); 

Strict mode always expects the property specified with the JSON path expression to exist. If it does not exist, it returns the following error message:

Msg 13608, Level 16, State 2, Line 34
Property cannot be found on the specified JSON path.

Be aware when you add a value that it is already JSON formatted. In the next example, assume you want to add a new property named Members and you have already prepared the whole JSON array:

DECLARE @json NVARCHAR(MAX) = N'{ 
"Album":"Wish You Were Here", 
"Year":1975, 
"IsVinyl":true 
}'; 
DECLARE @members NVARCHAR(500) = N'["Gilmour","Waters","Wright","Mason"]'; 
PRINT JSON_MODIFY(@json, '$.Members', @members); 

A new Members property has been added to the input JSON data, but our JSON conform value has been interpreted as text and therefore all special characters are escaped. Here is the modified input string:

{
"Album":"Wish You Were Here",
"Year":1975,
"IsVinyl":true,
"Members":"["Gilmour","Waters","Wright","Mason"]"
}

To avoid the escaping of JSON conforming text, you need to tell the function that the text is already JSON and escaping should not be performed. You can achieve this by wrapping the new value with the JSON_QUERY function:

DECLARE @json NVARCHAR(MAX) = N'{ 
"Album":"Wish You Were Here", 
"Year":1975, 
"IsVinyl":true 
}'; 
DECLARE @members NVARCHAR(500) = N'["Gilmour","Waters","Wright","Mason"]'; 
PRINT JSON_MODIFY(@json, '$.Members', JSON_QUERY(@members)); 

As mentioned in the previous section, the JSON_QUERY function returns JSON conforming text and now SQL Server knows that escaping is not required. Here is the expected result:

{
"Album":"Wish You Were Here",
"Year":1975,
"IsVinyl":true,
"Members":["Gilmour","Waters","Wright","Mason"]
}

This is a drawback of the missing JSON data type. If you had it, it wouldn't be necessary to use JSON_QUERY and SQL Server would distinguish between JSON and string.