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

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.

The best way is to generate the statement like this:

• In this way you, won't get the error of "Cannot resolve the collation conflict between Latin1_General_BIN and SQL_Latin1_General_CP1_CI_AS"
• Also, you won't get this error "The argument 2 of the JSON_MODIFY must be a string literal"

  WITH cte AS (
    SELECT 
    t.PrimaryKey,
    JSON_VALUE([value], '$.id') as id,
    t.JsonColumn,
    o.*
    ,('UPDATE MyTable set JsonColumn = JSON_MODIFY(JsonColumn, ''$['+[key]+'].id'', ''NewVALUE'') WHERE PrimaryKey = '''+t.PrimaryKey COLLATE SQL_Latin1_General_CP1_CI_AS+ '''') as statement
    FROM MyTable t
     CROSS APPLY OPENJSON(JSON_QUERY(JsonColumn, '$')) o WHERE  JSON_VALUE(o.value, '$.Id')= 1
     )
  select * from cte; 
  

Do like this:

Declare @Input nvarchar(max) = N'[{"id":"101","name":"John"}, {"id":"102","name":"peter"}]';
Declare @UpdatedItem nvarchar(max) = N'{"id":"102","name":"peter1"}'

SET @Input = JSON_MODIFY(@Input, CONCAT('$[', (SELECT [Key] FROM OPENJSON(@Input) as I WHERE JSON_VALUE(I.Value,'$.id') = "102"), ']'), JSON_QUERY(@UpdatedItem));