PostgreSQL中更新jsonb字段子集元素的实现方法问询

我有一张名为Table的表，其中包含一个jsonb类型的Column字段，该字段的示例数据如下：

{
   "Id": 1,
   "Elements": 
   [
      {
         "ElementId": 1,
         "IsEnabled": true
      },
      {
         "ElementId": 2,
         "IsEnabled": false
      }
   ]
}

需求是通过传入指定ElementId，启用对应元素（这些ID存在间隔，无法通过索引直接定位）。

编辑1
我已通过以下查询语句得到修改后的元素数组，但无法保留JSON的外层结构（即Id和Elements节点）：

select jsonb_path_query_array(data, '$.Elements[*] ?(@.ElementId<>1)') || '{"ElementId": 1, "IsEnabled": false, "ProductName": "Name"}'::jsonb
from table

编辑2
已通过jsonb_set函数实现仅更新Elements部分，同时完整保留外层JSON结构，问题解决。

具体实现示例 #

以下是使用jsonb_set完成指定元素更新的SQL语句：

UPDATE "Table"
SET "Column" = jsonb_set(
    "Column",
    '{Elements}',
    (
        SELECT jsonb_agg(
            CASE WHEN elem->>'ElementId' = '1'  -- 替换为目标ElementId
                 THEN elem || '{"IsEnabled": true}'::jsonb  -- 更新IsEnabled为true，可按需添加其他字段
                 ELSE elem
            END
        )
        FROM jsonb_array_elements("Column"->'Elements') AS elem
    )
)
WHERE "Column"->>'Id' = '1'; -- 根据外层Id筛选需要更新的行，可按需调整条件

语句说明 #

1. jsonb_array_elements("Column"->'Elements')：将Elements数组拆分为独立的JSON元素
2. CASE语句：匹配目标ElementId，更新其IsEnabled状态，其他元素保持不变
3. jsonb_agg：将处理后的单个元素重新聚合为JSON数组
4. jsonb_set：将新生成的数组替换回原JSON字段的Elements节点，完整保留外层结构

内容的提问来源于stack exchange，提问作者deha