如何在PostgreSQL查询中获取对象数组格式的结果？

问题场景 #

当前执行的查询语句（注：原语句存在语法缺失，已补全关联逻辑）：

SELECT
jsonb_build_object('platform_id',pl.id,'platform_Name',pl."name") as "platform"
FROM "platforms" as pl
JOIN user_platforms up ON pl.id = up."platform_id"
JOIN users usrs ON usrs.id = up.user_id
WHERE usrs.id = ${userid}

得到的JSON结果：

"platform": {
    "platform_id": 5,
    "platform_Name": "Snapchat"
}

期望platform字段返回对象数组格式：

"platform": [{
    "platform_id": 5,
    "platform_Name": "Snapchat"
}]

修改方案 #

根据实际场景选择以下两种方式：

1. 单条结果包装为数组 #

如果用户仅关联单个平台，需要将单个JSON对象包装成数组，使用jsonb_build_array函数包裹jsonb_build_object的结果：

SELECT
jsonb_build_array(jsonb_build_object('platform_id',pl.id,'platform_Name',pl."name")) as "platform"
FROM "platforms" as pl
JOIN user_platforms up ON pl.id = up."platform_id"
JOIN users usrs ON usrs.id = up.user_id
WHERE usrs.id = ${userid}

2. 多条结果聚合为数组 #

如果用户关联多个平台，需要将所有关联的平台对象聚合为一个数组，使用jsonb_agg聚合函数，并配合GROUP BY分组：

SELECT
jsonb_agg(jsonb_build_object('platform_id',pl.id,'platform_Name',pl."name")) as "platform"
FROM "platforms" as pl
JOIN user_platforms up ON pl.id = up."platform_id"
JOIN users usrs ON usrs.id = up.user_id
WHERE usrs.id = ${userid}
GROUP BY usrs.id

补充说明 #

原查询语句缺少JOIN关键字关联user_platforms和users表，上述修改已补全必要的关联逻辑，否则语句无法正常执行。

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