Question 111006 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

how to delete properties in array using JSON_MODIFY in sql server

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 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

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search