MongoDB如何聚合集合子数组对象并关联其他集合中的实际对象？

要实现将Teams集合中player子数组的对象关联到Players集合的完整球员信息，你可以通过以下聚合管道完成：

示例集合结构 #

Teams集合

const Teams = [
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a1',
        teamName: 'World XI A',
        totalPlayers: 15,
        player: [
            {
                playerId: '60f3b0b3f0b9a1b0b4f9b1a2',
                playerName: 'Player A',
                score: 10,
            },
            {
                playerId: '60f3b0b3f0b9a1b0b4f9b1a3',
                playerName: 'Player B',
                score: 20,
            },
        ],
    },
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a4',
        teamName: 'Crackers',
        totalPlayers: 15,
        player: [
            {
                playerId: '60f3b0b3f0b9a1b0b4f9b1a5',
                playerName: 'Player C',
                score: 30,
            },
            {
                playerId: '60f3b0b3f0b9a1b0b4f9b1a6',
                playerName: 'Player D',
                score: 40,
            },
        ],
    },
];

Players集合

const Players = [
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a2',
        playerName: 'Player A',
        rank: 2,
        totalRuns: 5000,
    },
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a3',
        playerName: 'Player B',
        rank: 1,
        totalRuns: 3050,
    },
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a5',
        playerName: 'Player C',
        rank: 3,
        totalRuns: 2050,
    },
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a6',
        playerName: 'Player D',
        rank: 4,
        totalRuns: 1000,
    },
];

聚合管道实现 #

db.Teams.aggregate([
    // 展开player数组，让每个球员对象成为独立文档
    { $unwind: "$player" },
    // 关联Players集合，通过playerId匹配_id
    {
        $lookup: {
            from: "Players",
            localField: "player.playerId",
            foreignField: "_id",
            as: "player.playerInfo"
        }
    },
    // 展开playerInfo数组（每个匹配只会返回一个球员，所以展开后得到单个对象）
    { $unwind: "$player.playerInfo" },
    // 按球队_id重新分组，重组player数组，保留球队其他字段
    {
        $group: {
            _id: "$_id",
            teamName: { $first: "$teamName" },
            totalPlayers: { $first: "$totalPlayers" },
            player: { $push: "$player" }
        }
    },
    // 可选：调整字段顺序并移除不需要的playerId字段
    {
        $project: {
            _id: 1,
            teamName: 1,
            totalPlayers: 1,
            player: {
                playerInfo: 1,
                playerName: 1,
                score: 1
            }
        }
    }
])

管道阶段说明 #

• $unwind: 把Teams文档中的player数组拆分成多个独立文档，每个文档对应一个球员，确保能单独关联Players集合。
• $lookup: 执行左连接，从Players集合中匹配player.playerId等于_id的文档，将匹配结果存入player.playerInfo字段。
• $unwind: 由于$lookup返回的是数组类型，而每个球员只会匹配到一个Players文档，因此展开该数组得到单个对象。
• $group: 按球队的_id重新聚合文档，将拆分后的球员对象重新组合成player数组，同时用$first保留球队的固定字段。
• $project: 可选阶段，用来调整输出字段结构，比如移除不需要的playerId，让结果和预期一致。

最终执行结果 #

const result = [
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a1',
        teamName: 'World XI A',
        totalPlayers: 15,
        player: [
            {
                playerInfo: {
                    _id: '60f3b0b3f0b9a1b0b4f9b1a2',
                    playerName: 'Player A',
                    rank: 2,
                    totalRuns: 5000,
                },
                playerName: 'Player A',
                score: 10,
            },
            {
                playerInfo: {
                    _id: '60f3b0b3f0b9a1b0b4f9b1a3',
                    playerName: 'Player B',
                    rank: 1,
                    totalRuns: 3050,
                },
                playerName: 'Player B',
                score: 20,
            },
        ],
    },
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a4',
        teamName: 'Crackers',
        totalPlayers: 15,
        player: [
            {
                playerInfo: {
                    _id: '60f3b0b3f0b9a1b0b4f9b1a5',
                    playerName: 'Player C',
                    rank: 3,
                    totalRuns: 2050,
                },
                playerName: 'Player C',
                score: 30,
            },
            {
                playerInfo: {
                    _id: '60f3b0b3f0b9a1b0b4f9b1a6',
                    playerName: 'Player D',
                    rank: 4,
                    totalRuns: 1000,
                },
                playerName: 'Player D',
                score: 40,
            },
        ],
    },
];

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