CODEWITHSUNDEEP
sqlsql-server
Prasanth
Prasanth

Reputation: 31

Updating a json array IN SQL Server table

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"}]

Upvotes: 3

Views: 3470

Answers (3)

Meyssam Toluie
Meyssam Toluie

Reputation: 1071

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

Upvotes: 0

riofly
riofly

Reputation: 1765

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;

Upvotes: 0

Avi
Avi

Reputation: 1845

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.

enter image description here

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

Upvotes: 2

Related Questions