arrays - 将键值插入到 JSON 数组的所有对象中

Question

我在数据库中有 JSON 数组值列:

[
   {
      "id": 1,
      "name": "One"
   },
   {
      "id": 2,
      "name": "Two"
   }
]

我想在每个对象中添加一个新的键值。预期结果是:

[
   {
      "id": 1,
      "name": "One",
      "active": "1"
   },
   {
      "id": 2,
      "name": "Two",
      "active": "1"
   }
]

通过使用 JSON_MODIFY我可以将一个新的键值添加到一个对象中，如下所示:

UPDATE MyTable
   SET JsonValueColumn=JSON_MODIFY(JsonValueColumn,'$[1].active','1')
 WHERE Id = 'id'

但是我需要在 JSON 数组中的所有对象中添加一个新的键值。我可以选择在 json 对象中迭代，添加新的键值，再次组合它们并用新值更新 JsonValueColumn。
但我正在寻找是否有更简单的方法来实现这一目标。就像是:

UPDATE MyTable
   SET JsonValueColumn=JSON_MODIFY(JsonValueColumn,'$[*].active','1')
 WHERE Id = 'id'

Answer 1

这是我目前找到的最简单的方法:

UPDATE MyTable
SET JsonValueColumn= '[' + (
        SELECT
            STRING_AGG(JSON_MODIFY([value],'$.active', '1'), ',') WITHIN GROUP (ORDER BY CAST([key] AS int))
        FROM
            OPENJSON(JsonValueColumn)
    ) + ']' 

发件人: https://stackoverflow.com/a/62648430/2794280