mysql - 带有连接的 SQL 查询以获取嵌套的对象数组

Question

总结:我将从一个 JSON 模式开始来描述期望。请注意具有嵌套对象数组的角色，我正在寻找一个可以通过单个查询获取它的“智能查询”。

{
    "id": 1,
    "first": "John",
    "roles": [ // Expectation -> array of objects
        {
            "id": 1,
            "name": "admin"
        },
        {
            "id": 2,
            "name": "accounts"
        }
    ]
}

用户

+----+-------+
| id | first |
+----+-------+
|  1 | John  |
|  2 | Jane  |
+----+-------+

作用

+----+----------+
| id |   name   |
+----+----------+
|  1 | admin    |
|  2 | accounts |
|  3 | sales    |
+----+----------+

用户角色

+---------+---------+
| user_id | role_id |
+---------+---------+
|       1 |       1 |
|       1 |       2 |
|       2 |       2 |
|       2 |       3 |
+---------+---------+

---

尝试 01

在连接字符串中的 multipleStatements:true 的帮助下，我会在我的 nodejs 代码中运行两个 sql 查询，这是一种天真的方法。 Info .

User.getUser = function(id) {
    const sql = "SELECT id, first FROM user WHERE id = ?; \
        SELECT role_id AS id, role.name from user_role \
        INNER JOIN role ON user_role.role_id = role.id WHERE user_id = ?";

    db.query(sql, [id, id], function(error, result){
        const data = result[0][0]; // first query result
        data.roles = result[1];  // second query result, join in code.
        console.log(data);
    });
};

问题:上面的代码产生了预期的 JSON 模式，但它需要两个查询，由于有多个语句，我能够将它缩小到尽可能小的代码单元，但我没有这样的在其他语言(例如 Java 或 C#)中的奢侈，我必须创建两个函数和两个 sql 查询。所以我正在寻找单一查询解决方案。

---

尝试 02

在早期的尝试中，在 SO 社区的帮助下，我能够使用单个查询接近以下内容，但它只能帮助生成字符串数组(而不是对象数组)。

User.getUser = function(id) {
    const sql = "SELECT user.id, user.first, GROUP_CONCAT(role.name) AS roles FROM user \
        INNER JOIN user_role ON user.id = user_role.user_id \
        INNER JOIN role ON user_role.role_id = role.id \
        WHERE user.id = ? \
        GROUP BY user.id";
    db.query(sql, id, function (error, result) {
        const data = {
            id: result[0].id, first: result[0].first,
            roles: result[0].roles.split(",") // manual split to create array
        };
        console.log(data);
    });
};

尝试 02 结果

{
    "id": 1,
    "first": "John",
    "roles": [ // array of string
        "admin",
        "accounts"
    ]
}

生成对象数组是一个很常见的需求，所以想知道 SQL 中一定有一些我不知道的东西。有没有办法借助最佳查询更好地实现这一目标。

或者让我知道没有这样的解决方案，就是这样，这就是它在生产代码中通过两个查询完成的方式。

---

尝试 03

在 GROUP_CONCAT(role.id) 中使用 role.id 而不是 role.name，这样你就可以获得一些 id然后使用另一个子查询来获取关联的角色名称，只是想...

SQL(不起作用，只是为了思考一些东西)

SELECT 
  user.id,  user.first,  
  GROUP_CONCAT(role.id) AS role_ids, 
  (SELECT id, name FROM role WHERE id IN role_ids) AS roles
FROM user 
INNER JOIN user_role ON user.id = user_role.user_id 
INNER JOIN role ON user_role.role_id = role.id 
WHERE user.id = 1
GROUP BY user.id;

---

编辑

根据 Amit 的回答，我了解到有这样的 solution在 SQL Server 中使用 JSON AUTO。是的，这是我在 MySQL 中寻找的东西。

准确表达。

When you join tables, columns in the first table are generated as properties of the root object. Columns in the second table are generated as properties of a nested object.

Answer 1

用户这个加入查询

FOR JSON AUTO 将为您的查询结果返回 JSON

SELECT U.UserID, U.Name, Roles.RoleID, Roles.RoleName  
FROM [dbo].[User] as U 
INNER JOIN [dbo].UserRole as UR ON UR.UserID=U.UserID 
INNER JOIN [dbo].RoleMaster as Roles ON Roles.RoleID=UR.RoleMasterID
FOR JSON AUTO

上述查询的输出是

[
  {
    "UserID": 1,
    "Name": "XYZ",
    "Roles": [
      {
        "RoleID": 1,
        "RoleName": "Admin"
      }
    ]
  },
  {
    "UserID": 2,
    "Name": "PQR",
    "Roles": [
      {
        "RoleID": 1,
        "RoleName": "Admin"
      },
      {
        "RoleID": 2,
        "RoleName": "User"
      }
    ]
  },
  {
    "UserID": 3,
    "Name": "ABC",
    "Roles": [
      {
        "RoleID": 1,
        "RoleName": "Admin"
      }
    ]
  }
]