SQL Server 2017 Developer???s Guide

Removing a JSON property

To remove a property from the input JSON string, you have to use a JSON path expression in lax mode. You will repeat the preceding code, but this time in lax mode:

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

When you observe the result of this action, you can see that the Year property does not exist anymore:

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

By taking this approach, you can remove only properties and their values. You cannot remove an array element. The following code will not remove the Waters element from the JSON array property Members