I'm not being able to find the average of a complex search in mongodb, using mongoose (nodejs) (and i'm not sure if its possible).
So, i have a collection of Scouts. A Scout is basically the vote of a user for a player, in a match (fixture). Scout items looks like this:
{
"_id": "6036dbbd148ccf17e82e6f14",
"player": "60243968d7ec0721344514ab",
"user": "601e9c826a339228e8f54305",
"fixture": "602435741f2e4e263492de66",
"score": 7,
"text": "Lorem ipsum",
"likes": 92,
"createdDate": "2021-02-24T23:05:33.176Z",
"__v": 0
},
{
"_id": "6036dbbd148ccf17e82e6f16",
"player": "60243968d7ec0721344514ab",
"user": "601e9c826a339228e8f54306",
"fixture": "602435741f2e4e263492de66",
"score": 1,
"text": "Lorem ipsum",
"likes": 76,
"createdDate": "2021-02-24T23:05:33.181Z",
"__v": 0
},
What i need is: the average score of a player in a fixture (among all users). And this is what i'm trying:
Scout.aggregate([
{ $match: {
$and: [
{player: mongoose.Types.ObjectId(req.params.playerId)},
{fixture: mongoose.Types.ObjectId(req.params.fixtureId)},
]}
},
{ $group: { _id: "$_id", "avgScore": { "$avg": "$score" }}}
])
But all i get as result is this (which is not the expected result - should be 4 as the avg):
{
"_id": "6036dbbd148ccf17e82e6f16",
"avgScore": 1
},
{
"_id": "6036dbbd148ccf17e82e6f14",
"avgScore": 7
}
]
Your aggregation is okay but you are regrouping on document by _id which will always be different, so do this,
{
$group: {
_id: null,
"avgScore": {
"$avg": "$score"
}
}
}
Test it here: https://mongoplayground.net/p/6Qt8E69Z2d6