Updating a json array IN SQL Server table

Question

I have an array of json in a SQL Server column, I am trying to update all names to 'Joe'.

I tried the below code , but it is updating only first element of the json array

CREATE TABLE #t (I INT, JsonColumn NVARCHAR(MAX) CHECK (ISJSON(JsonColumn) > 0))

INSERT INTO #t 
VALUES (1, '[{"id":"101","name":"John"}, {"id":"102","name":"peter"}]')

INSERT INTO #t VALUES (2,'[{"id":"103","name":"dave"}, {"id":"104","name":"mark"}]')


SELECT * FROM #t

SELECT * FROM #t  
CROSS APPLY OPENJSON(JsonColumn) s

WITH cte AS 
(
    SELECT *
    FROM #t
    CROSS APPLY OPENJSON(JsonColumn) s
)
UPDATE cte
SET JsonColumn = JSON_MODIFY(JsonColumn, '$[' + cte.[key] + '].name', 'Joe')

SELECT * FROM #t

--  DROP TABLE #t

It is only updating the first element of array to joe

Current result:

[{"id":"101","name":"Joe"}, {"id":"102","name":"cd"}]

[{"id":"103","name":"Joe"}, {"id":"104","name":"mark"}]

Expected

[{"id":"101","name":"Joe"}, {"id":"102","name":"Joe"}]

[{"id":"103","name":"Joe"}, {"id":"104","name":"Joe"}]

From what I can see, you would have to update each element of the JSON array separately. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 17, 2019 at 4:35
@TimBiegeleisen, Ideally, I want to update in one transaction — Prasanth
– Prasanth, Commented Jul 17, 2019 at 4:39
@PrasanthTP What is the structire of your JSON - fixed (id, name) or can be different? And what is your SQL Server version? Thanks. — Zhorov
– Zhorov, Commented Jul 17, 2019 at 6:22

Avi · Accepted Answer · 2019-07-17 05:47:30Z

2

Since you want to do in one transaction, I could not think of any other ways than to create another table and store the values into new table and use for XML path with the value. Problem is you are trying to update JSON array and I am not sure how would you update the same row twice with different value. With cross apply as you have shown it creates two rows and then only you can update it to JOE.

Your query will update name = Joe for ID = 101 for first row, and Name = Joe for ID = 102 based on value column. Since these are on two different rows you are seeing only one change in your temp table.

I created one more #temp2 table to store those values and use XML path to concatenate. The final table will be #t2 table for your expected results.

 SELECT *
      into #t2 
    FROM #t
    CROSS APPLY OPENJSON(JsonColumn) s

    select *, json_value (value, '$.name') from #t2  
UPDATE #t2
SET value =  JSON_MODIFY(value, '$.name', 'Joe')  

    select t.I , 
JSONValue  = concat('[',stuff((select   ',' + value  from #t2 t1 
where t1.i = t.i 
for XML path('')),1,1,''),']')
from #t2 t 
group by t.I

Output:

I   JSONValue
1   [{"id":"101","name":"Joe"},{"id":"102","name":"Joe"}]

Updating original table:

update   t
set t.JsonColumn =t2.JSONValue
from #t t
join  (select t.I , 
JSONValue  = concat('[',stuff((select   ',' + value  from #t2 t1 
where t1.i = t.i 
for XML path('')),1,1,''),']')
from #t2 t 
group by t.I ) t2 on t.I = t2.i

edited Jul 17, 2019 at 5:47

answered Jul 17, 2019 at 5:14

Avi

1,8453 gold badges18 silver badges32 bronze badges

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

5 Comments

Prasanth Over a year ago

Hi .. Thanks. It works if you have only one row in #t table. If you have more than one row it is not working as expected

Prasanth Over a year ago

I have updated the sql in the question . I am adding two rows into #t table now. You can see the result if you execute

Avi Over a year ago

@PrasanthTP Should your next insert have same ID ? If it is 1 I assume it should be on same row. If you insert as ID = 2, you can see it is working fine

Prasanth Over a year ago

Hi ..HOw do I update the result into original table. Like If i run SELECT * FROM #t , I want to get this result

Avi Over a year ago

@PrasanthTP I have added one update.. you can do in that way

riofly · Accepted Answer · 2022-09-28 10:33:08Z

I think that it is impossible to apply more updates to one record with one command. So you need to explode JSON array to records.

You can do this with a Temporary or Variable Table and a Cursor.

-- Declare the Variable Table
DECLARE @JsonTable TABLE (
    RecordKey UNIQUEIDENTIFIER,
    ArrayIndex INT,
    ObjKey NVARCHAR(100),
    ObjValue NVARCHAR(1000)
);

-- Fill the Variable Table
INSERT INTO @JsonTable
SELECT TB1.pk as RecordKey,
    TB1data.[key] AS ArrayIndex,
    TB1dataItem.[key] as ObjKey,
    TB1dataItem.[value] as ObjValue
FROM MyTable TB1
    CROSS APPLY OPENJSON(JSON_QUERY(TB1.data, '$.list')) TB1data
    CROSS APPLY OPENJSON(JSON_QUERY(TB1data.value, '$')) TB1dataItem
WHERE TB1dataItem.[key] = 'name'

-- Declare Cursor and relative variables
DECLARE @recordKey UNIQUEIDENTIFIER,
    @recordData NVARCHAR(MAX),
    @arrayIndex INT,
    @objKey NVARCHAR(100),
    @objValue NVARCHAR(1000);
DECLARE JsonCursor CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT * FROM @JsonTable;

-- Use Cursor to read any json array item
OPEN JsonCursor;
FETCH NEXT
    FROM JsonCursor
    INTO @recordKey, @arrayIndex, @objKey, @objValue;
WHILE @@FETCH_STATUS = 0 BEGIN

    UPDATE TB1
    SET data = JSON_MODIFY(
        data,
        '$.list[' + CAST(@arrayIndex as VARCHAR(20)) + '].name',
        'Joe'
    )
    FROM MyTable TB1
    WHERE TB1.pk = @recordKey;

    FETCH NEXT
        FROM JsonCursor
        INTO @recordKey, @arrayIndex, @objKey, @objValue;
END;

CLOSE JsonCursor;
DEALLOCATE JsonCursor;

Meyssam Toluie · Accepted Answer · 2022-09-28 10:45:48Z

0

Do you need this?

CREATE TABLE #t (
    I INT,
    JsonColumn NVARCHAR(MAX) CHECK (ISJSON(JsonColumn) > 0)
);

INSERT INTO #t
VALUES (1, '[{"id":"101","name":"John"}, {"id":"102","name":"peter"}]');

INSERT INTO #t
VALUES (2, '[{"id":"103","name":"dave"}, {"id":"104","name":"mark"}]');


SELECT CONCAT('[', STRING_AGG(JSON_MODIFY(JSON_MODIFY('{}', '$.id', j.id), '$.name', 'John'), ','), ']')
FROM #t t
     CROSS APPLY OPENJSON(JsonColumn) WITH (id INT, name sysname) j
     GROUP BY t.I

answered Sep 28, 2022 at 10:45

Meyssam Toluie

1,0817 silver badges21 bronze badges

Collectives™ on Stack Overflow

Updating a json array IN SQL Server table

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related