如何用单条SQL查询关联两表并将子表数据转为数组列

完全可以仅用SELECT语句实现这个需求，核心思路是分组聚合+JSON构造，不同数据库有对应的函数支持，以下是主流数据库的实现方案：

MySQL 5.7+/MariaDB 10.2+ #

利用JSON_ARRAYAGG和JSON_OBJECT函数直接聚合生成JSON数组：

SELECT
  t1.id,
  t1.value,
  COALESCE(
    JSON_ARRAYAGG(JSON_OBJECT('id', t2.id, 'value', t2.value)),
    JSON_ARRAY()
  ) AS newColumnForJoinedValuesFromTable2
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.id = t2.id_t1
GROUP BY t1.id, t1.value;

• LEFT JOIN保证Table1的所有行都被返回，即使没有对应Table2的记录
• COALESCE用于将无对应记录时的NULL替换为空数组[]

PostgreSQL #

使用json_agg和json_build_object函数实现：

SELECT
  t1.id,
  t1.value,
  COALESCE(
    json_agg(json_build_object('id', t2.id, 'value', t2.value)),
    '[]'::json
  ) AS newColumnForJoinedValuesFromTable2
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.id = t2.id_t1
GROUP BY t1.id, t1.value;

如果需要返回原生JSONB类型，可以把json_agg换成jsonb_agg。

SQL Server 2016+ #

通过子查询结合FOR JSON PATH生成JSON数组：

SELECT
  t1.id,
  t1.value,
  ISNULL(
    (
      SELECT id, value
      FROM Table2 t2
      WHERE t2.id_t1 = t1.id
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ),
    '[]'
  ) AS newColumnForJoinedValuesFromTable2
FROM Table1 t1;

• 子查询针对每个Table1的行，生成对应的Table2记录JSON数组
• WITHOUT_ARRAY_WRAPPER避免额外的外层数组包裹
• ISNULL确保无对应记录时返回空数组

结果说明 #

以上查询返回的newColumnForJoinedValuesFromTable2字段为JSON格式字符串，绝大多数客户端工具、ORM框架都会自动将其解析为你期望的对象数组结构，和示例输出完全匹配。

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