Sum document fields where the reference id is equal

user_13570001 0
Viewed 52

I have these documents with the following structure:

{
    "_id" : ObjectId("60c42bc26296623b0056515a"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565153")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565155")
    ],
    "goals_favor" : 5,
    "goals_against" : 2,
    "goals_difference" : 3,
    "matches_played" : 1,
    "won_matches" : 1,
    "tied_matches" : 0,
    "lost_matches" : 0,
    "points" : 3
}

{
    "_id" : ObjectId("60c42bc26296623b0056515b"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565154")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565155")
    ],
    "goals_favor" : 2,
    "goals_against" : 5,
    "goals_difference" : -3,
    "matches_played" : 1,
    "won_matches" : 0,
    "tied_matches" : 0,
    "lost_matches" : 1,
    "points" : 0
}

{
    "_id" : ObjectId("60c4eb5aaa5d6523c83f59c0"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565153")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565156")
    ],
    "goals_favor" : 5,
    "goals_against" : 2,
    "goals_difference" : 3,
    "matches_played" : 1,
    "won_matches" : 1,
    "tied_matches" : 0,
    "lost_matches" : 0,
    "points" : 3
}

{
    "_id" : ObjectId("60c4eb5aaa5d6523c83f59c1"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565154")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565156")
    ],
    "goals_favor" : 2,
    "goals_against" : 5,
    "goals_difference" : -3,
    "matches_played" : 1,
    "won_matches" : 0,
    "tied_matches" : 0,
    "lost_matches" : 1,
    "points" : 0
}

{
    "_id" : ObjectId("60c4eb99aa5d6523c83f59c4"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565153")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565157")
    ],
    "goals_favor" : 5,
    "goals_against" : 2,
    "goals_difference" : 3,
    "matches_played" : 1,
    "won_matches" : 1,
    "tied_matches" : 0,
    "lost_matches" : 0,
    "points" : 3
}

{
    "_id" : ObjectId("60c4eb99aa5d6523c83f59c5"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565154")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565157")
    ],
    "goals_favor" : 2,
    "goals_against" : 5,
    "goals_difference" : -3,
    "matches_played" : 1,
    "won_matches" : 0,
    "tied_matches" : 0,
    "lost_matches" : 1,
    "points" : 0
}

I'm trying to get all the docs with the goals_favor, goals_against, goals_difference, match_played, won_matches, tied_matches, lost_matches, and points summed fields, where the reference IDs in the team field are the same, note that there are three documents with the following id in the team field '60c42acf6296623b00565153' and the remaining three with the id '60c42acf6296623b00565154', so I would like a structure similar to the following:

{
    "_id" : ObjectId("60c42bc26296623b0056515a"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565153")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565155")
    ],
    "goals_favor" : 15,
    "goals_against" : 6,
    "goals_difference" : 9,
    "matches_played" : 3,
    "won_matches" : 3,
    "tied_matches" : 0,
    "lost_matches" : 0,
    "points" : 9
}

{
    "_id" : ObjectId("60c42bc26296623b0056515b"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565154")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565155")
    ],
    "goals_favor" : 6,
    "goals_against" : 15,
    "goals_difference" : -9,
    "matches_played" : 3,
    "won_matches" : 0,
    "tied_matches" : 0,
    "lost_matches" : 3,
    "points" : 0
}

I have tried this but cannot follow:

const ids = ['60c42acf6296623b00565153', '60c42acf6296623b00565154']

TeamsDetails.aggregate([
                        { $match: { "team": { $in: ids } } },
                        { $group: { _id: "$_id", total: { $sum: "$points" } } },
                    ]).exec((error, find) => {
                        //Do something
                    })

Thank you.

mongoose node.js mongodb
1 Answers

You need to use $unwind to deconstruct the array and group by team

db.collection.aggregate([
  // match stage
  {
    $unwind: "$team"
  },
  {
    "$group": {
      "_id": "$team",
      "points": {
        "$sum": "$points"
      },
      goals_favor: {
        $sum: "$goals_favor"
      }
    }
  }
])

Working Mongo playground

user_79757710
Related