how to delete properties in array using JSON_MODIFY in sql server-CodePudding

I have a complex json stored in a varchar(max) column. I have an array of strings in the json.

myArray: ['one', 'two', 'three', 'four']

I am running the following update query to delete property two from the array above using JSON_MODIFY.

UPDATE MYTABLE SET MYJSONCOL = JSON_MODIFY(MYJSONCOL, '$.section[0].subsection[7].myArray[1]', null) WHERE MYID = 'ABCD';

However, the query results in:

myArray: ['one', null, 'three', 'four']

But I want it to be:

myArray: ['one', 'three', 'four']

How do I achieve this?

I tried adding lax in front of the path. But I got the same result i.e. null instead of property being completely removed.

UPDATE MYTABLE SET MYJSONCOL = JSON_MODIFY(MYJSONCOL, 'lax $.section[0].subsection[7].myArray[1]', null) WHERE MYID = 'ABCD';

How can I completely remove a property from a json array using JSON_MODIFY.

CodePudding user response：

As far as I am aware JSON_MODIFY() does not support removing items from an array. One workaround is to expand the array using OPENJSON(), remove the items you need to, then rebuild it using STRING_AGG() and then replace your full array with the newly create one.

It feels a bit hacky, but it does work:

IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL
    DROP TABLE #T;

CREATE TABLE #T (MyJSONCol VARCHAR(MAX));
INSERT #T (MyJSONCol)
VALUES ('{ "SomeProperty": "SomeValue", "myArray": ["one", "two", "three", "four"] }');


SELECT  *
FROM    #T AS t;

UPDATE  t
SET     t.MyJSONCol = JSON_MODIFY(t.MyJSONCol, '$.myArray', JSON_QUERY(oj.MyNewArray))
FROM    #T AS t
        CROSS APPLY 
        (   SELECT  CONCAT('[', STRING_AGG(CONCAT('"', oj.Value, '"'), ','), ']')
            FROM    OPENJSON(t.MyJSONCol, '$.myArray') AS oj
            WHERE   oj.[Key] <> 1
        ) AS oj (MyNewArray);

SELECT  *
FROM    #T AS t;