PostgreSQL中如何从JSONB数组提取指定属性并生成格式化查询结果？

问题说明 #

现有PostgreSQL表包含id、name列，以及存储对象数组的jsonb类型列services，数组示例：

[{"id": "1", "service_id": "4100002"}, {"id": "2", "service_id": "4100003"}, {"id": "3", "service_id": "410004"}]

需要编写查询，保留id、name列，同时将services列转换为提取出的service_id以逗号分隔的字符串，示例结果：

解决方案 #

基础查询语句 #

SELECT 
    id,
    name,
    string_agg(s->>'service_id', ', ') AS services
FROM 
    your_table_name,
    jsonb_array_elements(services) AS s
GROUP BY 
    id, name;

处理空值/空数组的增强版 #

如果需要处理services为null或空数组的场景，避免结果中出现null，可以用LEFT JOIN LATERAL配合COALESCE：

SELECT 
    id,
    name,
    COALESCE(string_agg(s->>'service_id', ', '), '') AS services
FROM 
    your_table_name
LEFT JOIN LATERAL jsonb_array_elements(services) AS s ON true
GROUP BY 
    id, name;

语句说明 #

• jsonb_array_elements(services)：将services列的JSONB数组展开为多行记录，每行对应数组中的一个对象，别名s。
• s->>'service_id'：提取对象s中的service_id字段，转为文本类型。
• string_agg(..., ', ')：将同一分组下的所有service_id用逗号加空格拼接成字符串。
• LEFT JOIN LATERAL + COALESCE：确保当services为空时，返回空字符串而非null。

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