$lookup with pipeline (advanced join) in MongoDB - Mini Project: Build & Apply

$lookup with pipeline (advanced join) in MongoDB

📖 Scenario: You are managing a MongoDB database for a bookstore. There are two collections: books and reviews. Each book has an _id and a title. The reviews collection stores customer reviews with fields book_id (referencing books._id), rating (1 to 5), and comment.You want to create a query that joins books with their reviews, but only includes reviews with a rating of 4 or higher. Also, you want to calculate the average rating for each book from these filtered reviews.

🎯 Goal: Build a MongoDB aggregation pipeline using $lookup with a pipeline to join books with filtered reviews (rating ≥ 4), and add a field averageHighRating showing the average of these high ratings.

📋 What You'll Learn

Create a books collection with exactly 3 books with specified _id and title.

Create a reviews collection with exactly 5 reviews referencing the books, with various ratings.

Write an aggregation on books using $lookup with a pipeline to join only reviews with rating >= 4.

Add a field averageHighRating to each book showing the average rating of the joined reviews.

💡 Why This Matters

🌍 Real World

Filtering and joining related data with conditions is common in real-world databases, such as showing only positive reviews for products.

💼 Career

Understanding $lookup with pipeline is important for MongoDB developers to write efficient and powerful queries for complex data relationships.

1

Create the books collection with 3 books

Insert these exact documents into a collection called books: { _id: 1, title: "The Great Gatsby" }, { _id: 2, title: "1984" }, and { _id: 3, title: "To Kill a Mockingbird" }.

MongoDB

// Insert the 3 books into the books collection
// Your code here

Hint

Use db.books.insertMany() with an array of the 3 book documents.

2

Create the reviews collection with 5 reviews

Insert these exact documents into a collection called reviews: { book_id: 1, rating: 5, comment: "Excellent" }, { book_id: 1, rating: 3, comment: "Good" }, { book_id: 2, rating: 4, comment: "Very good" }, { book_id: 3, rating: 2, comment: "Not great" }, and { book_id: 3, rating: 5, comment: "Loved it" }.

MongoDB

db.books.insertMany([
  { _id: 1, title: "The Great Gatsby" },
  { _id: 2, title: "1984" },
  { _id: 3, title: "To Kill a Mockingbird" }
])
// Insert the 5 reviews into the reviews collection
// Your code here

Hint

Use db.reviews.insertMany() with an array of the 5 review documents.

3

Write the aggregation with $lookup using a pipeline

Write an aggregation on books using db.books.aggregate() with a $lookup stage. Use the pipeline option inside $lookup to join only reviews where rating is greater than or equal to 4. Use let to pass bookId as $_id from books to the pipeline. Store the joined reviews in a field called highRatedReviews.

MongoDB

db.books.insertMany([
  { _id: 1, title: "The Great Gatsby" },
  { _id: 2, title: "1984" },
  { _id: 3, title: "To Kill a Mockingbird" }
])
db.reviews.insertMany([
  { book_id: 1, rating: 5, comment: "Excellent" },
  { book_id: 1, rating: 3, comment: "Good" },
  { book_id: 2, rating: 4, comment: "Very good" },
  { book_id: 3, rating: 2, comment: "Not great" },
  { book_id: 3, rating: 5, comment: "Loved it" }
])
// Write the aggregation with $lookup and pipeline to join high rated reviews
// Your code here

Hint

Use $lookup with let to pass bookId, then in pipeline use $match with $expr to filter reviews by book_id and rating ≥ 4.

4

Add averageHighRating field with average rating of joined reviews

Extend the aggregation pipeline by adding a $addFields stage after $lookup. Create a new field called averageHighRating that calculates the average of the rating values in the highRatedReviews array using $avg. If there are no high rated reviews, the average should be null.

MongoDB

db.books.insertMany([
  { _id: 1, title: "The Great Gatsby" },
  { _id: 2, title: "1984" },
  { _id: 3, title: "To Kill a Mockingbird" }
])
db.reviews.insertMany([
  { book_id: 1, rating: 5, comment: "Excellent" },
  { book_id: 1, rating: 3, comment: "Good" },
  { book_id: 2, rating: 4, comment: "Very good" },
  { book_id: 3, rating: 2, comment: "Not great" },
  { book_id: 3, rating: 5, comment: "Loved it" }
])
db.books.aggregate([
  {
    $lookup: {
      from: "reviews",
      let: { bookId: "$_id" },
      pipeline: [
        { $match: { $expr: { $and: [ { $eq: ["$book_id", "$${bookId}"] }, { $gte: ["$rating", 4] } ] } } }
      ],
      as: "highRatedReviews"
    }
  },
  // Add $addFields stage to calculate averageHighRating
  // Your code here
])

Hint

Use $addFields with averageHighRating set to { $avg: "$highRatedReviews.rating" }.

Practice

1. What is the main purpose of using $lookup with a pipeline in MongoDB?

easy

A. To create indexes on joined collections automatically

B. To update documents in the joined collection

C. To perform advanced joins with filtering and transformations on the joined data

D. To delete documents from multiple collections at once

Solution

1. Step 1: Understand $lookup basic function

   $lookup is used to join documents from another collection into the current one.

2. Step 2: Role of pipeline in $lookup

   Using a pipeline allows filtering and transforming the joined documents before adding them.

3. Final Answer:

   To perform advanced joins with filtering and transformations on the joined data -> Option C

4. Quick Check:

   $lookup with pipeline = advanced join [OK]

Hint: Remember: pipeline lets you filter joined data inside $lookup [OK]

Common Mistakes:

• Thinking $lookup with pipeline updates or deletes data
• Confusing $lookup with index creation
• Assuming $lookup merges collections permanently

2. Which of the following is the correct syntax to define variables for use inside the pipeline of a $lookup stage?

easy

A. "params: { var1: \"$field1\", var2: \"$field2\" }"

B. "vars: { var1: \"$field1\", var2: \"$field2\" }"

C. "variables: [ var1: \"$field1\", var2: \"$field2\" ]"

D. "let: { var1: \"$field1\", var2: \"$field2\" }"

Solution

1. Step 1: Recall $lookup variable syntax

   Variables for the pipeline are defined using the let keyword as an object.

2. Step 2: Match correct keyword and structure

   Only let with an object of key-value pairs is valid syntax.

3. Final Answer:

   "let: { var1: \"$field1\", var2: \"$field2\" }" -> Option D

4. Quick Check:

   Variables in $lookup pipeline = let [OK]

Hint: Use 'let' keyword with object to define variables in $lookup [OK]

Common Mistakes:

• Using 'vars' or 'variables' instead of 'let'
• Using array syntax instead of object for variables
• Omitting the $ sign before field names

3. Given collections orders and products, what will be the output of this aggregation on orders?

{
  $lookup: {
    from: "products",
    let: { prod_id: "$productId" },
    pipeline: [
      { $match: { $expr: { $eq: ["$_id", "$$prod_id"] } } },
      { $project: { name: 1, price: 1, _id: 0 } }
    ],
    as: "productDetails"
  }
}

medium

A. Each order document will have a 'productDetails' field with only product IDs

B. Each order document will have a new field 'productDetails' containing an array with matching product's name and price

C. The query will throw a syntax error due to incorrect variable reference

D. The 'productDetails' field will be a single object, not an array

Solution

1. Step 1: Understand $lookup with pipeline and let

   The let defines prod_id from orders.productId. The pipeline matches products._id to this variable.

2. Step 2: Analyze pipeline stages

   The pipeline filters products matching the order's productId and projects only name and price fields, excluding _id.

3. Final Answer:

   Each order document will have a new field 'productDetails' containing an array with matching product's name and price -> Option B

4. Quick Check:

   $lookup with pipeline returns array of matched docs [OK]

Hint: Remember $lookup with pipeline returns array in 'as' field [OK]

Common Mistakes:

• Expecting a single object instead of an array
• Using wrong variable reference syntax like '$prod_id' instead of '$$prod_id'
• Forgetting to project fields inside pipeline

4. Identify the error in this $lookup stage:

{
  $lookup: {
    from: "employees",
    let: { empId: "$employeeId" },
    pipeline: [
      { $match: { $expr: { $eq: ["$_id", "$$empId"] } } }
    ],
    as: "employeeInfo"
  }
}

medium

A. Variable reference inside $expr should be "$$empId" instead of "$empId"

B. The let keyword is not allowed inside $lookup

C. The from field must be an array, not a string

D. The as field cannot be named employeeInfo

Solution

1. Step 1: Check variable usage in $expr

   Variables defined in let must be referenced with double dollar signs $$ inside the pipeline.

2. Step 2: Identify incorrect variable reference

   The code uses "$empId" instead of "$$empId", causing a runtime error.

3. Final Answer:

   Variable reference inside $expr should be "$$empId" instead of "$empId" -> Option A

4. Quick Check:

   Use $$ to reference let variables inside pipeline [OK]

Hint: Use double $$ to reference let variables inside pipeline [OK]

Common Mistakes:

• Using single $ instead of double $$ for let variables
• Misplacing let outside $lookup
• Incorrect data type for 'from' field

5. You want to join orders with products but only include products priced above 100 in the joined array. Which $lookup pipeline correctly achieves this?

hard

A. { $lookup: { from: "products", let: { pid: "$productId" }, pipeline: [ { $match: { $expr: { $and: [ { $eq: ["$_id", "$$pid"] }, { $gt: ["$price", 100] } ] } } } ], as: "expensiveProduct" } }

B. { $lookup: { from: "products", localField: "productId", foreignField: "_id", as: "expensiveProduct", pipeline: [ { $match: { price: { $gt: 100 } } } ] } }

C. { $lookup: { from: "products", let: { pid: "$productId" }, pipeline: [ { $match: { _id: "$$pid", price: { $gt: 100 } } } ], as: "expensiveProduct" } }

D. { $lookup: { from: "products", let: { pid: "$productId" }, pipeline: [ { $match: { $expr: { $eq: ["$_id", "$$pid"] } } }, { $match: { price: { $gt: 100 } } } ], as: "expensiveProduct" } }

Solution

1. Step 1: Understand filtering with $expr and $and

   We must match documents where _id equals the variable and price is greater than 100 in a single $match using $expr and $and.

2. Step 2: Analyze each option's pipeline

   { $lookup: { from: "products", let: { pid: "$productId" }, pipeline: [ { $match: { $expr: { $and: [ { $eq: ["$_id", "$$pid"] }, { $gt: ["$price", 100] } ] } } } ], as: "expensiveProduct" } } correctly uses $expr with $and combining both conditions. { $lookup: { from: "products", localField: "productId", foreignField: "_id", as: "expensiveProduct", pipeline: [ { $match: { price: { $gt: 100 } } } ] } } wrongly uses pipeline with localField/foreignField which is invalid. { $lookup: { from: "products", let: { pid: "$productId" }, pipeline: [ { $match: { _id: "$$pid", price: { $gt: 100 } } } ], as: "expensiveProduct" } } uses invalid $match syntax inside pipeline. { $lookup: { from: "products", let: { pid: "$productId" }, pipeline: [ { $match: { $expr: { $eq: ["$_id", "$$pid"] } } }, { $match: { price: { $gt: 100 } } } ], as: "expensiveProduct" } } splits conditions into two $match stages, but second $match cannot use field without $expr inside pipeline.

3. Final Answer:

   Option A correctly filters joined products priced above 100 using $expr and $and -> Option A

4. Quick Check:

   Use $expr with $and for multiple conditions in $lookup pipeline [OK]

Hint: Combine conditions inside one $match using $expr and $and [OK]

Common Mistakes:

• Using localField/foreignField with pipeline together
• Splitting conditions into multiple $match without $expr
• Incorrect $match syntax inside pipeline