I want to delete the "AttributeName" : "Manufacturer" from the below json in SQL Server 2016:
declare @json nvarchar(max) = '[{"Type":"G","GroupBy":[],
"Attributes":[{"AttributeName":"Class Designation / Compressive Strength"},{"AttributeName":"Size"},{"AttributeName":"Manufacturer"}]}]'
This is the query I tried which is not working
select JSON_MODIFY((
select JSON_Query(@json, '$[0].Attributes') as res),'$.AttributeName.Manufacturer', null)
Here is the working solution using the for json and open json. The point is to:
Identify the item you wish to delete and replace it with NULL. This is done by JSON_MODIFY(@json,'$[0].Attributes[2]', null). We're simply saying, take the 2nd element in Attributes and replace it by null
Convert this array to a row set. We need to somehow get rid of this null element and that's something we can filter easily in SQL by where [value] is not null
Assemble it all back to original JSON. That's done by FOR JSON AUTO
Please bear in mind one important aspect of such JSON data transformations:
JSON is designed for information exchange or eventually to store the information. But you should avoid more complicated data manipulation on SQL level.
Anyway, solution here:
declare @json nvarchar(max) = '[{"Type": "G","GroupBy": [],"Attributes": [{"AttributeName": "Class Designation / Compressive Strength"}, {"AttributeName": "Size"}, {"AttributeName": "Manufacturer"}]}]';
with src as
(
SELECT * FROM OPENJSON(
JSON_Query(
JSON_MODIFY(@json,'$[0].Attributes[2]', null) , '$[0].Attributes'))
)
select JSON_MODIFY(@json,'$[0].Attributes', (
select JSON_VALUE([value], '$.AttributeName') as [AttributeName] from src
where [value] is not null
FOR JSON AUTO
))
[2] index by subselect.. a query that will find the element index by its attributename value.