使用mysql从连接的记录构建层次结构

Question

Table1 (users): id, name
Table2 (roles): id, name
Table3 (user-roles): userid, roleid

以下JSON中的预期结果：

{
  "id":1,
  "name":"username",
  "roles":[
     {
       "id":1
       "name":"admin"
     },
     {
       "id":2,
       "name":"community manager"
     }
  ]
}

是否有一种MySQL语法可以输出无限连接所需的结果？

等待:不确定，我怎么才能说得更好呢？有两个表通过第三个表(user-roles)相互关联。记录应该根据查询连接语句以JSON形式排列到层次结构中。

参见MSSQL解决方案：https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server

Answer 1

此查询返回您想要的内容，但它只能在MySQL 8.0或更高版本上运行

SELECT JSON_OBJECT('id', users.id, 'name', users.name, 'roles', JSON_ARRAYAGG(JSON_OBJECT('id', roles.id, 'name', roles.name)))
FROM users
  INNER JOIN user_roles
  ON users.id = user_roles.userid

  INNER JOIN roles
  ON user_roles.roleid = roles.id
GROUP BY users.id;