如何在Mongoose+NextJS中用aggregate多集合查询并求和

需求 #

需要从Player和Score两个Mongoose Schema中获取数据，Player存储玩家基础信息，Score包含子文档记录各玩家的单场比赛得分，最终得到包含玩家ID、姓名、邮箱及总得分的结构化数据。

Player Schema #

import { Schema, model, models } from "mongoose";

const PlayerSchema = new Schema({
    creator: {
        type: Schema.Types.ObjectId,
        ref: 'User',
    },

    name: {
        type: String,
        require: [ true, 'Name is required!' ],
    },

    email: {
        type: String,
        unique: [ true, 'Email already exist!' ],
        required: [ true, 'Email is required!' ],
    }
});

const Player = models.Player || model( 'Player', PlayerSchema );

export default Player;

Score Schema（包含子文档结构） #

import { Schema, model, models } from "mongoose";

const scoresToSave = new Schema({
    player: {
        type: Schema.Types.ObjectId,
        ref: 'Player',
    },
    score: { type: Number }
});

const ScoreSchema = new Schema({
    creator: {
        type: Schema.Types.ObjectId,
        ref: 'User',
    },

    tournament: {
        type: Schema.Types.ObjectId,
        ref: 'Tournament',
    },

    date: {
        type: Date,
        require: [ true, 'Date is required!' ],
    },

    scores: [scoresToSave],
});

const Score = models.Score || model( 'Score', ScoreSchema );

export default Score;

集合存储的实际文档示例 #

Players集合 #

{
"_id":{"$oid":"652f3470729e8d8a59b221dd"},
"creator":{"$oid":"652af415729e8d8a59b2202f"},
"name":"Player 1",
"email":"player1@email.com"
}

{
"_id":{"$oid":"652f36c9f6ad40c3aedf0cdf"},
"creator":{"$oid":"652af415729e8d8a59b2202f"},
"name":"Player 2",
"email":"player2@email.com"
}

Scores集合 #

{
"_id":{"$oid":"65386103b5e6b094e0980f2a"},
"creator":{"$oid":"652af415729e8d8a59b2202f"},
"tournament":{"$oid":"652f2515729e8d8a59b221b6"},
"date":{"$date":{"$numberLong":"1698192000000"}},
"scores":[
  {
    "player":{"$oid":"652f3470729e8d8a59b221dd"},
    "score":{"$numberInt":"10"},
    "_id":{"$oid":"65386103b5e6b094e0980f2b"}
  },
  {
    "player":{"$oid":"652f36c9f6ad40c3aedf0cdf"},
    "score":{"$numberInt":"20"},
    "_id":{"$oid":"65386103b5e6b094e0980f2c"}
  }
]
}

{
"_id":{"$oid":"6538611bb5e6b094e0980f59"},
"creator":{"$oid":"652af415729e8d8a59b2202f"},
"tournament":{"$oid":"652f2515729e8d8a59b221b6"},
"date":{"$date":{"$numberLong":"1698192000000"}},
"scores":[
  {
    "player":{"$oid":"652f3470729e8d8a59b221dd"},
    "score":{"$numberInt":"11"},
    "_id":{"$oid":"6538611bb5e6b094e0980f5a"}
  },
  {
    "player":{"$oid":"652f36c9f6ad40c3aedf0cdf"},
    "score":{"$numberInt":"21"},
    "_id":{"$oid":"6538611bb5e6b094e0980f5b"}
  }
]
}

期望的响应数据结构 #

[
  {
    _id: new ObjectId("652f3470729e8d8a59b221dd"),
    name: 'Player 1',
    email: 'player1@email.com',
    totalScore: 21
  },
  {
    _id: new ObjectId("6536f85cb5e6b094e09809f1"),
    name: 'Player 2',
    email: 'player2@email.com',
    totalScore: 41
  },
]

当前实现代码 #

import { connectToDB } from "@utils/database";
import Player from "@models/player";

export const GET = async ( req ) => {
    const resources = {
        "_id": "$_id",
        name: { "$first": "$name" },
        email: { "$first": "$email" },
        "totalScore": { "$sum": "scores.scores.score" },
    };

    try {
        await connectToDB();

        const players = await Player.aggregate([
            {
                $group: resources
            }, {
                $lookup: {
                    from: "Scores",
                    localField: "_id",
                    foreignField: "scores.player",
                    as: "score"
                }
            }
        ]);

        console.log('TRY: ', players);

        return new Response(
            JSON.stringify(players),
            {status: 200}
        );
    } catch (error) {
        return new Response(
            "Failed to fetch all players",
            {status: 500}
        );
    }
}

代码执行后的返回结果 #

TRY:  [
  {
    _id: new ObjectId("652f3470729e8d8a59b221dd"),
    name: 'Player 1',
    email: 'player1@email.com',
    totalScore: 0,
    score: []
  },
  {
    _id: new ObjectId("6536f85cb5e6b094e09809f1"),
    name: 'Player 2',
    email: 'player2@email.com',
    totalScore: 0,
    score: []
  },
]

问题排查与修复方案 #

核心问题分析 #

1. 聚合阶段顺序错误：先执行$group再执行$lookup，此时$group中引用的scores.scores.score字段尚未通过关联获取，导致totalScore计算为0；同时$lookup的集合名称错误，Mongoose默认集合名为小写复数形式，from: "Scores"应改为from: "scores"，这也是score数组为空的原因。
2. 无意义的$group阶段：Player集合中每个文档对应独立玩家，按_id分组完全多余，反而会提前锁定字段，阻碍后续关联数据的处理。
3. 得分计算路径错误：即使关联到Score数据，原代码的scores.scores.score路径也不匹配$lookup返回的数据结构，需要先展开嵌套数组才能正确求和。

修复后的代码（从Player集合出发） #

import { connectToDB } from "@utils/database";
import Player from "@models/player";

export const GET = async (req) => {
    try {
        await connectToDB();

        const players = await Player.aggregate([
            // 先关联Score集合，匹配玩家ID在scores子文档中的记录
            {
                $lookup: {
                    from: "scores",
                    localField: "_id",
                    foreignField: "scores.player",
                    as: "scoreRecords"
                }
            },
            // 展开关联到的所有Score文档
            { $unwind: "$scoreRecords" },
            // 展开每个Score文档中的scores子数组
            { $unwind: "$scoreRecords.scores" },
            // 过滤出当前玩家对应的得分记录
            { $match: { "scoreRecords.scores.player": "$_id" } },
            // 按玩家ID分组，聚合基础信息和总得分
            {
                $group: {
                    _id: "$_id",
                    name: { $first: "$name" },
                    email: { $first: "$email" },
                    totalScore: { $sum: "$scoreRecords.scores.score" }
                }
            }
        ]);

        console.log('TRY: ', players);

        return new Response(JSON.stringify(players), { status: 200 });
    } catch (error) {
        return new Response("Failed to fetch all players", { status: 500 });
    }
}

更高效的方案（从Score集合反向聚合） #

如果玩家数量较多，从Score集合开始聚合可避免全量玩家关联，性能更优：

import { connectToDB } from "@utils/database";
import Score from "@models/score";

export const GET = async (req) => {
    try {
        await connectToDB();

        const playerScores = await Score.aggregate([
            // 展开scores子数组
            { $unwind: "$scores" },
            // 按玩家ID分组计算总得分
            {
                $group: {
                    _id: "$scores.player",
                    totalScore: { $sum: "$scores.score" }
                }
            },
            // 关联Player集合获取基础信息
            {
                $lookup: {
                    from: "players",
                    localField: "_id",
                    foreignField: "_id",
                    as: "playerInfo"
                }
            },
            // 展开玩家信息数组
            { $unwind: "$playerInfo" },
            // 重构输出结构
            {
                $project: {
                    _id: "$_id",
                    name: "$playerInfo.name",
                    email: "$playerInfo.email",
                    totalScore: "$totalScore"
                }
            }
        ]);

        console.log('TRY: ', playerScores);

        return new Response(JSON.stringify(playerScores), { status: 200 });
    } catch (error) {
        return new Response("Failed to fetch player scores", { status: 500 });
    }
}

内容的提问来源于stack exchange，提问作者Jose Molina