如何在MySQL 8.x中通过层级多表直接生成嵌套JSON结果？

可以通过MySQL 8.x提供的JSON_OBJECT和JSON_ARRAYAGG函数组合，仅需一次查询就能直接生成你需要的嵌套JSON结构，避免多次数据库调用和内存拼接的开销。

核心查询语句 #

SELECT JSON_ARRAYAGG(user_json) AS result
FROM (
    SELECT 
        JSON_OBJECT(
            'id', u.id,
            'first_name', u.first_name,
            'pets', JSON_ARRAYAGG(
                JSON_OBJECT(
                    'id', p.id,
                    'name', p.name
                )
            )
        ) AS user_json
    FROM Users u
    LEFT JOIN Pets p ON u.id = p.owner_id
    -- 可添加过滤条件，例如 WHERE u.first_name = 'Frank'
    GROUP BY u.id, u.first_name
) AS sub_query;

语句说明 #

1. 关联与分组：通过LEFT JOIN关联用户表和宠物表（若只需要有宠物的用户，可替换为INNER JOIN），并按用户的id和first_name分组，确保每个用户的记录被聚合在一起。
2. 生成宠物JSON数组：用JSON_OBJECT将单条宠物记录转为{"id": x, "name": y}格式的JSON对象，再通过JSON_ARRAYAGG将同一用户的所有宠物对象聚合成数组。
3. 生成用户嵌套JSON：外层JSON_OBJECT将用户自身字段与宠物数组组合成包含pets属性的用户JSON对象。
4. 生成最终JSON数组：最外层的JSON_ARRAYAGG将所有用户对象聚合成一个完整的JSON数组，与你期望的结构完全匹配。

过滤特定用户的示例 #

如果需要查询名为Frank的用户及其宠物，只需在子查询中添加WHERE条件，一次查询即可完成：

SELECT JSON_ARRAYAGG(user_json) AS result
FROM (
    SELECT 
        JSON_OBJECT(
            'id', u.id,
            'first_name', u.first_name,
            'pets', JSON_ARRAYAGG(
                JSON_OBJECT(
                    'id', p.id,
                    'name', p.name
                )
            )
        ) AS user_json
    FROM Users u
    LEFT JOIN Pets p ON u.id = p.owner_id
    WHERE u.first_name = 'Frank'
    GROUP BY u.id, u.first_name
) AS sub_query;

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