MongoDB group and compute results from multiple fields within same document

Question

Can I have your help regarding MongoDB aggregation framework. I trying to build a Premier League Table from following collection of games:

{ 
    "_id" : ObjectId("5b39fec4b5f8df161d259f36"), 
    "gameWeek" : 1, 
    "homeTeam" : "Arsenal", 
    "awayTeam" : "Leicester", 
    "homeGoals" : 2, 
    "awayGoals" : 1 
}, { 
    "_id" : ObjectId("5b39ffc2b5f8df161d259f6d"), 
    "gameWeek" : 2, 
    "homeTeam" : "Arsenal", 
    "awayTeam" : "Sunderland", 
    "homeGoals" : 1, 
    "awayGoals" : 1 
}, { 
    "_id" : ObjectId("5b39ffe8b5f8df161d259f7f"), 
    "gameWeek" : 2, 
    "homeTeam" : "Sunderland", 
    "awayTeam" : "Manchester United", 
    "homeGoals" : 1, 
    "awayGoals" : 1 
}, { 
    "_id" : ObjectId("5b492cbea5aef964f0911cce"), 
    "gameWeek" : 1, 
    "homeTeam" : "Manchester United", 
    "awayTeam" : "Leicester", 
    "homeGoals" : 0, 
    "awayGoals" : 1 
}

I wish to get following results:

{ 
    "_id" : "Arsenal", 
    "team" : "Arsenal", 
    "gamesPlayed" : 2, 
    "goalsFor" : 3, 
    "goalsAgainst" : 2, 
    "goalsDifference" : 1, 
    "gamesWon" : 1, 
    "gamesDraw" : 1, 
    "gamesLost" : 0, 
    "points" : 4 
}, { 
    "_id" : "Leicester", 
    "team" : "Leicester", 
    "gamesPlayed" : 2, 
    "goalsFor" : 2, 
    "goalsAgainst" : 2, 
    "goalsDifference" : 0, 
    "gamesWon" : 1, 
    "gamesDraw" : 0, 
    "gamesLost" : 1, 
    "points" : 3 
}, { 
    "_id" : "Sunderland", 
    "team" : "Sunderland", 
    "gamesPlayed" : 2, 
    "goalsFor" : 2, 
    "goalsAgainst" : 2, 
    "goalsDifference" : 0, 
    "gamesWon" : 0, 
    "gamesDraw" : 2, 
    "gamesLost" : 0, 
    "points" : 2 
}, { 
    "_id" : "Manchester United", 
    "team" : "Manchester United", 
    "gamesPlayed" : 2, 
    "goalsFor" : 1, 
    "goalsAgainst" : 2, 
    "goalsDifference" : -1, 
    "gamesWon" : 0, 
    "gamesDraw" : 1, 
    "gamesLost" : 1, 
    "points" : 1 
}

where:

• gamesPlayed - total number of the games played,
• goalsFor - total goals made by team,
• goalsAgainst - total let in goals,
• goalsDifference - 'goalsFor' subtracting 'goalsAgainst'
• points - gets calculated scoring 3 points for each won game and 1 point for every draw game.

So far I have following query for building team standing by homeTeam results:

db.football_matches.aggregate([
    { 
        $group: { 
            _id:  "$homeTeam",  
            gamesPlayed : { $sum: NumberInt(1) }, 
            goalsFor: { $sum:  "$homeGoals"  }, 
            goalsAgainst: { $sum:  "$awayGoals" }, 
            gamesWon: { $sum: { $cond: { if: { $gt: [ "$homeGoals",  "$awayGoals" ]}, then: NumberInt(1), else: NumberInt(0) } }}, 
            gamesDraw: { $sum: { $cond: { if: { $eq: [ "$homeGoals",  "$awayGoals" ]}, then: NumberInt(1), else: NumberInt(0) } }},
            gamesLost: { $sum: { $cond: { if: { $lt: [ "$homeGoals",  "$awayGoals" ]}, then: NumberInt(1), else: NumberInt(0) } }}
        }
     }, { 
         $project: { 
             team:  "$_id" , 
             gamesPlayed:  "$gamesPlayed", 
             goalsFor:  "$goalsFor", 
             goalsAgainst:  "$goalsAgainst", 
             goalsDifference: { $subtract: [ "$goalsFor", "$goalsAgainst"] }, 
             gamesWon: "$gamesWon", 
             gamesDraw: "$gamesDraw",
             gamesLost: "$gamesLost",
             points: { $add: [ {$multiply: [ "$gamesWon", NumberInt(3)]}, {$multiply: [ "$gamesDraw", NumberInt(1)]} ]}
        }
     }, {
       $sort: { points: -1, goalsDifference: -1 }
    }
])

Theoretically I need to combine following grouping results with another similar group statement where similar action will be perform against awayTeam fields:

{
    $group: { 
        _id:  "$awayTeam",  
        gamesPlayed : { $sum: NumberInt(1) }, 
        goalsFor: { $sum:  "$awayGoals"  }, 
        goalsAgainst: { $sum:  "$homeGoals" }, 
        gamesWon: { $sum: { $cond: { if: { $gt: [ "$awayGoals",  "$homeGoals" ]}, then: NumberInt(1), else: NumberInt(0) } }}, 
        gamesDraw: { $sum: { $cond: { if: { $eq: [ "$awayGoals",  "$homeGoals" ]}, then: NumberInt(1), else: NumberInt(0) } }},
        gamesLost: { $sum: { $cond: { if: { $lt: [ "$awayGoals",  "$homeGoals" ]}, then: NumberInt(1), else: NumberInt(0) } }}
    }
 } 

How can I do that? Thank you very much. Please accept my apologies if similar question was asked before.

Answer 1

You can try below aggregation using $facet, $replaceRoot, $unwind, $concatArrays and finally with one more $group stage

db.collection.aggregate([
  { "$facet": {
    "first": [
      { "$group": {
        "_id": "$homeTeam",
        "gamesPlayed": { "$sum": 1 },
        "goalsFor": { "$sum": "$homeGoals" },
        "goalsAgainst": { "$sum": "$awayGoals" },
        "gamesWon": {
          "$sum": { "$cond": { "if": { "$gt": [ "$homeGoals", "$awayGoals" ] }, "then": 1, "else": 0 } }
        },
        "gamesDraw": {
          "$sum": { "$cond": { "if": { "$eq": [ "$homeGoals", "$awayGoals" ] }, "then": 1, "else": 0 } }
        },
        "gamesLost": {
          "$sum": { "$cond": { "if": { "$lt": [ "$homeGoals", "$awayGoals" ] }, "then": 1, "else": 0 } }
        }
      }},
      { "$project": {
        "team": "$_id",
        "gamesPlayed": "$gamesPlayed",
        "goalsFor": "$goalsFor",
        "goalsAgainst": "$goalsAgainst",
        "goalsDifference": { "$subtract": [ "$goalsFor", "$goalsAgainst" ] },
        "gamesWon": "$gamesWon",
        "gamesDraw": "$gamesDraw",
        "gamesLost": "$gamesLost",
        "points": { "$add": [{ "$multiply": [ "$gamesWon", 3 ] }, { "$multiply": [ "$gamesDraw", 1 ] }] }
      }},
      { "$sort": { "points": -1, "goalsDifference": -1 } }
    ],
    "second": [
      { "$group": {
        "_id": "$awayTeam",
        "gamesPlayed": { "$sum": 1 },
        "goalsFor": { "$sum": "$awayGoals" },
        "goalsAgainst": { "$sum": "$homeGoals" },
        "gamesWon": {
          "$sum": { "$cond": { "if": { "$gt": [ "$awayGoals", "$homeGoals" ] }, "then": 1, "else": 0 } }
        },
        "gamesDraw": {
          "$sum": { "$cond": { "if": { "$eq": [ "$awayGoals", "$homeGoals" ] }, "then": 1, "else": 0 } }
        },
        "gamesLost": {
          "$sum": { "$cond": { "if": { "$lt": [ "$awayGoals", "$homeGoals" ] }, "then": 1, "else": 0 } }
        }
      }},
      { "$project": {
        "team": "$_id",
        "gamesPlayed": "$gamesPlayed",
        "goalsFor": "$goalsFor",
        "goalsAgainst": "$goalsAgainst",
        "goalsDifference": { "$subtract": [ "$goalsFor", "$goalsAgainst" ] },
        "gamesWon": "$gamesWon",
        "gamesDraw": "$gamesDraw",
        "gamesLost": "$gamesLost",
        "points": { "$add": [{ "$multiply": [ "$gamesWon",3 ] }, { "$multiply": [ "$gamesDraw",1 ] } ] }
      }},
      { "$sort": { "points": -1, "goalsDifference": -1 } }
    ]
  }},
  { "$project": {
    "data": {
      "$concatArrays": [ "$first", "$second" ]
    }
  }},
  { "$unwind": "$data" },
  { "$replaceRoot": { "newRoot": "$data" } },
  { "$group": {
    "_id": "$_id",
    "gamesPlayed": { "$sum": "$gamesPlayed" },
    "goalsFor": { "$sum": "$goalsFor" },
    "goalsAgainst": { "$sum": "$goalsAgainst" },
    "gamesWon": { "$sum": "$gamesWon" },
    "gamesDraw": { "$sum": "$gamesDraw" },
    "gamesLost": { "$sum": "$gamesLost" }
  }}
])