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

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?

sql-serverjson


Solution 1:[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

Solution 2:[2]

In my SQL Server environment, I have installed CLR RegEx Replace functions and using these, I've achieved what you wanted:

DECLARE @Text NVARCHAR(MAX) = '{ 
    "Property1" :"",
    "Property2" :"",
    "metadata-department": "A",
    "metadata-group": "B"
}';

PRINT dbo.RegExReplace(@Text, '"metadata-department": "[A-Za-z0-z]",\r\n\s+', '');

Result:

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

CLR Source: https://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/

Solution 3:[3]

in MYSQL it's JSON_REMOVE()

UPDATE orders SET JSON=JSON_REMOVE(JSON, $."metadata-department")

https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-remove

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Dennis W
Solution 2 Evaldas Buinauskas
Solution 3

Related Questions