$lookup and $unwind

The $lookup stage performs a left outer join between two collections, similar to a SQL JOIN. You specify the foreign collection, the local field containing the reference, the matching foreign field, and the output array name. The result embeds the matched documents as an array in each input document. $unwind then deconstructs that array — if the joined array has one element per document you get a flat structure similar to an SQL join. $unwind by default removes documents where the array is missing or empty; setting preserveNullAndEmptyArrays: true keeps them, mimicking a LEFT JOIN. The $lookup stage also supports a pipeline sub-query (let + pipeline) for more complex join conditions.

Example

// Join orders with the users collection
db.orders.aggregate([
  { $match: { status: "shipped" } },

  // Standard $lookup (foreign key join)
  { $lookup: {
      from:         "users",
      localField:   "userId",
      foreignField: "_id",
      as:           "customer"
  }},

  // $unwind turns the array into a single embedded doc
  { $unwind: { path: "$customer", preserveNullAndEmptyArrays: true } },

  { $project: {
      orderId:       "$_id",
      customerName:  "$customer.name",
      customerEmail: "$customer.email",
      total:         1,
      status:        1
  }},

  // Pipeline $lookup with let — advanced join with filter
  // (shown as a second example)
  // { $lookup: {
  //     from: "products",
  //     let:  { itemIds: "$items.productId" },
  //     pipeline: [
  //       { $match: { $expr: { $in: ["$_id", "$$itemIds"] } } },
  //       { $project: { name: 1, price: 1 } }
  //     ],
  //     as: "productDetails"
  // }}
])

HTML

Full Editor

Related Resources

MongoDB Reference

Complete tag & property list

MongoDB How-To Guides

Step-by-step practical guides

MongoDB Exercises

Practice what you've learned