PostgreSQL关联双表，实现类似MongoDB $lookup的嵌套结果

你需要用PostgreSQL的聚合函数将关联的options行转换为JSON数组，替代INNER JOIN返回的平铺结果。以下是两种可行方案：

基础实现（保留空数组） #

这个查询会返回所有对话，没有对应选项的对话会得到空的options数组：

SELECT 
  dialogues.*,
  COALESCE(json_agg(options) FILTER (WHERE options.dialogue_id IS NOT NULL), '[]'::json) AS options
FROM dialogues
LEFT JOIN options ON dialogues.dialogue_id = options.dialogue_id
GROUP BY dialogues.dialogue_id;

关键点解释： #

• LEFT JOIN：确保没有选项的对话也会被返回，对应MongoDB $lookup的默认行为
• json_agg(options)：将每个对话关联的所有options行聚合为一个JSON数组
• FILTER (WHERE options.dialogue_id IS NOT NULL)：排除LEFT JOIN后产生的NULL选项记录，避免数组中出现null元素
• COALESCE(..., '[]'::json)：把没有选项时的null替换为空数组，保持结果格式一致

仅返回有选项的对话 #

如果只需要保留存在对应选项的对话，可以用INNER JOIN替代LEFT JOIN：

SELECT 
  dialogues.*,
  json_agg(options) AS options
FROM dialogues
INNER JOIN options ON dialogues.dialogue_id = options.dialogue_id
GROUP BY dialogues.dialogue_id;

对应MongoDB $lookup的逻辑 #

这个查询和你原来的MongoDB聚合逻辑完全对应：

• LEFT JOIN 等价于 $lookup 的左连接行为
• json_agg(options) 等价于 $lookup 中as字段指定的数组聚合效果
• GROUP BY dialogues.dialogue_id 确保每个对话只返回一条记录，所有关联选项嵌套在options数组中

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