Update an existing JSON value inside a JSON Array in SQL
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 cteSET 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
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.