How to use .aggregate to get data from multiple collections, including sum values with Mongoose + NextJS

I'm trying to return the data from 2 Schemas. One of those contain the info of 'Players', and the other one contain the info of the 'Scores' by game for each 'Player.

This is my '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;

This is my 'Score' schema: (note this one also contain a subdocuments content)

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;

This is an example of the actual documents saved in the Collections.

Players:

{
"_id":{"$oid":"652f3470729e8d8a59b221dd"},
"creator":{"$oid":"652af415729e8d8a59b2202f"},
"name":"Player 1",
"email":"[email protected]"
}

{
"_id":{"$oid":"652f36c9f6ad40c3aedf0cdf"},
"creator":{"$oid":"652af415729e8d8a59b2202f"},
"name":"Player 2",
"email":"[email protected]"
}

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

This is the data structure that I want to create as response:

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

My actual code to try to get that data structure is:

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

This is what console.log('TRY: ', players); returns:

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

May someone try to help me to understand what I'm missing there?

Thanks!

Solution

I think it would be easier to do your aggregate on the scores collection. Simply because that is where your scores.score fields is located so that is where you can perform the $sum. There could be more efficient ways but this query should return your desired results:

const players = await Scores.aggregate([
  {
    $unwind: {
      path: "$scores"
    }
  },
  {
    $group: {
      _id: "$scores.player",
      totalScore: {
        $sum: "$scores.score"
      }
    }
  },
  {
    $lookup: {
      from: "players",
      localField: "_id",
      foreignField: "_id",
      as: "fromPlayers"
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          {
            $arrayElemAt: [
              "$fromPlayers",
              0
            ]
          },
          "$$ROOT"
        ]
      }
    }
  },
  {
    $project: {
      fromPlayers: 0,
      creator: 0
    }
  }
])

See HERE for a working example.

Explanation:

$unwind : flatten the scores array so that we get one object per scores document. This obviously duplicates the documents but is easier to then group by player.
$group : group each object by player and $sum their scores.
$lookup : now join the players collection based on _id. But because $lookup returns an array, just assign to a new field called fromPlayers.
$replaceRoot : create a new root document by merging the fromPlayers array with the original document from previous pipeline stage.
$project : now remove that fromPlayers array and creator field from the ouput.