如何在PostgreSQL中为JSONB列嵌套数组添加键值并转换结构

API 探路者

2026-8-26

PostgreSQL JSONB 数组转换查询方案

假设你的表名为users，存储JSON数据的jsonb列名为data，可以通过以下两种方式实现需求：

1. 查询时动态转换（不修改原数据）

适合仅临时查看转换后结果的场景：

SELECT 
  jsonb_set(
    data,
    '{Details,Images}',
    COALESCE(
      (SELECT jsonb_agg(
        jsonb_build_object(
          'Link', img,
          'UploadedBy', null,
          'Size', null
        )
      ) FROM jsonb_array_elements(data->'Details'->'Images') AS img),
      '[]'::jsonb
    )
  ) AS transformed_data
FROM users;

关键逻辑说明：

jsonb_array_elements(data->'Details'->'Images')：把Images数组拆分为单行的字符串值
jsonb_build_object('Link', img, ...)：将每个字符串包装成指定结构的JSON对象
jsonb_agg(...)：把转换后的单个对象重新聚合为数组
COALESCE(..., '[]'::jsonb)：处理Images为空数组的情况，避免聚合后返回null
jsonb_set(...)：将原JSON中的Details.Images字段替换为转换后的新数组

2. 永久更新原表数据

如果需要把转换结果持久化到表中，可执行UPDATE语句：

UPDATE users
SET data = jsonb_set(
  data,
  '{Details,Images}',
  COALESCE(
    (SELECT jsonb_agg(
      jsonb_build_object(
        'Link', img,
        'UploadedBy', null,
        'Size', null
      )
    ) FROM jsonb_array_elements(data->'Details'->'Images') AS img),
    '[]'::jsonb
  )
);