Postgres执行SELECT时如何从JSONB字段返回JSON对象列表而非ID列表

实现方案（PostgreSQL环境） #

你可以通过拆解JSONB数组、关联用户表、再聚合为JSON数组的逻辑实现需求，参考查询语句如下：

SELECT 
    s.id,
    s.simulation_name,
    s.datetime,
    JSONB_AGG(TO_JSONB(u) ORDER BY u.id) AS user_list
FROM simulations s
-- 拆解user_list为单个用户ID
LEFT JOIN LATERAL JSONB_ARRAY_ELEMENTS(s.user_list) AS uid(uid) ON TRUE
-- 关联用户表取详情
LEFT JOIN users u ON u.id = uid.uid::bigint
-- 保留原有筛选逻辑：仅保留关联了USA用户的模拟任务
WHERE EXISTS (
    SELECT 1 
    FROM JSONB_ARRAY_ELEMENTS(s.user_list) AS t_uid
    JOIN users t_u ON t_u.id = t_uid.uid::bigint
    WHERE t_u.country = 'USA'
)
-- 按模拟任务维度分组，聚合用户详情为JSON数组
GROUP BY s.id, s.simulation_name, s.datetime;

逻辑说明 #

1. 用LATERAL JSONB_ARRAY_ELEMENTS将每条模拟任务的user_listID数组拆分为多行单个ID，实现一对多展开
2. 关联users表拿到对应用户的全量字段，用TO_JSONB将每行用户数据转为JSON对象
3. 用JSONB_AGG将同一模拟任务下的多个用户JSON对象聚合为数组，得到你需要的user_list格式
4. 原有筛选逻辑替换为了EXISTS写法，相比原始的数组交集写法性能更好，尤其是在users表的country字段加了索引、simulations表数据量不大的情况下优化效果更明显

提示：如果user_list中存在已删除的用户ID，想要过滤掉无效ID只保留存在的用户详情，把上述语句中的两个LEFT JOIN改为INNER JOIN即可。

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