PostgreSQL中如何将jsonb数组转为以年份为键的jsonb对象

问题原因 #

你原有语句的错误在于 data -> 'indicators' 是数组类型，PostgreSQL 无法直接从数组中提取 year 字段，必须先将数组展开为单个元素，再聚合为目标格式的对象。

正确实现语句 #

UPDATE site
SET data = (
    data
    -- 新增转换后的 indicatorsByYear 字段
    || jsonb_build_object(
        'indicatorsByYear',
        (
            SELECT jsonb_object_agg(
                (elem ->> 'year')::TEXT, -- 年份转字符串作为键
                elem -> 'indicatorsByYear' -- 对应年份的指标数组作为值
            )
            FROM jsonb_array_elements(data -> 'indicators') AS elem
        )
    )
-- 删除原有 indicators 数组字段
) - 'indicators'
-- 仅更新包含 indicators 字段的行，避免无效更新
WHERE data ? 'indicators';

提前验证方法 #

执行UPDATE前可以先运行SELECT语句预览转换结果，确认符合预期后再执行更新：

SELECT
  -- 替换为你的主键字段名
  id,
  data AS old_data,
  (
    data
    || jsonb_build_object(
        'indicatorsByYear',
        (SELECT jsonb_object_agg((elem ->> 'year')::TEXT, elem -> 'indicatorsByYear') FROM jsonb_array_elements(data -> 'indicators') AS elem)
    )
  ) - 'indicators' AS new_data
FROM site
WHERE data ? 'indicators'
LIMIT 10;

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