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

Question

关闭。此题需要 details or clarity 。目前不接受答案。

---

想要改进这个问题吗？通过 editing this post 添加详细信息并澄清问题.

已关闭 5 年前。

Improve this 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 语法可以输出无限连接所需的结果？

等待:不确定，我该如何更好地表达这一点。有两个表通过第三个表(用户角色)相关联。应根据查询 JOIN 语句将记录排列为 JSON 层次结构。

参见MSSQL解决方案:https://learn.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;