使用 join 获取嵌套对象数组的 SQL 查询

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

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

user

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

role

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

用户角色

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

尝试01

在一个天真的方法中，我在我的nodejs代码中运行两个sql查询，在multipleStatements:true在连接字符串中。Info http://www.technicalkeeda.com/nodejs-tutorials/nodejs-mysql-multiple-statement-queries/.

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

Problem：上面的代码生成了预期的 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

use role.id代替role.name in GROUP_CONCAT(role.id)，这样你就可以获取一些 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;

Edit

根据阿米特的回答，我了解到有这样的solution https://learn.microsoft.com/en-us/sql/relational-databases/json/format-json-output-automatically-with-auto-mode-sql-server?view=sql-server-2017在 SQL Server 中使用JSON AUTO。是的，这就是我在 MySQL 中寻找的东西。

要准确表达。

当您连接表时，第一个表中的列将生成为 根对象的属性。第二个表中的列是 作为嵌套对象的属性生成。

使用此加入查询

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"
      }
    ]
  }
]