你可以使用CTE来解析它，并在UPDATE部分中合并path：

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

输出：

-- Before
[{"id":"101","name":"John"}, {"id":"102","name":"peter"}]

-- After
[{"id":"101","name":"John"}, {"id":"102","name":"Joe"}]

这将适用于SQL Server 2017+或SQL Azure DB，否则您将收到错误。More info about path literal

更新JSON数据（PostgreSQL）
如果表中的列包含json数据，并且您希望更新此数据，则可以使用以下结构：

UPDATE table_name SET column_name = '{"key" : value}'::jsonb 
WHERE column_name::jsonb @> '{“new_key” : new_value}'::jsonb;

注意：通常@>用作“contains”操作符。

最好的方法是生成如下语句：

• 这样就不会出现“无法解决Latin1_General_BIN和SQL_Latin1_General_CP1_CI_AS之间的排序规则冲突”的错误
• 此外，您不会得到此错误“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;

这样做：

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