Delete an object from nested array in openjson SQL Server 2016

Question

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) 

Answer 1

Here is the working solution using the for json and open json. The point is to:

1. 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

2. 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

3. 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 
))