PostgreSQL更新JSONB列内指定条件数组元素字段值问题

问题场景 #

• 表名：layout
• 字段：configurations，类型为jsonb，存储JSON数组结构数据
• 目标记录：id=1，初始存储内容如下：

[
  {"data": {"x": 664, "y": 176 }, "layout_id": "1", "layout_name": "Corner"},
  {"data": {"x": 334, "y": 268 }, "layout_id": "2", "layout_name": "Outside"}
]

需求 #

匹配数组内layout_id值为'1'的元素，将其layout_name字段从"Corner"更新为"Ground Floor"，保留其他元素和数组结构不变。

原有语句失效原因 #

之前使用的更新语句存在两个核心问题：

1. 子查询仅返回了匹配到的单个数组元素，jsonb_set修改后直接赋值给字段，会把原本的数组覆盖为单个JSON对象，丢失其他元素和数组结构
2. jsonb_set的第三个参数要求传入jsonb类型，字符串值需要额外包裹双引号，原写法传'Ground Floor'会触发类型错误

原错误语句参考：

UPDATE layout set configurations = jsonb_set(x1.config, '{layout_name}', 
   'Ground Floor') 
    FROM 
     (select * FROM 
        (                                                                                       
           SELECT jsonb_array_elements(d.configurations) AS config
           FROM 
           layout d
           WHERE jsonb_typeof(d.configurations) = 'array') x
        where x.config ->> 'layout_id' = '1'
     ) x1 where id= 1;

正确实现 #

写法1：PostgreSQL 12+ 高性能版本（推荐） #

通过generate_subscripts定位匹配元素的数组下标，直接原地修改，不需要重组整个数组，性能更好：

UPDATE layout
SET configurations = jsonb_set(
  configurations,
  -- 构造修改路径：[匹配下标, 字段名]
  ARRAY[
    (
      SELECT idx::text
      FROM generate_subscripts(configurations, 1) AS idx
      WHERE configurations->idx->>'layout_id' = '1'
    ),
    'layout_name'
  ],
  '"Ground Floor"' -- 注意字符串值需要包裹双引号，作为jsonb字符串传入
)
WHERE id = 1;

写法2：全版本通用写法 #

通过jsonb_array_elements拆分数组，判断修改对应元素后再聚合重组为数组，兼容所有PostgreSQL版本：

UPDATE layout
SET configurations = (
  SELECT jsonb_agg(
    CASE
      -- 匹配到目标元素时，合并更新layout_name字段
      WHEN elem->>'layout_id' = '1' 
      THEN elem || '{"layout_name": "Ground Floor"}'::jsonb
      -- 非目标元素保持原值
      ELSE elem
    END
  )
  FROM jsonb_array_elements(configurations) AS elem
)
WHERE id = 1;

扩展操作：删除数组内指定元素 #

如果后续需要根据layout_id删除对应数组元素，可使用如下语句：

UPDATE layout
SET configurations = (
  SELECT jsonb_agg(elem)
  FROM jsonb_array_elements(configurations) AS elem
  -- 过滤掉要删除的元素即可
  WHERE elem->>'layout_id' != '1'
)
WHERE id = 1;

操作提示：所有写入jsonb字段的字符串类型值，都需要用双引号包裹为合法JSON格式（例如'"文本值"'），否则会抛出invalid input syntax for type jsonb错误。

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