$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

Need a 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

Need a 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

Need a 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
])

Need a hint?

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