SQL左连接实现子表匹配行聚合为父行内嵌JSON数组方法

普通LEFT JOIN执行后会返回一对多平铺结果：如果一个父行匹配N条子行，父行字段就会重复返回N次，和你需要的嵌套数组结构不符。要实现目标效果，需要借助数据库内置的JSON聚合能力，将匹配的子行按父表主键分组聚合为数组字段即可。

原伪SQL的语法问题 #

你写的查询将WHERE条件放在了LEFT JOIN关键字之前，不符合SQL语法顺序，正确的SQL子句顺序为SELECT -> FROM -> JOIN -> WHERE -> GROUP BY -> 聚合计算 -> ORDER BY。

不同数据库的可执行写法 #

PostgreSQL #

用内置的jsonb_agg聚合函数，通过相关子查询直接聚合匹配的子行，无匹配子行时自动返回null，不需要额外写分组逻辑：

SELECT 
  p.uuid,
  p.caseId,
  p.param,
  (
    SELECT jsonb_agg(to_jsonb(c))
    FROM Child_table c
    WHERE c.parentUuid = p.uuid
  ) AS childRows
FROM Parent_table p
WHERE p.caseId = '1';

MySQL 5.7+/8.0 #

用JSON_ARRAYAGG配合JSON_OBJECT构造子行结构，通过CASE判断处理无匹配子行的边界场景，避免返回仅含null的无效数组，分组逻辑兼容ONLY_FULL_GROUP_BY的sql_mode要求：

SELECT 
  p.uuid,
  p.caseId,
  p.param,
  CASE 
    WHEN MAX(c.uuid) IS NULL THEN NULL
    ELSE JSON_ARRAYAGG(
      JSON_OBJECT(
        'uuid', c.uuid,
        'parentUuid', c.parentUuid
      )
    )
  END AS childRows
FROM Parent_table p
LEFT JOIN Child_table c 
  ON c.parentUuid = p.uuid
WHERE p.caseId = '1'
GROUP BY p.uuid, p.caseId, p.param;

SQL Server 2016及以上版本 #

通过FOR JSON PATH子句直接生成子表的JSON数组结构，无匹配子行时自动返回null：

SELECT 
  p.uuid,
  p.caseId,
  p.param,
  (
    SELECT c.uuid, c.parentUuid
    FROM Child_table c
    WHERE c.parentUuid = p.uuid
    FOR JSON PATH
  ) AS childRows
FROM Parent_table p
WHERE p.caseId = '1';

以上语句执行后，直接将结果序列化为JSON即可完全匹配你给出的期望输出格式：匹配到子行的父行携带childRows数组，无匹配的父行该字段为null，caseId不为1的父行会被WHERE条件过滤不会返回。

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