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?
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
null instead of removing it.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/
In MYSQL the function is 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
