Updating sql database from an array
The "hacky" way works because we're working with integer values and those are easily cleaned.
0)) INSERT INTO #t VALUES (1, '[, ]') INSERT INTO #t VALUES (2,'[, ]') SELECT * FROM #t SELECT * FROM #t CROSS APPLY OPENJSON(Json Column) s WITH cte AS ( SELECT * FROM #t CROSS APPLY OPENJSON(Json Column) s ) UPDATE cte SET Json Column = JSON_MODIFY(Json Column, '$[' cte.[key] '].name', 'Joe') SELECT * FROM #t -- DROP TABLE #t 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.
The modified documents can be returned or processed further. JSON Merge Patch is suitable for updating JSON documents that primarily use document that specifies the changes to make, and it returns a copy of the source document updated (patched).
The patch document specifies the differences between the source and the result documents. When merging two objects, one from source and one from patch, a member with a field that is in one object but not in the other is kept in the result.
SELECT * into #t2 FROM #t CROSS APPLY OPENJSON(Json Column) s select *, json_value (value, '$.name') from #t2 UPDATE #t2 SET value = JSON_MODIFY(value, '$.name', 'Joe') select t.
I noticed that only the last array value is written to all table fields.create table REGIONS (REGION_NAME varchar(32) NOT NULL, ZIPS varchar32 ARRAY NOT NULL, PRIMARY KEY (REGION_NAME)); insert into REGIONS values( 'Northwest', ''); insert into REGIONS values( 'Southwest', ''); Prepared Statement pstmt = con.prepare Statement( "insert into REGIONS (region_name, zips) " "VALUES (? )"); String(1, "North East"); Array(2, north East Region); pstmt.execute Update(); objects remain valid for at least the duration of the transaction in which they are created.