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

J.F. · Accepted Answer · 2021-01-21 12:00:25Z

2

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

edited Jan 21, 2021 at 12:00

answered Jan 21, 2021 at 11:57

J.F.

15.3k10 gold badges41 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gibbs Over a year ago

Good one. Taken more efforts to create data and show the output also.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related