如何在MySQL的JSON_OBJECT同一子节点中合并两个关联表数据

核心思路是避免直接多表JOIN产生的笛卡尔积，先将data_fields和reports的数据转换成统一结构（添加datatype区分类型），通过UNION ALL合并后再按模块聚合，最终生成目标JSON结构。

示例SQL（MySQL 8.0+） #

假设表结构如下：

• modules: modu_id(主键), modu_name
• data_fields: field_id(主键), modu_id, field_name, field_type
• reports: report_id(主键), modu_id, report_name, report_desc

SELECT
  JSON_OBJECT(
    'modu_id', m.modu_id,
    'modu_name', m.modu_name,
    'data', COALESCE(
      JSON_ARRAYAGG(
        JSON_OBJECT(
          'datatype', c.datatype,
          'id', c.id,
          'name', c.name,
          'details', CASE 
            WHEN c.datatype = 'data_field' THEN JSON_OBJECT('field_type', c.field_type)
            ELSE JSON_OBJECT('description', c.report_desc)
          END
        )
      ), JSON_ARRAY()
    )
  ) AS module_json
FROM modules m
LEFT JOIN (
  -- 转换data_fields为统一格式
  SELECT
    modu_id,
    'data_field' AS datatype,
    field_id AS id,
    field_name AS name,
    field_type,
    NULL AS report_desc
  FROM data_fields
  UNION ALL
  -- 转换reports为统一格式
  SELECT
    modu_id,
    'report' AS datatype,
    report_id AS id,
    report_name AS name,
    NULL AS field_type,
    report_desc
  FROM reports
) c ON m.modu_id = c.modu_id
GROUP BY m.modu_id, m.modu_name;

示例SQL（PostgreSQL） #

SELECT
  json_build_object(
    'modu_id', m.modu_id,
    'modu_name', m.modu_name,
    'data', COALESCE(
      json_agg(
        json_build_object(
          'datatype', c.datatype,
          'id', c.id,
          'name', c.name,
          'details', CASE 
            WHEN c.datatype = 'data_field' THEN json_build_object('field_type', c.field_type)
            ELSE json_build_object('description', c.report_desc)
          END
        )
      ), '[]'::json
    )
  ) AS module_json
FROM modules m
LEFT JOIN (
  SELECT
    modu_id,
    'data_field'::text AS datatype,
    field_id AS id,
    field_name AS name,
    field_type,
    NULL::text AS report_desc
  FROM data_fields
  UNION ALL
  SELECT
    modu_id,
    'report'::text AS datatype,
    report_id AS id,
    report_name AS name,
    NULL::text AS field_type,
    report_desc
  FROM reports
) c ON m.modu_id = c.modu_id
GROUP BY m.modu_id, m.modu_name;

重复记录的原因 #

如果直接用modules LEFT JOIN data_fields LEFT JOIN reports，同模块下的每条data_fields记录会和所有reports记录交叉配对，产生笛卡尔积，导致最终的data数组里出现大量重复记录。而用UNION ALL合并两张表的独立记录，再按模块聚合，就能彻底避免这个问题。

注意事项 #

• UNION ALL要求两个子查询的字段数量、类型完全匹配，所以需要用NULL填充缺失的字段
• 使用COALESCE确保没有关联数据的模块，data字段会生成空数组而非NULL
• 可以根据需求在JSON_ARRAYAGG/json_agg中添加ORDER BY子句，对data数组内的记录排序

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