如何在PostgreSQL中用单查询获取一对多关联的嵌套格式数据

根据你的表结构，以下是不同主流数据库的实现方案，可直接查询出你需要的嵌套JSON格式：

MySQL 5.7+ #

利用JSON_OBJECT构造单个对象，JSON_ARRAYAGG聚合子分类为数组：

SELECT 
  JSON_OBJECT(
    'category', JSON_OBJECT('category_id', c.category_id, 'name', c.name),
    'subCategories', JSON_ARRAYAGG(
      JSON_OBJECT('subCategory_id', sc.sub_category_id, 'name', sc.name)
    )
  ) AS result
FROM category c
LEFT JOIN sub_category sc ON c.category_id = sc.category_id
WHERE c.category_id = 1 -- 按需指定分类ID，查询所有分类则删除此条件并保留GROUP BY
GROUP BY c.category_id, c.name;

• 若查询所有分类，删除WHERE子句后，每个分类会生成一条对应的JSON结果
• 若某分类无对应子分类，JSON_ARRAYAGG会返回NULL，可改用COALESCE(JSON_ARRAYAGG(...), JSON_ARRAY())生成空数组

PostgreSQL 9.4+ #

使用json_build_object构造对象，json_agg聚合子分类数组：

SELECT 
  json_build_object(
    'category', json_build_object('category_id', c.category_id, 'name', c.name),
    'subCategories', COALESCE(
      json_agg(json_build_object('subCategory_id', sc.sub_category_id, 'name', sc.name)),
      '[]'::json
    )
  ) AS result
FROM category c
LEFT JOIN sub_category sc ON c.category_id = sc.category_id
WHERE c.category_id = 1 -- 按需指定分类ID
GROUP BY c.category_id, c.name;

• COALESCE用于处理无子女分类的空数组场景
• 若要查询所有分类，删除WHERE子句即可

SQL Server 2016+ #

通过FOR JSON PATH自动映射嵌套结构：

SELECT 
  c.category_id AS 'category.category_id',
  c.name AS 'category.name',
  sc.sub_category_id AS 'subCategories.subCategory_id',
  sc.name AS 'subCategories.name'
FROM category c
LEFT JOIN sub_category sc ON c.category_id = sc.category_id
WHERE c.category_id = 1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

• WITHOUT_ARRAY_WRAPPER用于移除外层数组，返回单个JSON对象
• 若查询所有分类，删除WHERE和WITHOUT_ARRAY_WRAPPER，会返回包含所有分类对象的JSON数组

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