$lookup for joining collections in MongoDB - Step-by-Step Execution

Concept Flow - $lookup for joining collections

Start with main collection

↓

For each document in main

↓

Use $lookup to find matching docs in foreign collection

↓

Add matched docs as array field

↓

Output enriched documents

↓

End

The $lookup stage takes each document from the main collection, finds matching documents in another collection, and adds them as an array field to enrich the original document.

Execution Sample

MongoDB

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

This query joins 'orders' with 'products' by matching 'product_id' in orders to '_id' in products, adding matching product info as 'product_details'.

Execution Table

Step	Current Document (orders)	Lookup Condition	Matched Documents (products)	Output Document
1	{"_id": 1, "product_id": 101, "quantity": 2}	product_id=101 matches _id in products	[{"_id": 101, "name": "Pen", "price": 1.5}]	{"_id": 1, "product_id": 101, "quantity": 2, "product_details": [{"_id": 101, "name": "Pen", "price": 1.5}]}
2	{"_id": 2, "product_id": 102, "quantity": 1}	product_id=102 matches _id in products	[{"_id": 102, "name": "Notebook", "price": 3.0}]	{"_id": 2, "product_id": 102, "quantity": 1, "product_details": [{"_id": 102, "name": "Notebook", "price": 3.0}]}
3	{"_id": 3, "product_id": 999, "quantity": 5}	product_id=999 does not match _id in products	[]	{"_id": 3, "product_id": 999, "quantity": 5, "product_details": []}
4	No more documents	N/A	N/A	Aggregation ends

💡 All documents in 'orders' processed; aggregation completes.

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
Current Document	N/A	{"_id":1,"product_id":101,"quantity":2}	{"_id":2,"product_id":102,"quantity":1}	{"_id":3,"product_id":999,"quantity":5}	No more documents
Matched Documents	N/A	[{"_id":101,"name":"Pen","price":1.5}]	[{"_id":102,"name":"Notebook","price":3.0}]	[]	N/A
Output Document	N/A	{"_id":1,"product_id":101,"quantity":2,"product_details":[{"_id":101,"name":"Pen","price":1.5}]}	{"_id":2,"product_id":102,"quantity":1,"product_details":[{"_id":102,"name":"Notebook","price":3.0}]}	{"_id":3,"product_id":999,"quantity":5,"product_details":[]}	Aggregation ends

Key Moments - 3 Insights

Why does the 'product_details' field contain an array even if only one product matches?

Because $lookup always adds matched documents as an array, even if there is only one or zero matches, as shown in execution_table rows 1 and 3.

What happens if no matching documents are found in the foreign collection?

The matched documents array is empty, so the new field contains an empty array, as seen in execution_table row 3.

Does $lookup modify the original documents in the main collection?

No, it outputs new documents with an added field; the original documents remain unchanged, as shown by the output documents in execution_table.

Visual Quiz - 3 Questions

Look at the execution_table at Step 2. What is the value of 'product_details' in the output document?

A [{"_id": 102, "name": "Notebook", "price": 3.0}]

B []

C [{"_id": 101, "name": "Pen", "price": 1.5}]

D null

💡 Hint

Check the 'Matched Documents' and 'Output Document' columns at Step 2 in the execution_table.

At which step does the lookup find no matching documents?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look for the empty array in 'Matched Documents' column in execution_table.

If the 'product_id' field in the main document was missing, what would $lookup do?

A Return all documents from the foreign collection

B Throw an error and stop aggregation

C Return an empty array for 'product_details'

D Add null instead of an array

💡 Hint

Recall that $lookup matches on field values; missing localField means no matches, so empty array is returned.

Concept Snapshot

$lookup syntax:
{
  $lookup: {
    from: <foreign_collection>,
    localField: <field_in_main>,
    foreignField: <field_in_foreign>,
    as: <output_array_field>
  }
}

Behavior:
- For each main doc, find matching foreign docs
- Add matches as array field
- If no matches, array is empty
- Does not modify original docs

Full Transcript

The $lookup stage in MongoDB aggregation joins two collections. It takes each document from the main collection and finds matching documents in a foreign collection based on specified fields. The matches are added as an array field to the original document. Even if only one or no matches are found, the result is always an array. This process does not change the original documents but outputs enriched documents. The execution table shows step-by-step how each document is processed, matches found, and output formed. Key points include understanding that the added field is always an array and that no matches result in an empty array. The visual quiz tests understanding of these steps and outcomes.

Practice

1. What does the $lookup stage do in a MongoDB aggregation pipeline?

easy

A. It deletes documents from a collection.

B. It joins documents from two collections based on a matching field.

C. It sorts documents in ascending order.

D. It updates documents with new values.

Solution

1. Step 1: Understand the purpose of $lookup

   $lookup is used to combine documents from two collections by matching fields.

2. Step 2: Compare with other options

   Deleting, sorting, or updating documents are different operations and not related to $lookup.

3. Final Answer:

   It joins documents from two collections based on a matching field. -> Option B

4. Quick Check:

   $lookup joins collections = A [OK]

Hint: Remember: $lookup is like a join in SQL [OK]

Common Mistakes:

• Confusing $lookup with update or delete operations
• Thinking $lookup sorts documents
• Assuming $lookup modifies documents instead of joining

2. Which of the following is the correct syntax for a basic $lookup stage in MongoDB?

easy

A. { $lookup: { from: 'other', localField: 'id', foreignField: 'userId', joinAs: 'joinedData' } }

B. { $lookup: { collection: 'other', localKey: 'id', foreignKey: 'userId', output: 'joinedData' } }

C. { $lookup: { from: 'other', localField: 'id', foreignField: 'userId', as: 'joinedData' } }

D. { $lookup: { from: 'other', localField: 'id', foreignField: 'userId' } }

Solution

1. Step 1: Identify correct field names in $lookup

   The correct keys are from, localField, foreignField, and as.

2. Step 2: Check each option

   { $lookup: { from: 'other', localField: 'id', foreignField: 'userId', as: 'joinedData' } } uses all correct keys and syntax. Options A, B, and C have incorrect or missing keys.

3. Final Answer:

   { $lookup: { from: 'other', localField: 'id', foreignField: 'userId', as: 'joinedData' } } -> Option C

4. Quick Check:

   Correct $lookup keys = D [OK]

Hint: Use keys: from, localField, foreignField, as [OK]

Common Mistakes:

• Using wrong key names like collection or joinAs
• Omitting the 'as' field for output array
• Missing foreignField or localField keys

3. Given two collections: orders with documents { _id: 1, productId: 101 } and products with documents { _id: 101, name: 'Pen' }, what will be the result of this aggregation on orders?

{ $lookup: { from: 'products', localField: 'productId', foreignField: '_id', as: 'productInfo' } }

medium

A. [{ _id: 1, productId: 101, productInfo: [{ _id: 101, name: 'Pen' }] }]

B. [{ _id: 1, productId: 101, productInfo: [] }]

C. [{ _id: 1, productId: 101 }]

D. SyntaxError

Solution

1. Step 1: Match localField and foreignField values

   The productId in orders is 101, which matches _id 101 in products.

2. Step 2: Understand $lookup output

   The matching product document is added as an array in productInfo.

3. Final Answer:

   [{ _id: 1, productId: 101, productInfo: [{ _id: 101, name: 'Pen' }] }] -> Option A

4. Quick Check:

   Matching product added in array = B [OK]

Hint: Matched docs appear in array named by 'as' [OK]

Common Mistakes:

• Expecting productInfo to be a single object, not an array
• Thinking no match means error instead of empty array
• Ignoring the 'as' field name in output

4. What is wrong with this $lookup stage?

{ $lookup: { from: 'users', localField: 'userId', foreignField: 'id', as: 'userDetails' } }

Assuming the users collection uses _id as the user ID field.

medium

A. The 'as' field cannot be 'userDetails'.

B. The localField should be 'id' instead of 'userId'.

C. The 'from' field should be 'user' not 'users'.

D. The foreignField should be '_id' instead of 'id'.

Solution

1. Step 1: Check foreignField correctness

   The users collection uses _id as the ID field, so foreignField must be '_id'.

2. Step 2: Verify other fields

   localField and as are valid; collection name 'users' is correct.

3. Final Answer:

   The foreignField should be '_id' instead of 'id'. -> Option D

4. Quick Check:

   foreignField must match actual field = C [OK]

Hint: Match foreignField to actual field name in other collection [OK]

Common Mistakes:

• Using wrong foreignField name
• Changing 'as' field unnecessarily
• Mistaking collection name pluralization

5. You have two collections: students with { _id: 1, name: 'Alice' } and grades with multiple documents per student like { studentId: 1, score: 85 }. How do you write a $lookup to get each student with all their grades in an array called allGrades?

hard

A. { $lookup: { from: 'grades', localField: '_id', foreignField: 'studentId', as: 'allGrades' } }

B. { $lookup: { from: 'grades', localField: 'studentId', foreignField: '_id', as: 'allGrades' } }

C. { $lookup: { from: 'grades', localField: 'name', foreignField: 'studentId', as: 'allGrades' } }

D. { $lookup: { from: 'grades', localField: '_id', foreignField: 'score', as: 'allGrades' } }

Solution

1. Step 1: Identify matching fields between collections

   students._id matches grades.studentId, so these are the fields to join on.

2. Step 2: Write $lookup with correct fields and output array name

   Use from: 'grades', localField: '_id', foreignField: 'studentId', and as: 'allGrades'.

3. Final Answer:

   { $lookup: { from: 'grades', localField: '_id', foreignField: 'studentId', as: 'allGrades' } } -> Option A

4. Quick Check:

   Match student _id to grades studentId = A [OK]

Hint: Join on matching ID fields, output array named by 'as' [OK]

Common Mistakes:

• Swapping localField and foreignField
• Using wrong fields like name or score for join
• Incorrect output array name