如何在MySQL中按分类将子菜单项作为独立数组返回

当前数据表与查询结果 #

我拥有menu_categories和menu两张数据表，执行关联查询后得到的结果如下：

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [category_name] => Settings
            [category_icon] => gear
            [menu_name] => Create settings
        )

    [1] => stdClass Object
        (
            [id] => 2
            [category_name] => Users
            [category_icon] => men-shape
            [menu_name] => Create user
        )

    [2] => stdClass Object
        (
            [id] => 1
            [category_name] => Settings
            [category_icon] => gear
            [menu_name] => Edit settings
        )
)

期望结果 #

希望仅通过SQL查询（不使用PHP）得到如下格式的结果，每个分类下包含子菜单数组：

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [category_name] => Settings
            [category_icon] => 
            [whole_menu_table_as_array] => Array
            (
                [0] => Array
                (
                    [id] => 1
                    [menu_name] => Create Settings
                )
                [1] => Array
                (
                    [id] => 2
                    [menu_name] => Edit Settings
                )                    
            )
        )

    [1] => stdClass Object
        (
            [id] => 2
            [category_name] => Users
            [category_icon] => 
            [whole_menu_table_as_array] => Array
            (
                [0] => Array
                (
                    [id] => 3
                    [menu_name] => Create Users
                )                   
            )
        )
)

当前使用的SQL语句 #

SELECT cat.id, cat.category_name, cat.category_icon, men.id, men.menu_name 
FROM `menu_categories` AS cat 
INNER JOIN menu AS men on cat.id = men.menu_category_id 
WHERE cat.user_role_id = 3 GROUP BY men

解决方案 #

适用MySQL 5.7+或MariaDB的实现方案 #

利用MySQL的JSON聚合函数JSON_ARRAYAGG()和JSON_OBJECT()，可以直接将每个分类下的菜单数据打包成JSON数组，查询语句如下：

SELECT 
    cat.id,
    cat.category_name,
    cat.category_icon,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', men.id,
            'menu_name', men.menu_name
        )
    ) AS whole_menu_table_as_array
FROM `menu_categories` AS cat
INNER JOIN menu AS men ON cat.id = men.menu_category_id
WHERE cat.user_role_id = 3
GROUP BY cat.id, cat.category_name, cat.category_icon;

返回的whole_menu_table_as_array字段为JSON格式数组，可直接在应用中解析为数组使用。

注意事项 #

• 若使用MySQL 5.7以下版本，由于缺少原生JSON函数，只能通过GROUP_CONCAT()拼接字符串模拟，后续仍需应用层处理成数组，无法完全通过SQL得到数组结构结果。
• 原查询中的GROUP BY men属于错误写法，MySQL不支持直接按整张表分组，正确的分组逻辑应基于分类的唯一标识（如cat.id、cat.category_name等）。

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