How can I remove JSON property stored in a SQL Server column?

Question

I have a task to remove a JSON property saved in a SQL Server database table's column. Here is the structure of my table

OrderId   Name   JSON

In the JSON column I have this JSON data:

{ 
    "Property1" :"",
    "Property2" :"",
    "metadata-department": "A",
    "metadata-group": "B"
}

I have over 500 records that have this json value.

Can I update all the records to remove metadata-department?

Answer 1

This question is old but I thought I'd post another solution for those who may end up here via search engine.

In SQL Server 2016 or SQL Azure, the following works:

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}'

PRINT JSON_MODIFY(@info, '$.name', NULL)

-- Result: {"skills":["C#","SQL"]}

Source: https://msdn.microsoft.com/en-us/library/dn921892.aspx