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
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.$filter.$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