MySQL中json_object与json_arrayagg结合GROUP BY未达预期结果

你的问题出在缺少顶层的分支聚合步骤：当前SQL只完成了单个分支与对应资产的嵌套，但没有把所有分支对象合并成一个顶层数组，所以每个分支单独输出一行JSON。

正确实现思路 #

需要两层JSON聚合：

1. 按分支分组，将每个分支关联的资产聚合成子数组，生成带资产列表的分支对象。
2. 将所有分支对象聚合成一个顶层的branches数组，得到统一的嵌套结构。

PostgreSQL 示例代码 #

假设表结构如下：

• branches：branch_id(主键), branch_name, ...
• assets：asset_id(主键), asset_name, asset_type, ...
• branch_r_asset：branch_id, asset_id(联合主键)

SELECT json_agg(branch_obj) AS nested_result
FROM (
  SELECT
    b.branch_id,
    b.branch_name,
    -- 过滤无资产的情况，生成空数组而非含null的数组
    json_agg(a) FILTER (WHERE a.asset_id IS NOT NULL) AS assets
  FROM branches b
  LEFT JOIN branch_r_asset bra ON b.branch_id = bra.branch_id
  LEFT JOIN assets a ON bra.asset_id = a.asset_id
  -- 必须包含branches的所有非聚合字段
  GROUP BY b.branch_id, b.branch_name
) AS branch_obj;

MySQL 示例代码（8.0+） #

MySQL 使用JSON_ARRAYAGG替代PostgreSQL的json_agg，逻辑一致：

SELECT JSON_ARRAYAGG(branch_obj) AS nested_result
FROM (
  SELECT
    b.branch_id,
    b.branch_name,
    JSON_ARRAYAGG(a) FILTER (WHERE a.asset_id IS NOT NULL) AS assets
  FROM branches b
  LEFT JOIN branch_r_asset bra ON b.branch_id = bra.branch_id
  LEFT JOIN assets a ON bra.asset_id = a.asset_id
  GROUP BY b.branch_id, b.branch_name
) AS branch_obj;

关键注意事项 #

• 分组时必须包含branches表的所有非聚合字段，避免因分组规则导致的数据错误。
• 使用FILTER (WHERE ...)过滤掉无资产时的null值，确保空分支的assets字段是空数组而非包含null元素的数组。

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