如何通过MySQL从多对多关系生成嵌套JSON对象？

需求 #

需要从用户-角色的多对多关系中生成如下格式的嵌套JSON对象：

[
  {
    "user_id": 151,
    "user_name": "Sam123",
    "role_desc": ["Power_User"]
  },
  {
    "user_id": 152,
    "user_name": "John999",
    "role_desc": ["Admin", "Power_User"]
  }
]

尝试的SQL及问题 #

第一种SQL及问题 #

执行以下SQL：

SET @result = JSON_OBJECT('result',0,'data',(SELECT 
              JSON_ARRAYAGG(JSON_OBJECT(             
              'user_id',user_tbl.user_id,
              'user_name', user_tbl.user_name,
              'role_desc',app_role_tbl.role_desc)) 
              FROM user_tbl
INNER JOIN user_role ON user_tbl.user_id = user_role.user_id
INNER JOIN app_role_tbl ON user_role.role_id = app_role_tbl.role_id ));

问题：出现user_id重复，每个角色对应一条用户记录，不符合需求格式。

第二种SQL及问题 #

执行以下SQL：

SET @result = JSON_OBJECT('result',0,'data',(SELECT 
                         JSON_ARRAYAGG(JSON_OBJECT(                      
                         'user_id',user_tbl.user_id,
                         'user_name', user_tbl.user_name,
              'role_desc',JSON_ARRAYAGG(app_role_tbl.role_desc))) 
              FROM user_tbl
INNER JOIN user_role ON user_tbl.user_id = user_role.user_id
INNER JOIN app_role_tbl ON user_role.role_id = app_role_tbl.role_id ));

问题：触发错误Error Code: 1242. Subquery returns more than 1 row，因为未分组的情况下，内层JSON_ARRAYAGG会返回多个结果，无法直接嵌套在外层的JSON_OBJECT中。

解决方法 #

核心思路是先按用户维度分组，在分组内聚合角色为数组，再构造每个用户的JSON对象，最后聚合所有用户对象为数组。正确SQL如下：

SET @result = JSON_OBJECT(
    'result', 0,
    'data', (
        SELECT JSON_ARRAYAGG(
            JSON_OBJECT(
                'user_id', user_tbl.user_id,
                'user_name', user_tbl.user_name,
                'role_desc', JSON_ARRAYAGG(app_role_tbl.role_desc)
            )
        )
        FROM user_tbl
        INNER JOIN user_role ON user_tbl.user_id = user_role.user_id
        INNER JOIN app_role_tbl ON user_role.role_id = app_role_tbl.role_id
        GROUP BY user_tbl.user_id, user_tbl.user_name
    )
);

说明 #

• 通过GROUP BY user_tbl.user_id, user_tbl.user_name按用户分组，确保每个用户只生成一条记录；
• 分组内使用JSON_ARRAYAGG(app_role_tbl.role_desc)将当前用户的所有角色聚合为数组；
• 外层用JSON_ARRAYAGG将每个用户的JSON对象聚合为最终的数组，完美匹配需求格式。

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