如何在PostgreSQL中将jsonb值提取映射为新数组

实现方案 #

你可以通过「拆分数组→提取字段→聚合结果」的逻辑实现需求，以下是不同输出格式的对应写法：

1. 输出PostgreSQL原生文本数组（结果为{foo,fuu}）

SELECT array_agg(elem->>'id') AS result
FROM json_array_elements(
    '{"a": {"b":{"c": [{"id":"foo"},{"id":"fuu"}]}}}'::json #> '{a,b,c}'
) AS elem;

2. 输出标准JSON格式数组（结果为["foo", "fuu"]）

SELECT json_agg(elem->'id') AS result
FROM json_array_elements(
    '{"a": {"b":{"c": [{"id":"foo"},{"id":"fuu"}]}}}'::json #> '{a,b,c}'
) AS elem;

补充说明 #

• json_array_elements：将JSON数组拆分为多行，每行对应数组内的一个JSON元素
• ->>：提取JSON字段的纯文本值，自动移除字符串两侧的引号
• ->：提取JSON字段的原始JSON类型值
• array_agg：将多行文本值聚合为PostgreSQL原生数组
• json_agg：将多行JSON值聚合为JSON类型数组

如果你使用的是jsonb类型，只需要把上述代码中的json替换为jsonb、json_array_elements替换为jsonb_array_elements即可，逻辑完全一致。

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