nosql-5.md

05 — Relationships & Joins ($lookup)

Goal: Combine data from two separate collections. Key Idea: MongoDB's equivalent of LEFT OUTER JOIN is the $lookup stage inside an Aggregation Pipeline.

---

How Results Differ

	SQL JOIN	MongoDB $lookup
Shape	Flat rows — data is "spread out"	Hierarchical — joined data is an array inside the document
1 order with 3 products	Returns 3 rows	Returns 1 document with an array of 3 products

---

Task: Get orders with their product details

SQL Way

SELECT *
FROM Orders
LEFT JOIN Products ON Orders.product_id = Products.id;

MongoDB Way

db.orders.aggregate([
  {
    $lookup: {
      from: "products",        // The foreign collection to join
      localField: "product_id", // Field in the Orders collection
      foreignField: "_id",      // Field in the Products collection
      as: "product_details"     // Name of the new output array field
    }
  }
])

---

🔍 Breaking Down $lookup

$lookup: {
  from:         "products",    // Which collection to pull from?
  localField:   "product_id",  // Which field in THIS collection?
  foreignField: "_id",         // Which field in the OTHER collection?
  as:           "product_details" // Store results in this new field
}

What the output document looks like:

{
  "_id": "order_1",
  "customer": "Alice",
  "product_id": "prod_42",
  "product_details": [
    {
      "_id": "prod_42",
      "name": "Keyboard",
      "price": 79.99
    }
  ]
}

Notice: product_details is an array, even if there's only one match.

---

Getting a Single Object Instead of an Array

Use $unwind after $lookup to flatten the array into a single object:

db.orders.aggregate([
  {
    $lookup: {
      from: "products",
      localField: "product_id",
      foreignField: "_id",
      as: "product_details"
    }
  },
  {
    $unwind: "$product_details"  // Flatten the array
  }
])

---

💡 Notes

• $lookup is part of the Aggregation Pipeline — always inside aggregate([...]).
• If you find yourself using $lookup for every query, you may be better off embedding the data instead (see Rules of Thumb).
• $lookup is more expensive than a simple find() — use it intentionally.