Update an existing JSON value inside a JSON Array in SQL
15,154
Solution 1
You could use CTE
to parse it and combine path in UPDATE
part:
WITH cte AS (
SELECT *
FROM t
CROSS APPLY OPENJSON(c) s
WHERE i = 1
AND JSON_VALUE(s.value, '$.id')=102
)
UPDATE cte
SET c = JSON_MODIFY(c, '$[' + cte.[key] + '].name', 'Joe');
Output:
-- Before
[{"id":"101","name":"John"}, {"id":"102","name":"peter"}]
-- After
[{"id":"101","name":"John"}, {"id":"102","name":"Joe"}]
This will work on SQL Server 2017+ or SQL Azure DB otherwise you will get error. More info about path literal
Solution 2
Updating JSON Data (Postgresql)
If the column in your table contains json data and you want to update this data, you can use the following structure:
UPDATE table_name SET column_name = '{"key" : value}'::jsonb
WHERE column_name::jsonb @> '{“new_key” : new_value}'::jsonb;
Note: Usually @> is used as the "contains" operator.
Author by
Santanu
Updated on June 18, 2022Comments
-
Santanu about 2 years
I want to update an existing JSON value inside a JSON array. I can append a new JSON to the JSON array using
JSON_MODIFY
. Suppose i have a JSON like :[{"id":"101","name":"John"}, {"id":"102","name":"peter"}]
But i want to update only the json with id=102.
Is it possible using
JSON_MODIFY()
?EDIT:
Actual data
{"Details":{"SId":{"Type":"string","Value":"1234"},"BookList":{"Type":"List","Value":[{"id": "101", "name": "Book1"},{"id": "102", "name": "Book2"}]},"SName":{"Type":"string","Value":"john"}}}
-
Santanu about 6 yearsI have given my actual json format.How can i use cte and cross aply there?
-
Jishad almost 6 yearsCan you please suggest a solution that works in versions below SQL Server 2017? i'm getting
The argument 2 of the "JSON_MODIFY" must be a string literal.
error. -
JohnnyFun over 5 years@Jishad, you could generate your update statements. Something like
select 'update [yourtable] set [jsoncol] = json_modify([jsoncol], ''$[' + [key] + '].name'', ''Joe'') where id = ' + [yourtable].id from [yourtable] cross apply openjson([jsoncol])
and then execute them all with sp_executesql (of course, being wary of sql injection) -
jolySoft almost 3 yearsWouldn't believe how hard I've hunted for this answer, much love