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

Answer 1

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