PostgreSQL查询：将names与ages数组转为jsonb对象数组

需求说明 #

现有数据表包含以下字段：

• id：int类型
• names：char varying[]类型
• ages：int[]类型

示例原始数据：

id: 1,
names: ["Dave", "Pete", "Eric"],
ages: [30, 40, 50]

需要转换为以下结果，其中people为jsonb类型的数组：

id: 1,
people: [{"name": "Dave", "Age": 30}, {"name": "Pete", "Age": 40}, {"name": "Eric", "Age": 50}]

实现方法 #

方法1：使用unnest+array_agg+jsonb_build_object #

这是最直观的实现方式，通过拆分数组、构建JSON对象再聚合完成转换：

SELECT
    id,
    array_agg(jsonb_build_object('name', name, 'Age', age)) AS people
FROM
    your_table,
    unnest(names, ages) AS t(name, age)
GROUP BY
    id;

代码说明

• unnest(names, ages)：同时将names和ages按索引拆分为对应行，保证每个name与对应age匹配
• jsonb_build_object('name', name, 'Age', age)：将每行的name和age组合成单个JSONB对象
• array_agg(...)：将同一id下的所有JSONB对象聚合为JSONB数组
• GROUP BY id：确保每个id对应一条结果

方法2：使用generate_subscripts遍历数组索引 #

如果需要处理数组长度不一致的场景，可用索引遍历的方式实现：

SELECT
    id,
    array_agg(
        jsonb_build_object(
            'name', names[i],
            'Age', ages[i]
        )
    ) AS people
FROM
    your_table,
    generate_subscripts(names, 1) AS i
WHERE
    i <= array_length(ages, 1) -- 可选：避免索引超出ages数组长度报错
GROUP BY
    id;

代码说明

• generate_subscripts(names, 1)：生成names数组的索引序列（从1开始）
• names[i]和ages[i]：通过索引获取对应位置的元素
• WHERE子句：可选，用于处理两个数组长度不一致的情况，防止越界

补充说明 #

• 上述方法生成的people字段为jsonb[]类型；若需要单个jsonb类型（即数组形式的JSONB），可将array_agg替换为jsonb_agg
• 若原始数组存在NULL元素，jsonb_build_object会保留NULL值，可根据需求添加COALESCE处理空值

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