mongodb - Conditionally Aggregate Against Choice of Pairs : Home or Away -
i'm looking best way aggregate soccer matches aggregate table display qualification table. have data looks this:
/* 1 */ { "_id" : objectid("5976b6e1f42aa4d69585f4fb"), "home" : objectid("596fe03ad496e047d6314bf7"), "away" : objectid("596fe03ad496e047d6314be8"), "homegoals" : 4, "awaygoals" : 1, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b3"), "date" : isodate("2017-01-28t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 2 */ { "_id" : objectid("5976b6e1f42aa4d69585f4fd"), "home" : objectid("596fe03ad496e047d6314be9"), "away" : objectid("596fe03ad496e047d6314c0d"), "homegoals" : 0, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b3"), "date" : isodate("2017-01-29t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 3 */ { "_id" : objectid("5976b6e1f42aa4d69585f502"), "home" : objectid("596fe03ad496e047d6314bed"), "away" : objectid("596fe03ad496e047d6314c0f"), "homegoals" : 4, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b3"), "date" : isodate("2017-05-18t07:00:00.000z"), "notes" : "", "__v" : 0 } /* 4 */ { "_id" : objectid("5976b6e1f42aa4d69585f4fe"), "home" : objectid("596fe03ad496e047d6314c13"), "away" : objectid("596fe03ad496e047d6314bee"), "homegoals" : 1, "awaygoals" : 1, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b3"), "date" : isodate("2017-01-29t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 5 */ { "_id" : objectid("5976b6e1f42aa4d69585f503"), "home" : objectid("596fe03ad496e047d6314c1c"), "away" : objectid("596fe03ad496e047d6314be9"), "homegoals" : 1, "awaygoals" : 3, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b4"), "date" : isodate("2017-02-03t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 6 */ { "_id" : objectid("5976b6e0f42aa4d69585f4fa"), "home" : objectid("596fe03ad496e047d6314c30"), "away" : objectid("596fe03ad496e047d6314bfe"), "homegoals" : 1, "awaygoals" : 2, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b3"), "date" : isodate("2017-01-28t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 7 */ { "_id" : objectid("5976b6e1f42aa4d69585f4ff"), "home" : objectid("596fe03ad496e047d6314c21"), "away" : objectid("596fe03ad496e047d6314bf4"), "homegoals" : 0, "awaygoals" : 2, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b3"), "date" : isodate("2017-01-29t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 8 */ { "_id" : objectid("5976b6e1f42aa4d69585f4fc"), "home" : objectid("596fe03ad496e047d6314bdb"), "away" : objectid("596fe03ad496e047d6314bf6"), "homegoals" : 1, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b3"), "date" : isodate("2017-01-28t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 9 */ { "_id" : objectid("5976b6e1f42aa4d69585f500"), "home" : objectid("596fe03ad496e047d6314c2d"), "away" : objectid("596fe03ad496e047d6314be0"), "homegoals" : 3, "awaygoals" : 1, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b3"), "date" : isodate("2017-01-29t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 10 */ { "_id" : objectid("5976b6e2f42aa4d69585f504"), "home" : objectid("596fe03ad496e047d6314bf4"), "away" : objectid("596fe03ad496e047d6314c30"), "homegoals" : 1, "awaygoals" : 2, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b4"), "date" : isodate("2017-02-03t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 11 */ { "_id" : objectid("5976b6e1f42aa4d69585f501"), "home" : objectid("596fe03ad496e047d6314bfa"), "away" : objectid("596fe03ad496e047d6314c1c"), "homegoals" : 1, "awaygoals" : 1, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b3"), "date" : isodate("2017-03-25t07:00:00.000z"), "notes" : "", "__v" : 0 } /* 12 */ { "_id" : objectid("5976b6e2f42aa4d69585f505"), "home" : objectid("596fe03ad496e047d6314be0"), "away" : objectid("596fe03ad496e047d6314c13"), "homegoals" : 0, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b4"), "date" : isodate("2017-02-05t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 13 */ { "_id" : objectid("5976b6e2f42aa4d69585f506"), "home" : objectid("596fe03ad496e047d6314be0"), "away" : objectid("596fe03ad496e047d6314bee"), "homegoals" : 2, "awaygoals" : 1, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b5"), "date" : isodate("2017-02-11t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 14 */ { "_id" : objectid("5976b6e2f42aa4d69585f507"), "home" : objectid("596fe03ad496e047d6314c13"), "away" : objectid("596fe03ad496e047d6314c30"), "homegoals" : 3, "awaygoals" : 4, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b5"), "date" : isodate("2017-02-12t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 15 */ { "_id" : objectid("5976b6e2f42aa4d69585f50c"), "home" : objectid("596fe03ad496e047d6314be8"), "away" : objectid("596fe03ad496e047d6314bfa"), "homegoals" : 1, "awaygoals" : 2, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b4"), "date" : isodate("2017-02-04t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 16 */ { "_id" : objectid("5976b6e2f42aa4d69585f511"), "home" : objectid("596fe03ad496e047d6314be0"), "away" : objectid("596fe03ad496e047d6314bf4"), "homegoals" : 0, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b7"), "date" : isodate("2017-02-23t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 17 */ { "_id" : objectid("5976b6e2f42aa4d69585f516"), "home" : objectid("596fe03ad496e047d6314be9"), "away" : objectid("596fe03ad496e047d6314bfa"), "homegoals" : 1, "awaygoals" : 2, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b5"), "date" : isodate("2017-02-12t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 18 */ { "_id" : objectid("5976b6e2f42aa4d69585f50a"), "home" : objectid("596fe03ad496e047d6314c13"), "away" : objectid("596fe03ad496e047d6314c0f"), "homegoals" : 4, "awaygoals" : 3, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b7"), "date" : isodate("2017-02-25t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 19 */ { "_id" : objectid("5976b6e2f42aa4d69585f50b"), "home" : objectid("596fe03ad496e047d6314bf7"), "away" : objectid("596fe03ad496e047d6314bdb"), "homegoals" : 0, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b8"), "date" : isodate("2017-03-04t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 20 */ { "_id" : objectid("5976b6e2f42aa4d69585f51b"), "home" : objectid("596fe03ad496e047d6314be8"), "away" : objectid("596fe03ad496e047d6314bf6"), "homegoals" : 2, "awaygoals" : 2, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b8"), "date" : isodate("2017-03-05t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 21 */ { "_id" : objectid("5976b6e2f42aa4d69585f50f"), "home" : objectid("596fe03ad496e047d6314bee"), "away" : objectid("596fe03ad496e047d6314c1c"), "homegoals" : 0, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b5"), "date" : isodate("2017-02-13t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 22 */ { "_id" : objectid("5976b6e2f42aa4d69585f520"), "home" : objectid("596fe03ad496e047d6314bfa"), "away" : objectid("596fe03ad496e047d6314bf7"), "homegoals" : 2, "awaygoals" : 2, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b6"), "date" : isodate("2017-02-19t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 23 */ { "_id" : objectid("5976b6e2f42aa4d69585f514"), "home" : objectid("596fe03ad496e047d6314bfe"), "away" : objectid("596fe03ad496e047d6314c21"), "homegoals" : 0, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b4"), "date" : isodate("2017-02-05t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 24 */ { "_id" : objectid("5976b6e2f42aa4d69585f510"), "home" : objectid("596fe03ad496e047d6314c0d"), "away" : objectid("596fe03ad496e047d6314be0"), "homegoals" : 0, "awaygoals" : 2, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b6"), "date" : isodate("2017-02-19t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 25 */ { "_id" : objectid("5976b6e2f42aa4d69585f519"), "home" : objectid("596fe03ad496e047d6314bf6"), "away" : objectid("596fe03ad496e047d6314c30"), "homegoals" : 1, "awaygoals" : 3, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b7"), "date" : isodate("2017-02-25t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 26 */ { "_id" : objectid("5976b6e2f42aa4d69585f515"), "home" : objectid("596fe03ad496e047d6314c2d"), "away" : objectid("596fe03ad496e047d6314c0f"), "homegoals" : 1, "awaygoals" : 1, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b5"), "date" : isodate("2017-02-10t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 27 */ { "_id" : objectid("5976b6e2f42aa4d69585f51e"), "home" : objectid("596fe03ad496e047d6314c21"), "away" : objectid("596fe03ad496e047d6314be8"), "homegoals" : 1, "awaygoals" : 2, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b5"), "date" : isodate("2017-02-12t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 28 */ { "_id" : objectid("5976b6e2f42aa4d69585f51a"), "home" : objectid("596fe03ad496e047d6314c2d"), "away" : objectid("596fe03ad496e047d6314bfe"), "homegoals" : 1, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b7"), "date" : isodate("2017-02-27t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 29 */ { "_id" : objectid("5976b6e2f42aa4d69585f51f"), "home" : objectid("596fe03ad496e047d6314c30"), "away" : objectid("596fe03ad496e047d6314bdb"), "homegoals" : 2, "awaygoals" : 2, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b6"), "date" : isodate("2017-02-17t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 30 */ { "_id" : objectid("5976b6e2f42aa4d69585f508"), "home" : objectid("596fe03ad496e047d6314bfe"), "away" : objectid("596fe03ad496e047d6314c13"), "homegoals" : 0, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b6"), "date" : isodate("2017-02-19t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 31 */ { "_id" : objectid("5976b6e2f42aa4d69585f50d"), "home" : objectid("596fe03ad496e047d6314bee"), "away" : objectid("596fe03ad496e047d6314bf7"), "homegoals" : 1, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b4"), "date" : isodate("2017-02-06t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 32 */ { "_id" : objectid("5976b6e2f42aa4d69585f512"), "home" : objectid("596fe03ad496e047d6314c21"), "away" : objectid("596fe03ad496e047d6314bfa"), "homegoals" : 0, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b7"), "date" : isodate("2017-02-26t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 33 */ { "_id" : objectid("5976b6e2f42aa4d69585f517"), "home" : objectid("596fe03ad496e047d6314c0f"), "away" : objectid("596fe03ad496e047d6314c21"), "homegoals" : 4, "awaygoals" : 1, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b6"), "date" : isodate("2017-02-18t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 34 */ { "_id" : objectid("5976b6e2f42aa4d69585f51c"), "home" : objectid("596fe03ad496e047d6314c0f"), "away" : objectid("596fe03ad496e047d6314bdb"), "homegoals" : 2, "awaygoals" : 1, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b4"), "date" : isodate("2017-02-04t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 35 */ { "_id" : objectid("5976b6e2f42aa4d69585f521"), "home" : objectid("596fe03ad496e047d6314bf7"), "away" : objectid("596fe03ad496e047d6314be9"), "homegoals" : 2, "awaygoals" : 1, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b7"), "date" : isodate("2017-02-24t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 36 */ { "_id" : objectid("5976b6e2f42aa4d69585f509"), "home" : objectid("596fe03ad496e047d6314bee"), "away" : objectid("596fe03ad496e047d6314c2d"), "homegoals" : 1, "awaygoals" : 1, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b6"), "date" : isodate("2017-02-20t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 37 */ { "_id" : objectid("5976b6e2f42aa4d69585f50e"), "home" : objectid("596fe03ad496e047d6314bf7"), "away" : objectid("596fe03ad496e047d6314bfe"), "homegoals" : 1, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b5"), "date" : isodate("2017-02-12t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 38 */ { "_id" : objectid("5976b6e2f42aa4d69585f513"), "home" : objectid("596fe03ad496e047d6314c0f"), "away" : objectid("596fe03ad496e047d6314c0d"), "homegoals" : 2, "awaygoals" : 1, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b8"), "date" : isodate("2017-03-04t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 39 */ { "_id" : objectid("5976b6e2f42aa4d69585f518"), "home" : objectid("596fe03ad496e047d6314c1c"), "away" : objectid("596fe03ad496e047d6314bf6"), "homegoals" : 0, "awaygoals" : 3, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b6"), "date" : isodate("2017-02-19t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 40 */ { "_id" : objectid("5976b6e2f42aa4d69585f51d"), "home" : objectid("596fe03ad496e047d6314bf6"), "away" : objectid("596fe03ad496e047d6314bed"), "homegoals" : 0, "awaygoals" : 4, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b4"), "date" : isodate("2017-04-05t07:00:00.000z"), "notes" : "", "__v" : 0 } /* 41 */ { "_id" : objectid("5976b6e2f42aa4d69585f522"), "home" : objectid("596fe03ad496e047d6314bdb"), "away" : objectid("596fe03ad496e047d6314c1c"), "homegoals" : 6, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b7"), "date" : isodate("2017-02-27t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 42 */ { "_id" : objectid("5976b6e2f42aa4d69585f523"), "home" : objectid("596fe03ad496e047d6314bfa"), "away" : objectid("596fe03ad496e047d6314bed"), "homegoals" : 1, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b8"), "date" : isodate("2017-03-05t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 43 */ { "_id" : objectid("5976b6e2f42aa4d69585f524"), "home" : objectid("596fe03ad496e047d6314bdb"), "away" : objectid("596fe03ad496e047d6314be8"), "homegoals" : 1, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b9"), "date" : isodate("2017-03-11t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 44 */ { "_id" : objectid("5976b6e2f42aa4d69585f528"), "home" : objectid("596fe03ad496e047d6314bed"), "away" : objectid("596fe03ad496e047d6314be9"), "homegoals" : 1, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b9"), "date" : isodate("2017-03-12t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 45 */ { "_id" : objectid("5976b6e2f42aa4d69585f526"), "home" : objectid("596fe03ad496e047d6314bf6"), "away" : objectid("596fe03ad496e047d6314bfa"), "homegoals" : 1, "awaygoals" : 2, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b9"), "date" : isodate("2017-03-11t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 46 */ { "_id" : objectid("5976b6e2f42aa4d69585f525"), "home" : objectid("596fe03ad496e047d6314bf4"), "away" : objectid("596fe03ad496e047d6314c2d"), "homegoals" : 2, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b8"), "date" : isodate("2017-03-22t07:00:00.000z"), "notes" : "", "__v" : 0 } /* 47 */ { "_id" : objectid("5976b6e2f42aa4d69585f529"), "home" : objectid("596fe03ad496e047d6314bfe"), "away" : objectid("596fe03ad496e047d6314be0"), "homegoals" : 1, "awaygoals" : 1, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b8"), "date" : isodate("2017-03-05t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 48 */ { "_id" : objectid("5976b6e2f42aa4d69585f52b"), "home" : objectid("596fe03ad496e047d6314c21"), "away" : objectid("596fe03ad496e047d6314bf7"), "homegoals" : 2, "awaygoals" : 1, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b9"), "date" : isodate("2017-03-12t08:00:00.000z"), "notes" : "", "__v" : 0 } /* 49 */ { "_id" : objectid("5976b6e2f42aa4d69585f527"), "home" : objectid("596fe03ad496e047d6314c30"), "away" : objectid("596fe03ad496e047d6314bee"), "homegoals" : 3, "awaygoals" : 0, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b8"), "date" : isodate("2017-05-03t07:00:00.000z"), "notes" : "", "__v" : 0 } /* 50 */ { "_id" : objectid("5976b6e2f42aa4d69585f52a"), "home" : objectid("596fe03ad496e047d6314be9"), "away" : objectid("596fe03ad496e047d6314c21"), "homegoals" : 1, "awaygoals" : 2, "tournament" : objectid("59726e597ec27162718ff90b"), "stage" : objectid("59726e597ec27162718ff9b8"), "date" : isodate("2017-03-23t07:00:00.000z"), "notes" : "", "__v" : 0 } home , away ids of teams, homegoals , awaygoals score of match. main players on query need write. in matches, team home team, in others team away team. need aggregate table so:
team | games played | games won | games lost | games drawn | goals | goals against | goal differential | points ------ | ------------ | --------- | ---------- | ----------- | --------- | ------------- | ----------------- | ------ team | 10 | 6 | 3 | 1 | 10 | 3 | 7 | 21 team b | 10 | 5 | 3 | 2 | 11 | 5 | 6 | 18 i need way combine teams id, no matter if home or away teams, sum goals, , calculate differential.
what way achieve mongoose query, without having query , using reduce or that.
any appreciated. in advance.
personally "pre-aggregate" results per team , update them after each match complete, incrementing each of counters appropriate. there still nothing structurally wrong here, , it's matter of treating "each game" separate record each team.
this done applying $map each of teams, , selecting appropriate "home" or "away" values according 1 being processed. main "friend" here $cond "ternary" expression, allows selectively return values:
db.games.aggregate([ { "$project": { "teams": { "$map": { "input": ["$home","$away"], "as": "t", "in": { "team": "$$t", "played": 1, "win": { "$cond": { "if": { "$or": [ { "$and": [ { "$eq": [ "$$t", "$home" ] }, { "$gt": [ "$homegoals", "$awaygoals" ] } ]}, { "$and": [ { "$eq": [ "$$t", "$away" ] }, { "$gt": [ "$awaygoals", "$homegoals" ] } ]} ] }, "then": 1, "else": 0 } }, "lost": { "$cond": { "if": { "$or": [ { "$and": [ { "$eq": [ "$$t", "$home" ] }, { "$lt": [ "$homegoals", "$awaygoals" ] } ]}, { "$and": [ { "$eq": [ "$$t", "$away" ] }, { "$lt": [ "$awaygoals", "$homegoals" ] } ]} ] }, "then": 1, "else": 0 } }, "draw": { "$cond": { "if": { "$eq": ["$homegoals","$awaygoals"] }, "then": 1, "else": 0 } }, "goalsfor": { "$cond": { "if": { "$eq": [ "$$t", "$home" ] }, "then": "$homegoals", "else": "$awaygoals" } }, "goalsagainst": { "$cond": { "if": { "$eq": [ "$$t", "$away" ] }, "then": "$homegoals", "else": "$awaygoals" } } } } } }}, { "$unwind": "$teams" }, { "$group": { "_id": "$teams.team", "played": { "$sum": "$teams.played" }, "win": { "$sum": "$teams.win" }, "lost": { "$sum": "$teams.lost" }, "draw": { "$sum": "$teams.draw" }, "goalsfor": { "$sum": "$teams.goalsfor" }, "goalsagainst": { "$sum": "$teams.goalsagainst" } }}, { "$addfields": { "goaldifferential": { "$abs": { "$subtract": [ "$goalsfor", "$goalsagainst" ] } }, "points": { "$add": [ { "$multiply": [ "$win", 3 ] }, "$draw" ] } }}, { "$sort": { "points": -1, "goaldifferential": 1 } } ]) so if @ "purely" initial $project $map operation , different fields being projected in array, each "game" going transformed like:
{ "_id" : objectid("5976b6e1f42aa4d69585f4fb"), "teams" : [ { "team" : objectid("596fe03ad496e047d6314bf7"), "played" : 1.0, "win" : 1.0, "lost" : 0.0, "draw" : 0.0, "goalsfor" : 4.0, "goalsagainst" : 1.0 }, { "team" : objectid("596fe03ad496e047d6314be8"), "played" : 1.0, "win" : 0.0, "lost" : 1.0, "draw" : 0.0, "goalsfor" : 1.0, "goalsagainst" : 4.0 } ] } which turns each game "two" sets of results, being set of results perspective of "each team".
from there it's pretty standard fare, $unwind array in order $group each "team" identifier, accumulating results.
for additional fields, can calculate these "after" grouping has been done, or include them in initial projection , grouping appropriate. interpretation here applies 3 points "win" , 1 each "draw", , considers "absolute" difference in order apply $sort ranking leaderboard, , give separation on teams same "points".
of course can whatever want, main principles here using $map in order create "array" of results each participating team in each game, , using $cond discern between "home" , "away" , record appropriate values.
the overall results on sample of 50 games provided includes 18 distinct teams in output as:
/* 1 */ { "_id" : objectid("596fe03ad496e047d6314bfa"), "played" : 7.0, "win" : 4.0, "lost" : 0.0, "draw" : 3.0, "goalsfor" : 10.0, "goalsagainst" : 6.0, "goaldifferential" : 4.0, "points" : 15.0 } /* 2 */ { "_id" : objectid("596fe03ad496e047d6314c30"), "played" : 6.0, "win" : 4.0, "lost" : 1.0, "draw" : 1.0, "goalsfor" : 15.0, "goalsagainst" : 9.0, "goaldifferential" : 6.0, "points" : 13.0 } /* 3 */ { "_id" : objectid("596fe03ad496e047d6314bf7"), "played" : 7.0, "win" : 3.0, "lost" : 2.0, "draw" : 2.0, "goalsfor" : 10.0, "goalsagainst" : 7.0, "goaldifferential" : 3.0, "points" : 11.0 } /* 4 */ { "_id" : objectid("596fe03ad496e047d6314bdb"), "played" : 6.0, "win" : 3.0, "lost" : 1.0, "draw" : 2.0, "goalsfor" : 11.0, "goalsagainst" : 4.0, "goaldifferential" : 7.0, "points" : 11.0 } /* 5 */ { "_id" : objectid("596fe03ad496e047d6314c0f"), "played" : 6.0, "win" : 3.0, "lost" : 2.0, "draw" : 1.0, "goalsfor" : 12.0, "goalsagainst" : 12.0, "goaldifferential" : 0.0, "points" : 10.0 } /* 6 */ { "_id" : objectid("596fe03ad496e047d6314be0"), "played" : 6.0, "win" : 2.0, "lost" : 1.0, "draw" : 3.0, "goalsfor" : 6.0, "goalsagainst" : 5.0, "goaldifferential" : 1.0, "points" : 9.0 } /* 7 */ { "_id" : objectid("596fe03ad496e047d6314bed"), "played" : 4.0, "win" : 3.0, "lost" : 1.0, "draw" : 0.0, "goalsfor" : 9.0, "goalsagainst" : 1.0, "goaldifferential" : 8.0, "points" : 9.0 } /* 8 */ { "_id" : objectid("596fe03ad496e047d6314c2d"), "played" : 5.0, "win" : 2.0, "lost" : 1.0, "draw" : 2.0, "goalsfor" : 6.0, "goalsagainst" : 5.0, "goaldifferential" : 1.0, "points" : 8.0 } /* 9 */ { "_id" : objectid("596fe03ad496e047d6314c21"), "played" : 7.0, "win" : 2.0, "lost" : 3.0, "draw" : 2.0, "goalsfor" : 6.0, "goalsagainst" : 10.0, "goaldifferential" : 4.0, "points" : 8.0 } /* 10 */ { "_id" : objectid("596fe03ad496e047d6314bf4"), "played" : 4.0, "win" : 2.0, "lost" : 1.0, "draw" : 1.0, "goalsfor" : 5.0, "goalsagainst" : 2.0, "goaldifferential" : 3.0, "points" : 7.0 } /* 11 */ { "_id" : objectid("596fe03ad496e047d6314c13"), "played" : 5.0, "win" : 1.0, "lost" : 1.0, "draw" : 3.0, "goalsfor" : 8.0, "goalsagainst" : 8.0, "goaldifferential" : 0.0, "points" : 6.0 } /* 12 */ { "_id" : objectid("596fe03ad496e047d6314bfe"), "played" : 6.0, "win" : 1.0, "lost" : 2.0, "draw" : 3.0, "goalsfor" : 3.0, "goalsagainst" : 4.0, "goaldifferential" : 1.0, "points" : 6.0 } /* 13 */ { "_id" : objectid("596fe03ad496e047d6314bee"), "played" : 6.0, "win" : 1.0, "lost" : 2.0, "draw" : 3.0, "goalsfor" : 4.0, "goalsagainst" : 7.0, "goaldifferential" : 3.0, "points" : 6.0 } /* 14 */ { "_id" : objectid("596fe03ad496e047d6314be9"), "played" : 6.0, "win" : 1.0, "lost" : 4.0, "draw" : 1.0, "goalsfor" : 6.0, "goalsagainst" : 8.0, "goaldifferential" : 2.0, "points" : 4.0 } /* 15 */ { "_id" : objectid("596fe03ad496e047d6314be8"), "played" : 5.0, "win" : 1.0, "lost" : 3.0, "draw" : 1.0, "goalsfor" : 6.0, "goalsagainst" : 10.0, "goaldifferential" : 4.0, "points" : 4.0 } /* 16 */ { "_id" : objectid("596fe03ad496e047d6314bf6"), "played" : 6.0, "win" : 1.0, "lost" : 4.0, "draw" : 1.0, "goalsfor" : 7.0, "goalsagainst" : 12.0, "goaldifferential" : 5.0, "points" : 4.0 } /* 17 */ { "_id" : objectid("596fe03ad496e047d6314c1c"), "played" : 5.0, "win" : 0.0, "lost" : 3.0, "draw" : 2.0, "goalsfor" : 2.0, "goalsagainst" : 13.0, "goaldifferential" : 11.0, "points" : 2.0 } /* 18 */ { "_id" : objectid("596fe03ad496e047d6314c0d"), "played" : 3.0, "win" : 0.0, "lost" : 2.0, "draw" : 1.0, "goalsfor" : 1.0, "goalsagainst" : 4.0, "goaldifferential" : 3.0, "points" : 1.0 } and of course if want "team name", resides in "another collection" use $lookup , further operations in order match appropriate objectid value other related collection information wanted in final output.
Comments
Post a Comment