PostgreSQL如何在SELECT查询中将ID数组填充为关联表对象列表

可以实现，你之前的写法问题在于JOIN之后会将table_2的单条记录按匹配到的table_1条数拆成多条，没有做后续的聚合重组为数组，所以无法得到嵌套的数组结构。你可以结合PostgreSQL的unnest函数（对应MongoDB的$unwind功能）和JSON聚合函数完成需求，具体实现SQL如下：

SELECT
  t2.id,
  JSON_AGG(
    JSON_BUILD_OBJECT(
      'id', t1.id::text,
      'name', t1.name
    )
  ) AS "table1ElementsList"
FROM table_2 t2
-- 展开外键数组，对应MongoDB的$unwind功能
LEFT JOIN LATERAL unnest(t2.table_1_fk_list) AS t1_fk(id) ON true
LEFT JOIN table_1 t1 ON t1.id = t1_fk.id
GROUP BY t2.id;

如果需要直接返回和你预期完全一致的JSON数组结构，可以使用如下语句：

SELECT
  JSON_AGG(
    JSON_BUILD_OBJECT(
      'id', t2.id::text,
      'table1ElementsList', COALESCE(t1_agg.element_list, '[]'::json)
    )
  ) AS final_result
FROM table_2 t2
LEFT JOIN LATERAL (
  SELECT
    JSON_AGG(
      JSON_BUILD_OBJECT('id', t1.id::text, 'name', t1.name)
    ) AS element_list
  FROM unnest(t2.table_1_fk_list) AS t1_fk(id)
  JOIN table_1 t1 ON t1.id = t1_fk.id
) t1_agg ON true;

关键逻辑说明 #

• unnest(array)：将数组字段拆分为多行记录，功能和MongoDB的$unwind完全一致
• JSON_BUILD_OBJECT：构建单个JSON对象，对应你需要的嵌套元素结构
• JSON_AGG：将多行结果聚合为JSON数组，得到你预期的列表结构
• LEFT JOIN LATERAL：允许关联子查询时引用前置表的字段，实现行级别的关联逻辑
• COALESCE：处理外键数组为空或没有匹配到table_1记录的场景，返回空数组避免出现null值

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