MongoDB .- Create a field for each element of an array

Question

I have a collection of this type with some Premier League matches:

{"HomeTeam": "Chelsea", "HTScore": 2, "FTR": 'D', "AwayTeam": "Everton", "ATScore":2},
{"HomeTeam": "Chelsea", "HTScore": 3, "FTR": 'H', "AwayTeam": "Wolves", "ATScore":1},
{"HomeTeam": "Chelsea", "HTScore": 2, "FTR": 'D', "AwayTeam": "Everton", "ATScore":2},
.....
{"HomeTeam": "Liverpool", "HTScore": 1, "FTR": 'A', "AwayTeam": "Aston Villa", "ATScore":3}
{"HomeTeam": "Liverpool", "HTScore": 6, "FTR": 'H', "AwayTeam": "Chelsea", "ATScore":3}
etc.

where HomeTeam and AwayTeam represent both teams of the match, HTScore represents the HomeTeam score, ATScore represents de AwayTeam score and FTR represents full time result ('A': AwayTeam wins, 'D': Draw, 'H' : Home team wins)

I want to create a table of this type, which in the example set above would be like this

HomeTeam  | Number of Victories | Number of Draws | Number of Defeats | Max Goals scored in a Match
---------------------------------------------------------------------------------------
Chelsea   |         1           |        2        |         0         |          3
---------------------------------------------------------------------------------------
Liverpool |         1           |        0        |         1         |          6

Is there a way of creating those fields automatically by iterating over the values of FTR? Thank you very much in advance

Answer 1

You can use an aggregation pipeline and $group in this way:

• $group to get the home teams with their data. Here, for each one you add a new field called result where the final result is stored and another called goals with the number of goals scored.
• Then set how many "D", "H" or "A" there is in results, i.e. how many victories, loss or draws using $filter.
• And at least use $project to get the values you want. In this case the size of the arrays and max value of goals scored.

db.collection.aggregate([
  {
    "$group": {
      "_id": "$HomeTeam",
      "result": {
        "$push": "$FTR"
      },
      "goals": {
        "$push": "$HTScore"
      }
    }
  },
  {
    "$set": {
      "draws": {
        "$filter": {
          "input": "$result",
          "as": "d",
          "cond": {"$eq": ["$$d","D"]}
        }
      },
      "wins": {
        "$filter": {
          "input": "$result",
          "as": "w",
          "cond": {"$eq": ["$$w","H"]}
        }
      },
      "defeat": {
        "$filter": {
          "input": "$result",
          "as": "a",
          "cond": {"$eq": ["$$a","A"]}
        }
      }
    }
  },
  {
    "$project": {
      "HomeTeam": "$_id",
      "Number_of_victories": {"$size": "$wins"},
      "Number_of_draws": {"$size": "$draws"},
      "Number_of_defeats": {"$size": "$defeat"},
      "Max_goals": {"$max": "$goals"}
    }
  }
])

Example here