如何通过MySQL查询将多对多关联的赛事用户转为JSON数组？

完全可以直接用SQL实现这个需求！不用非得依赖JavaScript来处理，不同的关系型数据库都提供了原生的JSON聚合函数，能直接返回你要的格式。下面我给你分几种常用数据库来举例说明，还有备选的JS处理方案：

根据你使用的数据库类型，选择对应的聚合函数即可：

MySQL（5.7及以上版本） #

MySQL提供了JSON_ARRAYAGG和JSON_OBJECT函数，可以直接将聚合后的用户数据转为JSON数组：

SELECT 
    t.TournamentId,
    t.name AS tournament_name,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'UserId', u.UserId,
            'name', u.name
        )
    ) AS users_list
FROM Tournament t
LEFT JOIN Tournament_Users tu ON t.TournamentId = tu.Tournament_Id
LEFT JOIN User u ON tu.User_Id = u.UserId
GROUP BY t.TournamentId, t.name;

说明：用LEFT JOIN是为了确保即使没有用户参与的赛事也能被返回，JSON_ARRAYAGG会把每个用户的信息封装成JSON对象后聚合成数组。

SQL Server（2016及以上版本） #

SQL Server用FOR JSON PATH语法来生成JSON，你可以通过子查询为每个赛事生成对应的用户列表：

SELECT 
    t.TournamentId,
    t.name AS tournament_name,
    ISNULL(
        (
            SELECT u.UserId, u.name
            FROM Tournament_Users tu
            INNER JOIN [User] u ON tu.User_Id = u.UserId
            WHERE tu.Tournament_Id = t.TournamentId
            FOR JSON PATH
        ), '[]'
    ) AS users_list
FROM Tournament t;

注意：SQL Server中User是关键字，所以查询时要加方括号[User]；ISNULL用来确保没有用户的赛事返回空数组[]而非NULL。

PostgreSQL（9.5及以上版本） #

PostgreSQL用json_agg和json_build_object来实现JSON聚合，还可以用FILTER过滤掉空数据：

SELECT 
    t.tournamentid,
    t.name AS tournament_name,
    json_agg(
        json_build_object(
            'UserId', u.userid,
            'name', u.name
        )
    ) FILTER (WHERE u.userid IS NOT NULL) AS users_list
FROM tournament t
LEFT JOIN tournament_users tu ON t.tournamentid = tu.tournament_id
LEFT JOIN "user" u ON tu.user_id = u.userid
GROUP BY t.tournamentid, t.name;

说明：FILTER子句用来排除没有用户时产生的NULL值，确保空赛事返回的是[]而不是包含null的数组。

如果你暂时不想用SQL的JSON函数，也可以先查询出所有关联数据，再在JS中进行聚合处理：

第一步：执行基础SQL查询 #

SELECT 
    t.TournamentId,
    t.name AS tournament_name,
    u.UserId,
    u.name AS user_name
FROM Tournament t
LEFT JOIN Tournament_Users tu ON t.TournamentId = tu.Tournament_Id
LEFT JOIN User u ON tu.User_Id = u.UserId;

第二步：用JavaScript聚合数据 #

假设查询结果是一个数组results，可以这样处理：

const tournamentMap = new Map();

results.forEach(row => {
    const { TournamentId, tournament_name, UserId, user_name } = row;
    
    // 如果当前赛事还没加入映射，初始化数据
    if (!tournamentMap.has(TournamentId)) {
        tournamentMap.set(TournamentId, {
            TournamentId,
            tournament_name,
            users_list: []
        });
    }
    
    // 只有存在用户数据时才添加到列表
    if (UserId) {
        tournamentMap.get(TournamentId).users_list.push({
            UserId,
            name: user_name
        });
    }
});

// 把映射转为最终的数组格式
const finalResult = Array.from(tournamentMap.values());
console.log(JSON.stringify(finalResult, null, 2));

优先推荐用SQL原生方案，因为数据库端聚合的效率更高，还能减少前端的数据处理量。如果你的数据库版本较低不支持JSON函数，再考虑用JavaScript来处理。

内容的提问来源于stack exchange，提问作者J.Kirk.