Update an existing JSON value inside a JSON Array in SQL

Question

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

Answer 1

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.

Answer 2

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');

DBFiddle Demo

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