Get the sum after the $unwind and $lookup returns 0

Working with Data
1

Player collection:

{ "_id" : 1, "Name" : "John Aims", "Gender" : "M", "DoB" : ISODate("1990-01-01T00:00:00Z"), "Nationality" : "USA", "Hand" : "R", "YearTurnedPro" : 2010, "Tournament" : [ { "tournamentID" : 1, "TournamentYear" : 2016 }, { "tournamentID" : 2, "TournamentYear" : 2019 }, { "tournamentID" : 3, "TournamentYear" : 2021 } ] }
{ "_id" : 2, "Name" : "George Brown", "Gender" : "M", "DoB" : ISODate("1997-03-04T00:00:00Z"), "Nationality" : "GB", "Hand" : "L", "YearTurnedPro" : 2013, "Tournament" : [ { "tournamentID" : 2, "TournamentYear" : 2016 }, { "tournamentID" : 5, "TournamentYear" : 2019 } ] }

Tournament collection:

{ "_id" : ObjectId("626c18a3d880647a888888ff"), "TournamentID" : 1, "TournamentCode" : "GS1", "Position" : 8, "PrizeMoney" : 125000, "RankingPoints" : 250 }
{ "_id" : ObjectId("626c18c2d880647a888888ff"), "TournamentID" : 2, "TournamentCode" : "GS1", "Position" : 4, "PrizeMoney" : 250000, "RankingPoints" : 500 }
{ "_id" : ObjectId("626c18ddd880647a888888ff"), "TournamentID" : 3, "TournamentCode" : "GS1", "Position" : 1, "PrizeMoney" : 1000000, "RankingPoints" : 2000 }

1st Question:

Hello, I want to get the sum of ranking points of each player.

I have tried:

db.Player.aggregate([
  {"$unwind" : "$Tournament"}, 
{"$lookup":
{"from":"Tournament",
"localField":"Tournament.tournamentID",
"foreignField":"TournamentID",
"as":"Tennis-player"}},
  { "$group": {
    "_id": { Name:"$Name" },
    "total_qty": { "$sum": "$Tennis-player.PrizeMoney" }
  }}
])

But I get for every played the sum is 0.

I can show it on playground as it is using more than 1 collection.

2nd question:

Would it be better to create only 1 collections with all the data?

2 Likes
2

I clicked the :heart: because:

  1. you did supplied sample documents that we can cut-n-paste directly
  2. you did supplied what you tried
  3. you did indicate how it failed

Thanks

The only issue I see at first glance is that Tennis-player is an array and that you may need to use $reduce before you $sum.

1 Like