PostgreSQL查询需求：获取reports表全字段并附加对应subjects表的device_id数组

没问题，这需求在PostgreSQL里用LATERAL结合数组函数就能轻松实现，不用改任何表结构！

核心思路是把reports.subject_ids数组拆成单个ID，关联subjects表拿到对应的device_id，再把这些device_id重新聚合成数组，最后和原reports表的所有字段一起返回。

给你写好完整的查询语句，带注释说明：

SELECT 
    r.*,  -- 返回reports表的所有字段
    -- 聚合对应device_id成数组，用空数组替代null，同时保持和subject_ids一致的顺序
    COALESCE(array_agg(s.device_id ORDER BY sub.id), '{}'::varchar[]) AS device_ids
FROM 
    reports r
-- 把当前行的subject_ids拆成单行记录
LEFT JOIN LATERAL 
    unnest(r.subject_ids) AS sub(id) ON true
-- 关联subjects表，匹配对应的device_id
LEFT JOIN 
    subjects s ON sub.id = s.id
-- 按reports的主键分组，确保每个report只返回一行
GROUP BY 
    r.id  -- 这里假设reports.id是主键，如果不是，需要GROUP BY reports的所有字段
ORDER BY 
    r.id;

关键细节说明： #

• LEFT JOIN LATERAL unnest(...)：LATERAL关键字允许我们在JOIN中引用当前行的字段（这里就是r.subject_ids），把每个数组拆成多行，这样才能和subjects表做关联。用LEFT JOIN是为了保证即使subject_ids里的ID在subjects表中不存在，对应的report也不会被过滤掉。
• array_agg(s.device_id ORDER BY sub.id)：把关联到的device_id重新聚合成数组，ORDER BY sub.id是为了让device_id的顺序和原subject_ids数组的顺序一致。
• COALESCE(..., '{}'::varchar[])：如果某个report的subject_ids是空数组，或者所有ID都没匹配到device_id，会返回空数组而不是null，结果更规整。

如果你的subject_ids数组里有重复的ID，而你希望device_id数组里去重，可以把array_agg(s.device_id)改成array_agg(DISTINCT s.device_id)。

内容的提问来源于stack exchange，提问作者No Such Agency