aggregation framework - MongoDB: aggregating fields from arrays of subdocuments -
aggregation framework - MongoDB: aggregating fields from arrays of subdocuments -
i have mongodb collection called events, containing baseball game games. here illustration of 1 record in table:
class="lang-js prettyprint-override">{ "name" : "game# 814", "datestart" : isodate("2012-09-28t14:47:53.695z"), "_id" : objectid("53a1b24de3f25f4443d9747e"), "stats" : [ { "team" : objectid("53a11a43a8de6dd8375c940b"), "teamname" : "reds", "_id" : objectid("53a1b24de3f25f4443d97480"), "score" : 17 }, { "team" : objectid("53a11a43a8de6dd8375c938d"), "teamname" : "yankees", "_id" : objectid("53a1b24de3f25f4443d9747f"), "score" : 12 } ] "__v" : 0 } i need help writing query returns standings teams. result set should like:
class="lang-js prettyprint-override">{ "team" : objectid("53a11a43a8de6dd8375c938d"), "teamname" : "yankees", "wins" : <<number of yankees wins>> "losses" : <<number of yankees losses>> "draws" : <<number of yankees draws>> } { "team" : objectid("53a11a43a8de6dd8375c940b"), "teamname" : "reds", "wins" : <<number of reds wins>> "losses" : <<number of reds losses>> "draws" : <<number of reds draws>> } ... here's query i've started with...
class="lang-js prettyprint-override">db.events.aggregate( {"$unwind": "$stats" }, { $group : { _id : "$stats.team", gamesplayed : { $sum : 1}, totalscore : { $sum : "$stats.score" } }} ); ... returns results:
class="lang-js prettyprint-override">{ "result" : [ { "_id" : objectid("53a11a43a8de6dd8375c93cb"), "gamesplayed" : 125, // not requirement... trying $sum working "totalscore" : 1213 // ...same here }, { "_id" : objectid("53a11a44a8de6dd8375c955f"), "gamesplayed" : 128, "totalscore" : 1276 }, { "_id" : objectid("53a11a44a8de6dd8375c9661"), "gamesplayed" : 152, "totalscore" : 1509 }, ....
it seem advisable maintain "wins", "losses", "draws" within documents create or update them. possible aggregate if little long winded
class="lang-js prettyprint-override">db.events.aggregate([ // unwind "stats" array { "$unwind": "$stats" }, // combine document new fields { "$group": { "_id": "$_id", "firstteam": { "$first": "$stats.team" }, "firstteamname": { "$first": "$stats.teamname" }, "firstscore": { "$first": "$stats.score" }, "lastteam": { "$last": "$stats.team" }, "lastteamname": { "$last": "$stats.teamname" }, "lastscore": { "$last": "$stats.score" }, "minscore": { "$min": "$stats.score" }, "maxscore": { "$max": "$stats.score" } }}, // calculate comparing scores { "$project": { "firstteam": 1, "firstteamname": 1, "firstscore": 1, "lastteam": 1, "lastteamname": 1, "lastscore": 1, "firstwins": { "$cond": [ { "$gt": [ "$firstscore", "$lastscore" ] }, 1, 0 ] }, "firstlosses": { "$cond": [ { "$lt": [ "$firstscore", "$lastscore" ] }, 1, 0 ] }, "firstdraws": { "$cond": [ { "$eq": [ "$firstscore", "$lastscore" ] }, 1, 0 ] }, "lastwins": { "$cond": [ { "$gt": [ "$lastscore", "$firstscore" ] }, 1, 0 ] }, "lastlosses": { "$cond": [ { "$lt": [ "$lastscore", "$firstscore" ] }, 1, 0 ] }, "lastdraws": { "$cond": [ { "$eq": [ "$lastscore", "$firstscore" ] }, 1, 0 ] }, "type": { "$literal": [ true, false ] } }}, // unwind "type" { "$unwind": "$type" }, // grouping teams conditionally on "type" { "$group": { "_id": { "team": { "$cond": [ "$type", "$firstteam", "$lastteam" ] }, "teamname": { "$cond": [ "$type", "$firstteamname", "$lastteamname" ] } }, "owins": { "$sum": { "$cond": [ "$type", "$firstwins", "$lastwins" ] } }, "olosses": { "$sum": { "$cond": [ "$type", "$firstlosses", "$lastlosses" ] } }, "odraws": { "$sum": { "$cond": [ "$type", "$firstdraws", "$lastdraws" ] } } }}, // project final form { "$project": { "_id": 0, "team": "$_id.team", "teamname": "$_id.teamname", "wins": "$owins", "losses": "$olosses", "draws": "$odraws" }} ]) the first part "re-shape" document unwinding array , grouping "first" , "last" defining fields 2 teams.
then want $project through documents , calculate "wins", "losses" , "draws" each team in pairing. additional thing adding array field 2 values true/false convenient here. if on pre 2.6 version of mongodb $literal can replaced $const not documented same thing.
once $unwind "type" array, documents can split apart in $group stage evaluating whether take "first" or "last" team field values via utilize of $cond. ternary operator evaluates true/false status , returns appropriate value according condition.
with final $project documents formed how want.
mongodb aggregation-framework
Comments
Post a Comment