如何在MySQL中生成含父级与多级子级的JSON列？

针对你的三张表结构，可以使用MySQL 8.0及以上版本提供的JSON_ARRAYAGG和JSON_OBJECT函数实现嵌套JSON格式的输出，同时返回appli_num字段。

实现SQL语句 #

SELECT
    a.appli_num,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'modu_num', m.modu_num,
            'modu_title', m.modu_title,
            'data_fields', COALESCE(df.data_fields_arr, JSON_ARRAY())
        )
    ) AS modules_json
FROM
    Applications a
JOIN Modules m ON a.appli_num = m.appli_num
LEFT JOIN (
    SELECT
        appli_num,
        modu_num,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'data_num', data_num,
                'data_title', data_title
            )
        ) AS data_fields_arr
    FROM Data_fields
    GROUP BY appli_num, modu_num
) df ON m.appli_num = df.appli_num AND m.modu_num = df.modu_num
GROUP BY a.appli_num;

代码说明 #

1. 子查询df：按appli_num和modu_num分组，将每个模块下的所有数据字段聚合为data_fields_arr数组，每个元素是包含data_num和data_title的JSON对象。
2. 主查询关联：关联Applications和Modules表，通过左连接子查询结果确保无数据字段的模块也能被返回；使用COALESCE将空值替换为空数组，保证格式统一。
3. 最终聚合：按appli_num分组，将每个应用下的所有模块聚合为modules_json数组，每个模块对象包含modu_num、modu_title和对应的data_fields数组。

输出说明 #

返回结果包含两列：

• appli_num：应用编号
• modules_json：完全符合你要求格式的嵌套JSON数组

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