如何用SQL实现主表关联子表数据集以嵌套对象形式单列返回？

不同数据库提供了专门的JSON聚合函数来实现这种嵌套结构，以下是主流数据库的实现方式：

PostgreSQL #

利用json_agg聚合行数据为JSON数组，结合LEFT JOIN确保无关联数据的条目返回空数组：

-- 返回单个JSON对象组成的结果集
SELECT 
  json_build_object(
    'id', t1.id,
    'data', t1.data,
    'related_stuff', COALESCE(json_agg(json_build_object('id', t2.id, 'data', t2.data)) FILTER (WHERE t2.id IS NOT NULL), '[]'::json)
  )
FROM Table1 t1
LEFT JOIN Table2 t2 ON t2.related_to = t1.id
GROUP BY t1.id, t1.data;

-- 返回完整的JSON数组
SELECT json_agg(result)
FROM (
  SELECT 
    json_build_object(
      'id', t1.id,
      'data', t1.data,
      'related_stuff', COALESCE(json_agg(json_build_object('id', t2.id, 'data', t2.data)) FILTER (WHERE t2.id IS NOT NULL), '[]'::json)
    ) AS result
  FROM Table1 t1
  LEFT JOIN Table2 t2 ON t2.related_to = t1.id
  GROUP BY t1.id, t1.data
) sub;

MySQL 8.0+ #

使用JSON_ARRAYAGG和JSON_OBJECT函数实现嵌套：

SELECT 
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'id', t1.id,
      'data', t1.data,
      'related_stuff', COALESCE(related_data, JSON_ARRAY())
    )
  ) AS result
FROM Table1 t1
LEFT JOIN (
  SELECT 
    related_to,
    JSON_ARRAYAGG(JSON_OBJECT('id', id, 'data', data)) AS related_data
  FROM Table2
  GROUP BY related_to
) t2 ON t2.related_to = t1.id;

SQL Server 2016+ #

通过FOR JSON PATH语法生成嵌套JSON：

SELECT 
  t1.id,
  t1.data,
  (
    SELECT id, data 
    FROM Table2 t2 
    WHERE t2.related_to = t1.id 
    FOR JSON PATH
  ) AS related_stuff
FROM Table1 t1
FOR JSON PATH, ROOT('');

该查询会直接返回符合要求的JSON数组，无关联数据的条目related_stuff自动显示为空数组[]。

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