如何格式化array_agg聚合结果以输出指定结构的JSON对象数组？

直接使用array_agg(u)聚合用户表整行数据时，PostgreSQL默认会将复合行类型转换为括号拼接的无格式文本，因此会返回"(concatenated data f.e: userIdusernameemailpassword)"这类非结构化结果。要得到由独立键值对对象构成的标准数组，使用JSON聚合函数自定义返回字段即可，不需要做额外的字符串拼接处理。

正确SQL写法 #

推荐使用jsonb_agg搭配jsonb_build_object，手动指定需要返回的字段（这种写法可以自动过滤掉密码等不需要返回的敏感字段，安全性更高）：

select 
    w.workspace_id,
    w.owner_id,
    jsonb_agg(
        jsonb_build_object(
            'userId', u.user_id,
            'username', u.username
            -- 如果需要返回邮箱等其他字段，按照 '键名', 表字段 的格式往下追加即可，例如 'email', u.email
        )
    ) as user_list
from workspaces w
inner join workspace_relations wr on wr.workspace_id = w.workspace_id
inner join users u on u.user_id = wr.addressee_id
group by w.workspace_id, w.owner_id

返回效果 #

上述语句执行后，最后一列会直接返回标准JSON格式的数组，结构和预期完全一致：

[{"userId": "test", "username": "test"}, {"userId": "test2", "username": "test2"}]

注意事项 #

• 不要直接使用array_agg(row_to_json(u))这类写法聚合整行用户数据，会将密码哈希等敏感字段一并返回，存在数据泄露风险
• 如果使用的是不支持jsonb的旧版PostgreSQL，可以将jsonb_agg、jsonb_build_object替换为json_agg、json_build_object，返回的JSON数组格式完全一致

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