MySQL 生成嵌套 JSON 对象

我正在尝试从 Many_to_many 关系生成嵌套 JSON 对象。我正在尝试找到下面的 JSON 模式。

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

我尝试了如下组合 JSON 函数，但结果与我需要的不同。

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，每个 user_id 有两行，每个角色一行。

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 ));

使用此 SQL 查询和那些 JSON 函数，我收到错误

错误代码：1242。子查询返回超过 1 行。

我该如何解决这个问题？

你把事情想得太复杂了。您希望每个用户一个对象，在一个数组中具有多个角色，因此您必须按用户分组并形成该对象。如果您不想，处理没有角色的用户会有点棘手[null]。 （如果您正在进行内部联接，而不是左联接，则可以省略大小写并仅使用 json_arrayagg。）

select
  json_object(
    'user_id',user_tbl.user_id,
    'user_name',user_tbl.user_name,
    'role_desc',
      case
        when count(app_role_tbl.role_desc) then json_arrayagg(app_role_tbl.role_desc)
        else json_array()
      end
  ) user
from user_tbl
left join user_role using (user_id)
left join app_role_tbl using (role_id)
group by user_tbl.user_id

这会为每个用户生成一个 json 对象；如果您希望服务器将所有这些组合到一个 json 数组中，只需将其放入子查询中即可：

select json_arrayagg(user)
from (
  select
    json_object(
      'user_id',user_tbl.user_id,
      'user_name',user_tbl.user_name,
      'role_desc',
        case
          when count(app_role_tbl.role_desc) then json_arrayagg(app_role_tbl.role_desc)
          else json_array()
        end
    ) user
  from user_tbl
  left join user_role using (user_id)
  left join app_role_tbl using (role_id)
  group by user_tbl.user_id
) users;

fiddle https://dbfiddle.uk/mPatGrhS