how to delete properties in array using JSON_MODIFY in sql server

Question

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.

GarethD · Accepted Answer · 2022-12-06 14:23:58Z

1

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;

Example on db<>fiddle

ADDENDUM

The above will fall down if any of the items in the array are objects, to get around this you need to check if the item is JSON first, and if so, don't concatenate the quotes to the value:

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"] }'),
('{ "SomeProperty": "SomeValue", "myArray": ["one", {"two": "SomeValue"}, {"three": "SomeValue"}, "four"] }'),
('{ "SomeProperty": "SomeValue", "myArray": [{"one": "SomeValue"}, {"two": "SomeValue"}, {"three": "SomeValue"}, {"four": ["one", "two", "three"]}] }');


SELECT  *
FROM    #T AS t;

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

SELECT  *
FROM    #T AS t;

Example on db<>fiddle

edited Dec 6, 2022 at 14:23

answered Dec 6, 2022 at 13:40

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vicky Over a year ago

Thanks for this. Is there a way to completely remove the whole array? That is '{ "SomeProperty": "SomeValue", "myArray": ["one", "two", "three", "four"] }' after array deletion should result in '{ "SomeProperty": "SomeValue" }' and not '{ "SomeProperty": "SomeValue", null }' ... The DB I want to update may not have compatibility level 150. Hence the question about completely removing the array.

Vicky Over a year ago

@gotqn please check my updated comment.

GarethD Over a year ago

@Vicky there's no issue with removing entire properties using JSON_MODIFY(), so JSON_MODIFY(t.MyJSONCol, '$.myArray', NULL) should work fine, the limitation is with removing items from an array, which is what requires the work around. If you do need a workaround that works in SQL Server 2016, you can use the old FOR XML PATH() trick to rebuild the array rather than string_agg, so you can still remove a single item even without compatibility level 150: example on db<>fiddle

gotqn · Accepted Answer · 2022-12-06 14:37:17Z

0

Try this to remove the whole array:

DECLARE @DataSource TABLE
(
    [Project] VARCHAR(MAX)
);

INSERT INTO @DataSource ([Project])
VALUES ('{"SomeProperty": "SomeValue","myArray":["one", "two", "three", "four"]}');


UPDATE @DataSource 
SET [Project] = JSON_MODIFY([Project], '$.myArray', NULL)


SELECT [Project]
FROM @DataSource

answered Dec 6, 2022 at 14:37

gotqn

43.9k47 gold badges167 silver badges256 bronze badges

1 Comment

Vicky Over a year ago

Thanks! I thought this will also result like my original question.

Collectives™ on Stack Overflow

how to delete properties in array using JSON_MODIFY in sql server

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related