title: How to join multiple tables in mongodb
body: <p>I currently have 5 tables that need to joined due to their coupling.
  Using $lookup I can join the Order table with the Plan table and get what I need, but how do I go about the other tables?</p>
  
  <p>Here is each table, and the Id/table it needs to connect with</p>
  
  <pre><code>Plan       - _id, unassignedOrderIds(array)
  DriverPlan - _id, planId, orderIds(array), driverId, vehicleId
  Driver     - _id, vehicleId
  Vehicle    - _id
  Orders     - _id
  </code></pre>
  
  <p>In the end I'm looking for mongoDb to return a Plan object that has UnassignedOrders loaded and DriverPlans loaded. Followed by DriverPlans having its Orders,Driver, and Vehicle loaded.</p>
  
  <p>Here is what I have so far:</p>
  
  <pre><code>db.Plan.aggregate([
      // Unwind the source
      { "$unwind": "$UnassignedOrderIds" },
      // Do the lookup matching
      { "$lookup": {
         "from": "Order",
         "localField": "UnassignedOrderIds",
         "foreignField": "_id",
         "as": "UnassignedOrders"
      }},
      // Unwind the result arrays ( likely one or none )
      { "$unwind": "$UnassignedOrders" },
      // Group back to arrays
      { "$group": {
          "_id": "$_id",
          "Order": { "$push": "$Order" },
          "UnassignedOrders": { "$push": "$UnassignedOrders" }
      }}
  ])
  </code></pre>
  
  <p>Sample Document:</p>
  
  <pre><code>//Plan
  {
      "_id" : ObjectId("5c1d244de707b20cece645f1"),
      "UnassignedOrderIds" : [ 
          ObjectId("5c1d247fe707b20cece6462e"), 
          ObjectId("5c1d035de707b20cece63104")
      ]
  }
  
  //DriverPlan
  [{
      "_id" : ObjectId("123d247fe707b20cece6462e"),
      "PlanId" : ObjectId("5c1d244de707b20cece645f1"),
      "DriverId" : ObjectId("1c1d247fe707b20cece64622"),
      "VehicleId" : ObjectId("3c1d247fe707b20cece64633"),
      "OrderIds": [ 
          ObjectId("5c1d247fe707b20cece64621"), 
          ObjectId("5c1d247fe707b20cece64624")
      ]
  },{
      "_id" : ObjectId("123d247fe707b20cece64655"),
      "PlanId" : ObjectId("5c1d244de707b20cece645f1"),
      "DriverId" : ObjectId("2c1d035de707b20cece63104"),
      "VehicleId" : null,
      "OrderIds": [ 
          ObjectId("5c1d247fe707b20cece64625")
      ]
  }]
  
  //Orders
  [{
      "_id" : ObjectId("5c1d247fe707b20cece6462e"),
      "name" "Order1"
  },{
      "_id" : ObjectId("5c1d035de707b20cece63104"),
      "name" "Order2"
  },{
      "_id" : ObjectId("5c1d247fe707b20cece64621"),
      "name" "Order3"
  },{
      "_id" : ObjectId("5c1d247fe707b20cece64624"),
      "name" "Order4"
  },{
      "_id" : ObjectId("5c1d247fe707b20cece64625"),
      "name" "Order5"
  }]
  
  //Driver
  [{
      "_id" : ObjectId("1c1d247fe707b20cece64622"),
      "vehicleId" : ObjectId("3c1d247fe707b20cece6462e"),
      "name" "Driver1"
  },{
      "_id" : ObjectId("2c1d035de707b20cece63104"),
      "vehicleId" : null,
      "name" "Driver2"
  },{
      "_id" : ObjectId("3c1d247fe707b20cece64621"),
      "vehicleId" : ObjectId("3c1d035de707b20cece63104"),
      "name" "Driver3"
  }]
  
  //Vehicle
  [{
      "_id" : ObjectId("3c1d247fe707b20cece6462e"),
      "name" "Vehicle1"
  },{
      "_id" : ObjectId("3c1d035de707b20cece63104"),
      "name" "Vehicle2"
  },{
      "_id" : ObjectId("3c1d247fe707b20cece64633"),
      "name" "Vehicle3"
  }]
  </code></pre>
  
  <p>The Expected output is json object as follows</p>
  
  <pre><code>//Plan with children loaded
  {
  "_id" : ObjectId("5c1d244de707b20cece645f1"),
  "UnassignedOrderIds" : [ 
      ObjectId("5c1d247fe707b20cece6462e"), 
      ObjectId("5c1d035de707b20cece63104")
  ],
  "UnassignedOrders": [{
      "_id" : ObjectId("5c1d247fe707b20cece6462e"),
      "name" "Order1"
  },{
      "_id" : ObjectId("5c1d035de707b20cece63104"),
      "name" "Order2"
  }],
  "DriverPlans" : 
  [{
      "_id" : ObjectId("123d247fe707b20cece6462e"),
      "PlanId" : ObjectId("5c1d244de707b20cece645f1"),
      "DriverId" : ObjectId("1c1d247fe707b20cece64622"),
      "Driver": {
          "_id" : ObjectId("1c1d247fe707b20cece64622"),
          "vehicleId" : ObjectId("3c1d247fe707b20cece6462e"),
          "name" "Driver1"
      },
      "VehicleId" : ObjectId("3c1d247fe707b20cece64633"),
      "Vehicle" : {
          "_id" : ObjectId("3c1d247fe707b20cece64633"),
          "name" "Vehicle3"
      },
      "OrderIds": [ 
          ObjectId("5c1d247fe707b20cece64621"), 
          ObjectId("5c1d247fe707b20cece64624")
      ],
      "Orders" : [ 
          {
              "_id" : ObjectId("5c1d247fe707b20cece64621"),
              "name" "Order3"
          },{
              "_id" : ObjectId("5c1d247fe707b20cece64624"),
              "name" "Order4"
          }]
  },{
      "_id" : ObjectId("123d247fe707b20cece64655"),
      "PlanId" : ObjectId("5c1d244de707b20cece645f1"),
      "DriverId" : ObjectId("2c1d035de707b20cece63104"),
      "Driver" : {
          "_id" : ObjectId("2c1d035de707b20cece63104"),
          "vehicleId" : null,
          "name" "Driver2"
      }
      "VehicleId" : null,
      "Vehicle" : null,
      "OrderIds": [ 
          ObjectId("5c1d247fe707b20cece64625")
      ],
      "Orders": [{
              "_id" : ObjectId("5c1d247fe707b20cece64625"),
              "name" "Order5"
          }
      ]
  }]
  </code></pre>
  
  <p>}</p>