PostgreSQL 12.10中更新jsonb列的嵌套数组元素

问题场景 #

participant表的activities列为JSONB类型，结构示例如下：

{
  "enrolled": [
    {
      "sport": {
        "id": 1,
        "name": "soccer"
      }
    },
    {
      "sport": {
        "id": 2,
        "name": "hockey"
      }
    }
  ]
}

需求 #

为enrolled数组中sport.id等于1的元素添加registered字段，字段结构为：

{
  "day": 12,
  "month": "Aug"
}

期望结果 #

{
  "enrolled": [
    {
      "sport": {
        "id": 1,
        "name": "soccer"
      },
      "registered": {
        "day": 12,
        "month": "Aug"
      }
    },
    {
      "sport": {
        "id": 2,
        "name": "hockey"
      }
    }
  ]
}

原错误尝试 #

以下语句会直接替换整个activities列，不符合需求：

UPDATE participant
SET activities = (
    SELECT jsonb_agg(jsonb_set(sports, '{registered}', '{"day": 12, "month": "Aug"}', true))
    FROM jsonb_array_elements(activities::jsonb -> 'enrolled') sports
)
WHERE activities::jsonb -> 'enrolled' @? '$.sport.id ? (@ == 1)';

正确解决方案 #

通过jsonb_set结合子查询，仅更新enrolled数组的目标元素，保留原JSON的其他结构：

UPDATE participant
SET activities = jsonb_set(
    activities,
    '{enrolled}',
    (
        SELECT jsonb_agg(
            CASE
                WHEN (elem -> 'sport' ->> 'id') = '1'
                THEN elem || '{"registered": {"day": 12, "month": "Aug"}}'::jsonb
                ELSE elem
            END
        )
        FROM jsonb_array_elements(activities -> 'enrolled') AS elem
    )
)
WHERE activities @? '$.enrolled[*].sport.id ? (@ == 1)';

关键说明 #

1. 用jsonb_array_elements拆分enrolled数组为单个元素
2. 通过CASE判断元素的sport.id是否为1，满足条件时用||运算符合并原元素与新增的registered字段
3. 用jsonb_agg重新聚合数组
4. 最终通过jsonb_set将更新后的数组放回原JSON的enrolled路径，保留其他原有结构
5. WHERE条件使用JSON路径查询，仅匹配包含目标元素的行

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