Convert SQL INNER JOIN to MongoDB $lookup with $unwind

INNER JOIN to $lookup + $unwind

SQL's INNER JOIN combines rows from two tables where the join condition is met. MongoDB handles this through the $lookup aggregation stage, which performs a left outer join, followed by $unwind to flatten the results.

Example SQL

SELECT orders.id, orders.total, users.name
FROM orders
INNER JOIN users ON orders.user_id = users.id

Generated MongoDB Query

db.orders.aggregate([
  {
    $lookup: {
      from: "users",
      localField: "user_id",
      foreignField: "id",
      as: "users"
    }
  },
  { $unwind: "$users" },
  {
    $project: {
      id: 1,
      total: 1,
      "users.name": 1
    }
  }
])

How $lookup Works

$lookup performs a left outer join between the current collection and a foreign collection. The key parameters are:

• from: the foreign collection to join with
• localField: the field from the input documents
• foreignField: the field from the foreign collection
• as: the output array field name

Why $unwind?

$lookup produces an array of matching documents in the as field. For an INNER JOIN equivalent, $unwind is used to:

1. Flatten the array so each matching document becomes a separate result
2. Remove documents with empty arrays (no matches), simulating INNER JOIN behavior

Without $unwind, each result document would contain an array of all matching foreign documents.

Multiple JOINs

For queries joining three or more tables, chain multiple $lookup + $unwind stages:

db.orders.aggregate([
  { $lookup: { from: "users", localField: "user_id", foreignField: "id", as: "user" } },
  { $unwind: "$user" },
  { $lookup: { from: "products", localField: "product_id", foreignField: "id", as: "product" } },
  { $unwind: "$product" }
])

Performance Considerations

MongoDB $lookup does not use indexes on the foreign collection's foreignField in the same way SQL uses indexed joins. For high-performance joins, ensure the foreign field is indexed and consider denormalizing your data model where frequent joins are needed.