sql - 在SQL中更新JSON数组中的现有JSON值

Question

我想更新JSON数组中的现有JSON值。我可以使用JSON_MODIFY将新的JSON附加到JSON数组。假设我有一个JSON，例如:

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

但是我只想更新id = 102的json。

可以使用 JSON_MODIFY()吗？

编辑:

实际数据

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

您可以使用CTE对其进行解析，并在UPDATE部分中合并路径:

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