Using $lookup to Join Collections in MongoDB
"MongoDB doesn't do joins" is one of those things people say that stopped being true a long time ago. Good document design reduces how often you need a join, because data that's read together is usually stored together. But real applications always have some references: orders point to customers, comments point to posts, products point to categories. Sooner or later you need to show both sides in one response.
The $lookup aggregation stage is MongoDB's join. It takes each document flowing through a pipeline, finds matching documents in another collection, and attaches them as an array. It supports simple equality joins, correlated sub-pipelines with arbitrary conditions, and combinations of both. Used well, it's fast. Used carelessly, it's the slowest stage in your pipeline.
This guide covers the basic equality syntax, $unwind and reshaping results, the pipeline form with let, joining on arrays, multiple and nested lookups, indexing and performance, and when to model your data differently instead.
Sample Data
db.customers.insertMany([
{ _id: 1, name: "Ada", tier: "gold", country: "GB" },
{ _id: 2, name: "Grace", tier: "silver", country: "US" },
{ _id: 3, name: "Linus", tier: "gold", country: "FI" },
]);
db.orders.insertMany([
{
_id: 101,
customerId: 1,
total: 120,
status: "shipped",
items: ["MUG-01", "LAMP-04"],
},
{ _id: 102, customerId: 1, total: 35, status: "pending", items: ["MUG-01"] },
{ _id: 103, customerId: 2, total: 80, status: "shipped", items: ["PEN-10"] },
{ _id: 104, customerId: 9, total: 15, status: "pending", items: ["PAD-02"] },
]);
db.products.insertMany([
{ _id: "MUG-01", name: "Mug", price: 12 },
{ _id: "LAMP-04", name: "Lamp", price: 65 },
{ _id: "PEN-10", name: "Pen", price: 1 },
]);
Order 104 references a customer that doesn't exist, and Linus has no orders. Both cases show up in the results below.
The Basic Equality Join
The simplest form matches a field in the input documents to a field in the other collection:
db.orders.aggregate([
{
$lookup: {
from: "customers", // collection to join
localField: "customerId", // field in orders
foreignField: "_id", // field in customers
as: "customer", // output array field
},
},
]);
[
{
_id: 101,
customerId: 1,
total: 120,
status: "shipped",
items: ["MUG-01", "LAMP-04"],
customer: [{ _id: 1, name: "Ada", tier: "gold", country: "GB" }],
},
// ...
{
_id: 104,
customerId: 9,
total: 15,
status: "pending",
items: ["PAD-02"],
customer: [],
},
];
Three things to notice:
- The result is always an array, even when at most one document can match. That's because
$lookuphas no way to know the relationship is one-to-one. - Unmatched documents are kept with an empty array.
$lookupbehaves like a left outer join. - The
fromcollection must be in the same database. In recent versions it can be sharded, but it can't live in another database.
In SQL terms, this is roughly:
SELECT o.*, c.*
FROM orders o
LEFT JOIN customers c ON c._id = o.customerId;
except that the matches are nested inside each order rather than flattened into columns.
Reshaping the Results
$unwind for One-to-One Joins
For a many-to-one join like order to customer, an array with one element is awkward. $unwind turns it into an object:
db.orders.aggregate([
{
$lookup: {
from: "customers",
localField: "customerId",
foreignField: "_id",
as: "customer",
},
},
{ $unwind: "$customer" },
]);
By default, $unwind drops documents whose array is empty, so order 104 disappears. That's an inner join. To keep it, preserve empty arrays:
{ $unwind: { path: "$customer", preserveNullAndEmptyArrays: true } }
Now order 104 comes through without a customer field at all.
An alternative that avoids $unwind is to take the first element directly:
{
$set: {
customer: {
$first: "$customer";
}
}
}
If the array is empty, $first returns nothing and the field is removed, which preserves the document the same way.
Trimming Fields
$lookup pulls in whole documents by default. Follow it with $project to keep responses small:
db.orders.aggregate([
{ $match: { status: "shipped" } },
{
$lookup: {
from: "customers",
localField: "customerId",
foreignField: "_id",
as: "customer",
},
},
{ $unwind: "$customer" },
{
$project: {
_id: 1,
total: 1,
customerName: "$customer.name",
tier: "$customer.tier",
},
},
]);
[
{ _id: 101, total: 120, customerName: "Ada", tier: "gold" },
{ _id: 103, total: 80, customerName: "Grace", tier: "silver" },
];
A better approach for large joined documents is to project inside the lookup itself, covered next, so the unneeded fields never enter the pipeline.
The Pipeline Form
The equality form covers most joins. When you need extra conditions, sorting, limits, or projection on the joined side, use the pipeline form:
db.customers.aggregate([
{
$lookup: {
from: "orders",
let: { custId: "$_id" },
pipeline: [
{ $match: { $expr: { $eq: ["$customerId", "$$custId"] } } },
{ $match: { status: "shipped" } },
{ $sort: { total: -1 } },
{ $limit: 3 },
{ $project: { _id: 1, total: 1 } },
],
as: "topShippedOrders",
},
},
]);
let defines variables from the input document (here custId from customers._id), and inside the pipeline you reference them with a double dollar sign: $$custId. A single $ still refers to fields of the joined collection's documents. Variable comparisons must go inside $expr.
Combining localField with a Pipeline
Since MongoDB 5.0, you can use localField and foreignField together with pipeline. The equality match is handled by the concise syntax, and the pipeline only has to express the extra logic:
db.customers.aggregate([
{
$lookup: {
from: "orders",
localField: "_id",
foreignField: "customerId",
pipeline: [
{ $match: { status: "pending" } },
{ $project: { _id: 1, total: 1 } },
],
as: "pendingOrders",
},
},
]);
This is usually the best of both worlds: it's easier to read than let plus $expr, and the equality condition reliably uses an index on orders.customerId.
Uncorrelated Sub-Pipelines
A pipeline without let or localField runs the same query for every input document. It's useful for attaching a small reference table or a global stat:
db.orders.aggregate([
{
$lookup: {
from: "orders",
pipeline: [{ $group: { _id: null, avgTotal: { $avg: "$total" } } }],
as: "stats",
},
},
{
$set: { aboveAverage: { $gt: ["$total", { $first: "$stats.avgTotal" }] } },
},
{ $unset: "stats" },
]);
MongoDB can cache the results of uncorrelated sub-pipelines, so this doesn't rerun the $group for each order.
Joining on Arrays
If localField is an array, $lookup matches any document whose foreignField equals any element. That makes resolving a list of references straightforward:
db.orders.aggregate([
{ $match: { _id: 101 } },
{
$lookup: {
from: "products",
localField: "items",
foreignField: "_id",
as: "products",
},
},
]);
[
{
_id: 101,
customerId: 1,
total: 120,
status: "shipped",
items: ["MUG-01", "LAMP-04"],
products: [
{ _id: "MUG-01", name: "Mug", price: 12 },
{ _id: "LAMP-04", name: "Lamp", price: 65 },
],
},
];
The joined array isn't guaranteed to follow the order of items, and duplicate IDs in items produce only one matched document. If order or quantities matter, store line items as objects ({ sku, qty }) and merge the looked-up details back in with $map and $filter, or resolve them in application code.
Multiple and Nested Lookups
You can chain lookups to pull in several related collections:
db.orders.aggregate([
{ $match: { status: "shipped" } },
{
$lookup: {
from: "customers",
localField: "customerId",
foreignField: "_id",
as: "customer",
},
},
{ $unwind: "$customer" },
{
$lookup: {
from: "products",
localField: "items",
foreignField: "_id",
as: "products",
},
},
]);
And you can nest a lookup inside a lookup's pipeline, for example customers with their orders, each with its products:
db.customers.aggregate([
{ $match: { _id: 1 } },
{
$lookup: {
from: "orders",
localField: "_id",
foreignField: "customerId",
pipeline: [
{
$lookup: {
from: "products",
localField: "items",
foreignField: "_id",
as: "products",
},
},
],
as: "orders",
},
},
]);
This works, but every level multiplies the work. If a customer has 500 orders and each has 5 products, that's 500 inner lookups for one customer. Nested lookups are a strong signal to reconsider the data model or add a limit.
Performance: Making $lookup Fast
For each input document, $lookup runs a query against the from collection. That makes two things matter above all else.
Index the foreign field. Without an index on orders.customerId, every customer triggers a scan of the entire orders collection. With it, each lookup is an index seek.
db.orders.createIndex({ customerId: 1 });
For the pipeline form, a compound index that covers the equality field plus the extra filter and sort fields ({ customerId: 1, status: 1, total: -1 } for the "top shipped orders" example) lets the sub-pipeline avoid in-memory sorting too.
Reduce input before joining. Put $match, $sort, and $limit before $lookup so you join 20 documents instead of 20,000:
db.orders.aggregate([
{ $match: { status: "pending" } },
{ $sort: { _id: -1 } },
{ $limit: 20 },
{
$lookup: {
from: "customers",
localField: "customerId",
foreignField: "_id",
as: "customer",
},
},
]);
A $match on joined fields after $lookup (for example, "orders whose customer is gold tier") can't use indexes on the original collection to cut the input, because the data doesn't exist until after the join. If you filter on joined fields often, that's a case for copying the field onto the order.
Use explain("executionStats") on the aggregation to check. Recent versions report per-lookup statistics including the index used and documents examined on the foreign side. The guide to using explain walks through reading that output. MongoDB can also execute some $lookup stages with the slot-based execution engine and strategies like hash joins, but indexing and early filtering remain the things you control.
Also keep an eye on output size. Every document in the pipeline is still bound by the 16 MB BSON limit, so a lookup that attaches thousands of matches can fail. Limit or project inside the sub-pipeline, or $unwind immediately after the lookup, which the optimizer can coalesce with the $lookup to avoid building the full array.
Using $lookup from a Driver
Aggregations look the same in every driver. In Node.js:
const recent = await db
.collection("orders")
.aggregate([
{ $sort: { _id: -1 } },
{ $limit: 10 },
{
$lookup: {
from: "customers",
localField: "customerId",
foreignField: "_id",
as: "customer",
},
},
{ $set: { customer: { $first: "$customer" } } },
{ $project: { total: 1, status: 1, "customer.name": 1 } },
])
.toArray();
In PyMongo:
pipeline = [
{"$match": {"status": "shipped"}},
{"$lookup": {
"from": "customers",
"localField": "customerId",
"foreignField": "_id",
"as": "customer",
}},
{"$unwind": "$customer"},
{"$project": {"total": 1, "customerName": "$customer.name"}},
]
for doc in db.orders.aggregate(pipeline):
print(doc)
If you use Mongoose, populate() isn't a $lookup; it runs separate queries and stitches the results together in your application. That's often fine, but for large result sets or filtering on joined fields, an aggregation with $lookup is more efficient.
When Not to Use $lookup
$lookup is a tool for occasional joins, not a replacement for good document design. If a page runs the same lookup on every request, ask whether the data should live together:
- If you always show the customer's name with an order, copy
customer.nameonto the order (the extended reference pattern). - If the child data is small and bounded, embed it instead of referencing it.
- If the join is used for reports rather than live requests, consider a materialized view built with
$merge.
The guide to one-to-many relationships covers these trade-offs in more depth.
Common Pitfalls
Joining on mismatched types. If orders.customerId is the string "1" and customers._id is the number 1, nothing matches. The same applies to ObjectIds stored as strings. Fix the data, or convert with $toObjectId inside a pipeline lookup as a stopgap (which prevents index use).
Missing index on the foreign field. This is the single most common cause of slow lookups. Index foreignField, and for pipeline lookups, index the fields in the sub-pipeline's $match.
Using $unwind without realizing it drops unmatched documents. If you want left-join behaviour, set preserveNullAndEmptyArrays: true or use $first instead.
Joining before filtering. Every document that reaches $lookup triggers a query. Filter, sort, and limit first.
Forgetting $expr for variables. In the pipeline form, { $match: { customerId: "$$custId" } } compares against the literal string "$$custId". Variable comparisons must be wrapped in $expr.
Conclusion
$lookup gives MongoDB a proper left outer join inside the aggregation pipeline. The equality form handles the common case in five lines, the pipeline form adds filtering, sorting, and projection on the joined side, and combining localField with a pipeline gives you both clarity and index use. Keep lookups fast by indexing the foreign field and reducing input first, and reach for denormalization when the same join shows up on every request.
Take your slowest page that joins data and run its pipeline with explain("executionStats"). If the foreign side shows a collection scan, add an index on the foreignField and measure again.
