MongoDB – Join Two Collections with GROUP BY and SUM

aggregatemongodbmongodb-4.0

i have this two collection and i want to join, after this I want to sum the value of TotalPrice each orderdate :

some line of my two collections

the first collection date :

{"Id_Date":1,"OrderDate":"2018-01-03"},
{"Id_Date":2,"OrderDate":"2018-01-05"},
{"Id_Date":3,"OrderDate":"2018-01-09"},
{"Id_Date":4,"OrderDate":"2018-01-10"},
{"Id_Date":5,"OrderDate":"2018-01-11"},
{"Id_Date":6,"OrderDate":"2018-01-12"},
{"Id_Date":7,"OrderDate":"2018-01-18"},
{"Id_Date":8,"OrderDate":"2018-01-21"},
{"Id_Date":9,"OrderDate":"2018-01-22"},
{"Id_Date":10,"OrderDate":"2018-02-02"},

the second collection order_fact :

{"orderid":"c3e15faf-e338-4ed5-9bbb-bbcd15295fd7","Id_Cust":"10995116278711","Id_Date":"91","TotalPrice":1933.53,"Orderline":[{"productId":"4165","asin":"B005G2G2SQ","title":"ESEE-6 Plain Black Blade With Grey Removable Lined Micarta Handles 1095 Carbon Steel 57-Rc","price":140.05,"brand":"Fila_(company)"},{"productId":"5916","asin":"B001CJ2LQ4","title":"Aimpoint M4s 2 Minute of Angle QRP2 CompM4 Sight with Mount","price":804.95,"brand":"Atletica"},{"productId":"7779","asin":"B003DO5L3G","title":"Lee Precision Load Master 223 Remington Reloading Rifle Kit (Red)","price":249.26,"brand":"BURRDA"},{"productId":"2722","asin":"B0017U1MJU","title":"Casio Mens PAW1500-1V Pathfinder Multi-Band Solar Atomic Ultimate Watch","price":190.27,"brand":"Reebok"},{"productId":"52","asin":"B007SYGLZO","title":"Armasight Spark CORE Multi-Purpose Night Vision Monocular","price":549.0,"brand":"Signia_(sportswear)"}],"Livreur":{"Id_livreur":"91bcee9f-8896-4e18-a24c-a8e86b97b3a1","Nom":"Tyson Anderson","gender":"Male","phone":"7-458-665-3444"}},
{"orderid":"35b26e6d-e450-4edc-9071-306cea10a083","Id_Cust":"10995116278711","Id_Date":"249","TotalPrice":1540.91,"Orderline":[{"productId":"1978","asin":"B001CZBMRC","title":"Benchmade Osborne Design Rift Knife","price":178.5,"brand":"CCM_(ice_hockey)"},{"productId":"857","asin":"B004IIKA2I","title":"Barnett Quad 400 Crossbow Package (Quiver 3 - 22-Inch Arrows and 4x32mm Scope)","price":354.99,"brand":"Fischer_(company)"},{"productId":"8362","asin":"B00635GTTW","title":"Schwinn IC2 Indoor Cycling Exercise Bike","price":422.49,"brand":"Admiral_Sportswear"},{"productId":"216","asin":"B000GKN45C","title":"Swagman XTC-4 Cross-Country 4-Bike Hitch Mount Rack (2-Inch Receiver Hitch)","price":349.99,"brand":"Elfin_Sports_Cars"},{"productId":"7766","asin":"B000KKEPJ2","title":"Lyman Reloading Press T-Mag Turret Press","price":234.94,"brand":"BURRDA"}],"Livreur":{"Id_livreur":"48d64c09-7a63-4fca-815a-a96921a82ee7","Nom":"Abdul Coll","gender":"Male","phone":"1-273-204-4721"}},
{"orderid":"ffaec62e-9875-42af-8789-0a97eabe77e4","Id_Cust":"10995116278711","Id_Date":"67","TotalPrice":1058.41,"Orderline":[{"productId":"2675","asin":"B00BPRM2O4","title":"Large Exercise Mat Large Workout Mat With A Unique Fabric Finish That Provides Excellent Traction And Floor Protection. Can Be Used In Conjunction With The Square36 YOGA Mat. The Square36 CARDIO Mat - The BIG Exercise Mat- Step Into the Square.","price":159.99,"brand":"Reebok"},{"productId":"1380","asin":"B000EQCVQ6","title":"Eureka! Apex 2XT Two-Person Tent","price":175.16,"brand":"Topper_(sports)"},{"productId":"9665","asin":"B004H4VSI8","title":"IRONMAN HIGH CAPACITY GRAVITY 3000 INVERSION TABLE","price":239.8,"brand":"Wilson_Sporting_Goods"},{"productId":"7603","asin":"B0017SC9H6","title":"Zero Tolerance Combat Folding Knife","price":199.0,"brand":"Tramontana_(sports_car)"},{"productId":"887","asin":"B00BCLL8C0","title":"SportRack SR7018 Vista XL Rear Opening Cargo Box 18-Cubic Feet","price":284.46,"brand":"Fischer_(company)"}],"Livreur":{"Id_livreur":"7548ac7b-5bf7-4c5a-a80d-5390b73b9b90","Nom":"Henry Wilde","gender":"Male","phone":"3-624-033-5243"}},
{"orderid":"125e7887-c41d-4a5a-aa6b-d2a3679d58e7","Id_Cust":"10995116278711","Id_Date":"477","TotalPrice":1624.98,"Orderline":[{"productId":"6250","asin":"B005DYQ9V2","title":"Invicta Men s 1463 Reserve Collection Chronograph Silver Dial Stainless Steel Watch","price":199.95,"brand":"Keuka_(brand)"},{"productId":"1138","asin":"B000AMRN12","title":"Stamina AeroPilates Reformer with Free-Form Cardio Rebounder","price":449.0,"brand":"Topper_(sports)"},{"productId":"7575","asin":"B005D7FXMA","title":"Cygolite Expilion 400 Usb Rechargeable Headlight with Quick Release Li-Ion Battery Stick and Wall Charger","price":113.99,"brand":"Derbi"},{"productId":"6529","asin":"B005Z2CYX4","title":"Zero Tolerance ZT0550 Hinderer Design Folding Knife","price":160.0,"brand":"CA_Sports"},{"productId":"7125","asin":"B007INYN6O","title":"Barnett Zombie 350 CRT Crossbow","price":702.04,"brand":"Elan_Snowboards"}],"Livreur":{"Id_livreur":"a1060a87-fdff-4acc-b728-c805b72b0740","Nom":"Percy Thornton","gender":"Male","phone":"5-744-522-0782"}},
{"orderid":"b259eafc-2f47-47dc-927b-5b6d6edbba1d","Id_Cust":"10995116278711","Id_Date":"617","TotalPrice":1484.76,"Orderline":[{"productId":"7667","asin":"B008W1TUAK","title":"MAGPUL ACS CARB STOCK MIL-SPEC BLACK","price":98.99,"brand":"POC_Sports"},{"productId":"6406","asin":"B001CJX50K","title":"Kinetic Rock n Roll Trainer w/Road Resistance Unit","price":389.99,"brand":"MYLAPS_Sports_Timing"},{"productId":"1883","asin":"B000O8Z7TW","title":"KNEX Education - Exploring Machines","price":126.79,"brand":"TRYMAX"},{"productId":"4158","asin":"B003AQKQAA","title":"Vortex Viper PST 6-24x50 Rifle PST-624S1-M","price":749.0,"brand":"Fila_(company)"},{"productId":"7578","asin":"B00D43A93I","title":"Under Armour Hammer 8600014-4808 Polarized Wrap Sunglasses","price":119.99,"brand":"Derbi"}],"Livreur":{"Id_livreur":"eb135eef-7ee2-4d90-b073-cbd4570935b3","Nom":"Violet Adler","gender":"Female","phone":"5-466-075-2676"}},
{"orderid":"b5755c1e-fc3f-4481-a175-edfc82290ea5","Id_Cust":"10995116278711","Id_Date":"208","TotalPrice":2487.71,"Orderline":[{"productId":"965","asin":"B000O3AVNY","title":"Advanced Elements AdvancedFrame Expedition Kayak","price":599.99,"brand":"Donnay_(sports)"},{"productId":"6888","asin":"B0029KL3S2","title":"Precor 240i Commercial Series StretchTrainer","price":704.2,"brand":"Elan_(company)"},{"productId":"6439","asin":"B004PJ1J4S","title":"Invicta Men s 6566 Subaqua Noma IV Collection Chronograph Black Polyurethane Watch","price":340.65,"brand":"MYLAPS_Sports_Timing"},{"productId":"9503","asin":"B0034PXYRY","title":"Eotech EXPS3-0 Holographic Weapon Site","price":606.92,"brand":"EA_Sports"},{"productId":"7740","asin":"B00271ERVI","title":"Crimson Trace Lasergrip for Ruger Lcr","price":235.95,"brand":"BURRDA"}],"Livreur":{"Id_livreur":"c75a672e-37cf-498e-8bf9-8dae7857165f","Nom":"Renee Eyres","gender":"Female","phone":"6-311-624-4031"}},
{"orderid":"8a129f4e-e736-4919-9a22-c98373f1df3b","Id_Cust":"10995116278711","Id_Date":"390","TotalPrice":1233.67,"Orderline":[{"productId":"1929","asin":"B004P4HH8U","title":"Barnett Ghost 350 CRT Crossbow Package (Quiver 3 - 20-Inch Arrows and Illuminated 3x32mm Scope)","price":509.99,"brand":"TRYMAX"},{"productId":"2940","asin":"B004IIZVQ8","title":"Barnett Wildcat C5 Crossbow Package (Quiver 3 - 20-Inch Arrows and Premium Red Dot Sight)","price":342.99,"brand":"Volkl"},{"productId":"3174","asin":"B006U0YZPA","title":"Bushnell X-8 6MP Trail Camera with Night Vision and Field Scan","price":136.94,"brand":"Daei_Sport"},{"productId":"6527","asin":"B003MA1SSI","title":"Sadlak Industries M14 National Match Spring Guide","price":40.75,"brand":"CA_Sports"},{"productId":"1759","asin":"B007L4ZGXE","title":"Zero Tolerance ZT0560 Hinderer Design Black Folding Knife","price":203.0,"brand":"Olympikus"}],"Livreur":{"Id_livreur":"2ccb39c1-5c1a-46d8-b394-fc887bd4cecd","Nom":"Faith Flanders","gender":"Female","phone":"2-586-775-3278"}},
{"orderid":"bb671476-5f67-414a-9b84-77498b05f0bd","Id_Cust":"10995116278711","Id_Date":"205","TotalPrice":1601.81,"Orderline":[{"productId":"810","asin":"B009K28INA","title":"Schwinn 425 Elliptical Trainer (2013)","price":599.99,"brand":"Fischer_(company)"},{"productId":"6802","asin":"B000WY8ZHO","title":"Luminox Men s Navy Seal ColorMark Watch 3051","price":229.09,"brand":"Onda_(sportswear)"},{"productId":"3178","asin":"B007O5B0LC","title":"Weslo Cadence G 5.9 Treadmill","price":351.0,"brand":"Daei_Sport"},{"productId":"2126","asin":"B0025YCYK8","title":"Spyderco Temperence 2 Canvas Micarta Plain Edge Knife","price":207.72,"brand":"Li-Ning"},{"productId":"6736","asin":"B002QWB8HY","title":"CamelBak BFM 3L Backpack","price":214.01,"brand":"Onda_(sportswear)"}],"Livreur":{"Id_livreur":"629b2edd-8bd4-4d50-8e63-33f4904ffd2c","Nom":"Stephanie Garcia","gender":"Female","phone":"6-618-288-6820"}},
{"orderid":"d3016947-f478-4782-941d-37132482a10a","Id_Cust":"10995116278711","Id_Date":"694","TotalPrice":1534.78,"Orderline":[{"productId":"3029","asin":"B00AJK9CW8","title":"Seiko Men s SRP307 Classic Automatic Dive Watch","price":192.87,"brand":"Kettler"},{"productId":"2964","asin":"B004V956P0","title":"Crosman Optimus Break Barrel Air Rifle (.177)","price":91.98,"brand":"Volkl"},{"productId":"4115","asin":"B003UNZHNY","title":"Schwinn A10 Upright Exercise Bike (2011)","price":199.99,"brand":"Fila_(company)"},{"productId":"6853","asin":"B0081MPIBA","title":"ETEK4 - Planet Eclipse Etek 4 LT / AM Paintball Guns","price":499.95,"brand":"Elan_(company)"},{"productId":"893","asin":"B001AS697O","title":"Bowflex PR1000 Home Gym","price":549.99,"brand":"Fischer_(company)"}],"Livreur":{"Id_livreur":"787192a7-8417-44a9-a5bb-dd9060afd524","Nom":"Isla Denton","gender":"Female","phone":"1-260-416-8800"}},
{"orderid":"30dd4a79-4dac-4043-8fbc-e746e78aced3","Id_Cust":"10995116278711","Id_Date":"702","TotalPrice":1329.04,"Orderline":[{"productId":"2726","asin":"B005SSWKMK","title":"Casio Men s PRW2500T-7CR Pathfinder Triple Sensor Tough Solar Digital Multi-Function Titanium Pathfinder Casual Watch","price":266.12,"brand":"Reebok"},{"productId":"6563","asin":"B0014NDXGA","title":"Bushnell Tour V2 Standard Edition Golf Laser Rangefinder","price":290.06,"brand":"Onda_(sportswear)"},{"productId":"4856","asin":"B001QD48GC","title":"FreeForm Hideaway Home Gym","price":641.93,"brand":"ASICS"},{"productId":"7653","asin":"B007ZNUG9K","title":"Blackhawk Black TraverseTrack Bipod - 10 - BH71BP10BK","price":50.98,"brand":"POC_Sports"},{"productId":"4166","asin":"B00AJT98OQ","title":"Universal Brass Catcher","price":79.95,"brand":"Fila_(company)"}],"Livreur":{"Id_livreur":"5acb6f8a-fb71-4b32-b40d-fde782d70600","Nom":"Belinda Marshall","gender":"Female","phone":"6-671-643-4273"}},

How i can sum of the total prices for each date ?

Best Answer

The below query will join both the tables and return the sum based on order date,

db.order_fact.aggregate([{"$lookup":{"from":"date","localField":"Id_Date","foreignField":"Id_Date","as":"dateDetails"}},{"$unwind":"$dateDetails"},{"$group":{"_id":{"date":"$dateDetails.OrderDate"},"totalPrice":{"$sum":"$TotalPrice"}}}])

Thanks, Rilwan