patternMinor
Return document that has $max value in specific field using mongodb
Viewed 0 times
fieldreturnmongodbvaluehasmaxthatdocumentusingspecific
Problem
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:
and what I get is this:
If I add this to get just team's with more wins name, and number of wins
I get this
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.
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.
Solution
what I did was this:
exported to a new collection
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()Code Snippets
db.temp.aggregate([
{$group: {
_id: "$total_wins",
my_winner: {$push: "$$ROOT"}
}},
{$sort:{_id:-1}},
{$limit:1} ]).pretty()Context
StackExchange Database Administrators Q#165724, answer score: 2
Revisions (0)
No revisions yet.