$lookup


Updated 14 October 20262 min read

$lookup is an aggregation stage that joins each input document with matching documents from another collection in the same database, storing the matches in an array field.

$lookup is MongoDB's join. Inside an aggregation pipeline, it takes each document flowing through, finds matching documents in another collection of the same database, and adds them as an array field. If nothing matches, the array is empty and the document still passes through, which makes $lookup behave like a SQL LEFT OUTER JOIN by default.

The simplest form compares one field on each side. localField names the field in the current documents, foreignField names the field in the other collection, from is that collection, and as is the name of the new array field. Joining orders to customers on customerId and _id is the classic case. If localField is an array, each element is matched, which is handy for many-to-many references.

The second form adds a pipeline that runs on the joined collection. You can filter what gets joined (only paid invoices), project only the fields you need, sort and limit (the latest three tickets), or match on several conditions using variables declared in let. Since MongoDB 5.0 you can combine localField and foreignField with a pipeline, which is often the clearest way to write a filtered join.

Because the result is an array, the next stage usually reshapes it. $unwind turns the array into one document per match and drops documents with no match, which turns the join into an INNER JOIN. $first or $arrayElemAt pulls a single value out of a one-to-one join, and $size counts matches, which replaces COUNT with GROUP BY in many SQL translations.

Performance depends on indexes. Each input document triggers a lookup in the foreign collection, so the foreignField should be indexed; without that index a join over many documents can be slow. Filtering with $match before the $lookup reduces how many lookups happen.

Embedding is the alternative to joining. When data is always read together, such as line items with their order, MongoDB schemas usually embed it, and $lookup is kept for relationships that are shared or large. wuup only allows $lookup between collections you have enabled in the same database, so a generated join can never reach data outside the scope you chose.

mongosh
db.orders.aggregate([
  { $match: { status: "delivered" } },
  { $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customer"
  } },
  { $unwind: "$customer" },
  { $project: { _id: 0, orderNumber: 1, total: 1, customer: "$customer.name" } }
])
A worked example of $lookup.

Questions,
answered

From definitions
to answers.