$lookup with pipeline (advanced join) in MongoDB - Step-by-Step Execution

Concept Flow - $lookup with pipeline (advanced join)

Start with main collection

↓

$lookup stage with pipeline

↓

For each main doc, run pipeline on joined collection

↓

Match, filter, or transform joined docs

↓

Add joined docs as array field

↓

Output enriched documents

The $lookup with pipeline runs a custom query on the joined collection for each document in the main collection, then adds the results as a new array field.

Execution Sample

MongoDB

db.orders.aggregate([
  {
    $lookup: {
      from: "products",
      let: { order_item: "$item" },
      pipeline: [
        { $match: { $expr: { $eq: ["$name", "$$order_item"] } } },
        { $project: { name: 1, price: 1 } }
      ],
      as: "productDetails"
    }
  }
])

This query joins 'orders' with 'products' by matching 'item' in orders to 'name' in products, returning product details for each order.

Execution Table

Step	Main Doc _id	let Variables	Pipeline Match Condition	Matched Docs	Output Field 'productDetails'
1	order1	{ order_item: 'Pen' }	Match products where name == 'Pen'	[{_id: 'p1', name: 'Pen', price: 1.5}]	[{name: 'Pen', price: 1.5}]
2	order2	{ order_item: 'Notebook' }	Match products where name == 'Notebook'	[{_id: 'p2', name: 'Notebook', price: 3.0}]	[{name: 'Notebook', price: 3.0}]
3	order3	{ order_item: 'Pencil' }	Match products where name == 'Pencil'	[]	[]
4	END	-	-	-	Aggregation complete, all orders enriched

💡 All documents processed; pipeline matched products or returned empty arrays when no match.

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
order_item	undefined	'Pen'	'Notebook'	'Pencil'	N/A
Matched Docs	undefined	[{_id: 'p1', name: 'Pen', price: 1.5}]	[{_id: 'p2', name: 'Notebook', price: 3.0}]	[]	N/A
productDetails	undefined	[{name:'Pen',price:1.5}]	[{name:'Notebook',price:3.0}]	[]	N/A

Key Moments - 2 Insights

Why does the 'productDetails' field sometimes contain an empty array?

Because the pipeline match condition found no products matching the order's item (see execution_table row 3), so no documents were joined.

How does the 'let' variable 'order_item' get used inside the pipeline?

It is referenced inside the $match stage with $expr and $$ to compare product names to the current order's item (see execution_table rows 1-3).

Visual Quiz - 3 Questions

Look at the execution_table at Step 2. What is the value of 'productDetails' for order2?

A []

B [{name: 'Pen', price: 1.5}]

C [{name: 'Notebook', price: 3.0}]

D [{name: 'Pencil', price: 0.5}]

💡 Hint

Check the 'Output Field productDetails' column at Step 2 in execution_table.

At which step does the pipeline find no matching products?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Matched Docs' column for empty arrays in execution_table.

If the 'let' variable was not used, how would the pipeline change?

A The pipeline could not dynamically match each order's item to products.

B The pipeline would match all products regardless of order item.

C The pipeline would fail with a syntax error.

D The pipeline would return only the first product.

💡 Hint

Refer to how 'order_item' is used in the $match with $expr in execution_table.

Concept Snapshot

$lookup with pipeline syntax:
{
  $lookup: {
    from: <collection>,
    let: { <vars> },
    pipeline: [ <stages using $$vars> ],
    as: <output array field>
  }
}

Behavior: For each main doc, runs pipeline on 'from' collection using 'let' vars.
Output: Adds matched docs as array field.
Key: Use $expr and $$var to reference 'let' vars inside pipeline.

Full Transcript

The $lookup with pipeline lets you join documents from another collection using a custom query pipeline for each document in the main collection. First, you define variables from the main document using 'let'. Then, inside the pipeline, you use $expr and $$ to compare fields dynamically. For example, matching product names to order items. The matched documents are added as an array field to each main document. If no matches are found, the array is empty. This allows advanced joins with filtering and transformations on the joined collection.