2

I have a json file that has some football results. I need to calculate which team won the most away games, and how many wins there were.

My query is this:

db.football.aggregate(
{"$unwind": "$rounds"},
{"$unwind": "$rounds.matches"}, 

{"$project":
    {
        //"rounds.matches.team2.name":1,
        away_team: "$rounds.matches.team2.name",
        winner_away:
            { $cond: {if: {$gt:["$rounds.matches.score2","$rounds.matches.score1"] }, then:1,else :0} },
        _id:0   
    }
},
{"$group": {
    _id: {team_name:"$away_team"},
    total_wins: {$sum:"$winner_away"}
    }
}
)

and what I get is this:

{ "_id" : { "team_name" : "Stoke City" }, "total_wins" : 6 }
{ "_id" : { "team_name" : "Newcastle United" }, "total_wins" : 2 }
{ "_id" : { "team_name" : "Chelsea" }, "total_wins" : 7 }
{ "_id" : { "team_name" : "Everton" }, "total_wins" : 5 }
{ "_id" : { "team_name" : "Bournemouth" }, "total_wins" : 6 }
{ "_id" : { "team_name" : "Manchester United" }, "total_wins" : 7 }
{ "_id" : { "team_name" : "Liverpool" }, "total_wins" : 8 }
{ "_id" : { "team_name" : "Manchester City" }, "total_wins" : 7 }
{ "_id" : { "team_name" : "Leicester City" }, "total_wins" : 11 }

If I add this to get just team's with more wins name, and number of wins

{"$group": {
        _id: "$team_name",
        max_wins: {$max: "$total_wins"}
        }
    }   

I get this

{ "_id" : null, "max_wins" : 11 }

Can someone tell me how to get the name as well? I can do it using sort, limit but this will not work in case of two or more teams having equal number of wins.

1

what I did was this: exported to a new collection temp and then, grouped by total_winds, sorted by ID and took first elements. It returns not only the very first, but all those being equal.

  db.temp.aggregate([
  {$group: {
      _id: "$total_wins",
      my_winner: {$push: "$$ROOT"}
  }},
  {$sort:{_id:-1}},
  {$limit:1}  ]).pretty()
0

The second $group stage is not getting the team name because the previous $group stage put it as a subfield inside the _id field. You can change the second $group stage to get the team name, like this:

{"$group": {
    _id: {team_name:"$away_team"},
    total_wins: {$sum:"$winner_away"}
    }
}
,
{"$group": {
    _id: "$_id.team_name",
    max_wins: {$max: "$total_wins"}
    }
}

However, this will not solve the problem of finding the teams with the most wins, as it will still be giving you the full list of team, each with their total_wins count.

  • thanks for replying. I have already tried it. You are right, this does not add anything to solving my problem. – Tom Zinger Feb 28 '17 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.