MongoDB Advanced Aggregation | $lookup, $unwind, $facet

# CHAPTER 16

Advanced Aggregation Pipelines #

1. Introduction #

2. Learning Objectives #

• Perform Left Outer JOINs using the $lookup stage.

• Deconstruct arrays into individual documents using $unwind.

• Execute multi-pipeline analytics using $facet.

• Understand pipeline optimization strategies.

3. The $lookup Stage (The MongoDB JOIN) #

Let's assume we have users and orders. We are querying orders, but we need to attach the User details.

db.orders.aggregate([
    { $lookup: {
        from: "users",             // 1. The foreign collection to join with
        localField: "user_id",     // 2. The ID field in the current 'orders' collection
        foreignField: "_id",       // 3. The ID field in the 'users' collection
        as: "customer_details"     // 4. The name of the new Array that will hold the joined data!
    }}
])

*Output: The order document now contains a new array called customerdetails containing the joined user data!*

*(If the array has 3 items, $unwind will literally duplicate the document 3 times, attaching one item to each document!)*

db.orders.aggregate([
    // Stage 1: JOIN the data (creates an array)
    { $lookup: {
        from: "users", localField: "user_id", foreignField: "_id", as: "customer"
    }},
    
    // Stage 2: FLATTEN the array into a clean object!
    { $unwind: "$customer" } 
])

5. Multi-Processing with $facet #

db.products.aggregate([
    { $facet: {
        "Categorized_Counts": [ // Sub-Pipeline 1
            { $group: { _id: "$category", count: { $sum: 1 } } }
        ],
        "Top_Expensive_Items": [ // Sub-Pipeline 2
            { $sort: { price: -1 } },
            { $limit: 5 }
        ]
    }}
])

*(MongoDB processes both pipelines in parallel and returns a single JSON object containing both result sets!)*

6. Pipeline Optimization (The Query Planner) #

• Rule 1: Always filter ($match) as early as possible to reduce the document count.

• Rule 2: If you have a $sort followed by a $limit, MongoDB will optimize them together using a "Top-K" algorithm, vastly reducing RAM usage.

• Rule 3: Never put an $unwind before a $match if the filter doesn't require the unwound data. You will multiply the documents unnecessarily!

7. Mini Project: The Master Sales Report #

db.orders.aggregate([
    // 1. Filter
    { $match: { status: "Completed" } },
    
    // 2. Join Customer Data
    { $lookup: {
        from: "customers", localField: "customer_id", foreignField: "_id", as: "buyer"
    }},
    
    // 3. Flatten the Buyer Array
    { $unwind: "$buyer" },
    
    // 4. Group by Customer Name and Sum Revenue
    { $group: {
        _id: "$buyer.full_name", // Accessing the joined data!
        total_purchased: { $sum: "$total_price" }
    }},
    
    // 5. Format Output
    { $project: {
        _id: 0,
        customer_name: "$_id",
        revenue: "$total_purchased"
    }}
])

8. Common Mistakes #

• Expecting INNER JOIN behavior: $lookup is fundamentally a LEFT OUTER JOIN. If orders has a userid that doesn't exist in the users collection, $lookup will still return the order document, but the customerdetails array will just be empty [].

• Using $lookup to fix bad schema: If you are using $lookup to join 5 collections together on every single page load, you are using MongoDB incorrectly. You should have embedded that data! (Review Chapter 14).

9. Best Practices #

• Index the foreignField: If you are performing a $lookup, you MUST ensure that the foreignField in the target collection has a B-Tree Index. If it does not, the pipeline will perform a devastating Collection Scan for every single document flowing through the pipeline!

10. Exercises #

1. 1. What aggregation stage is the NoSQL equivalent of a SQL JOIN?

1. 2. If a document enters an $unwind stage with an array containing 4 items, how many documents will exit the $unwind stage?

11. MongoDB Challenges #

{ $lookup: {
    from: "reviews",
    localField: "_id",
    foreignField: "product_id",
    as: "product_reviews"
}}

12. MCQ Quiz with Answers #

13. Interview Questions #

• Q: Explain how the $lookup stage mimics a SQL Left Outer Join. Describe the exact data structure it outputs (e.g., an Array or an Object).

• Q: Describe a complex analytics dashboard scenario where utilizing the $facet aggregation stage would significantly improve overall backend performance.

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #