4.3 Multi-Document Joins & Faceted Pipelines ($lookup, $facet, $bucket)

4.3 Multi-Document Joins & Faceted Pipelines ($lookup, $facet, $bucket)

While MongoDB's document model encourages embedding related data for high-performance atomic operations, complex enterprise domains often require referencing normalized collections across different entities (such as orders referencing customers, products, or inventory). Furthermore, modern web applications require faceted search navigation (e.g., displaying price distributions, category filters, and paginated search results simultaneously).

The MongoDB Aggregation Framework provides the $lookup, $facet, $bucket, and $bucketAuto stages to handle relational joins, multi-pipeline analytical branching, and statistical bucket partitioning.

---

1. The $lookup Stage: Left Outer Joins

The $lookup stage performs a left outer join to an un-sharded collection in the same database to filter in documents from the "joined" collection for processing. MongoDB supports two distinct $lookup syntax paradigms:

1. Standard Equality Join (Single field matching)
2. Correlated Subquery Join (Expressive joins with variables and nested pipelines)

+-----------------------------------------------------------------------------------------+
|                              $lookup Left Outer Join Flow                               |
|                                                                                         |
|  Orders Collection (Input)        Customers Collection (from)       Output Document     |
|  { orderId: 101, custId: 'C1' } + { _id: 'C1', name: 'Alice' } ---> { orderId: 101,    |
|                                                                     custId: 'C1',      |
|                                                                     customerDetails: [ |
|                                                                       { _id: 'C1', ...}|
|                                                                     ] }                |
+-----------------------------------------------------------------------------------------+

Core Rule: $lookup always outputs matched documents as an Array, even if exactly one matching document is found. If no matching documents are found in the foreign collection, $lookup outputs an empty array ([]).

---

2. Standard Equality Join Syntax

For straightforward 1-to-1 or 1-to-many joins where a field in the input collection equals a field in the foreign collection, use the four-argument equality syntax:

Syntax

{
  $lookup: {
    from: "<foreignCollection>",
    localField: "<inputField>",
    foreignField: "<foreignCollectionField>",
    as: "<outputArrayField>"
  }
}

Parameter Definitions

Example: Joining Orders with User Profiles

db.orders.aggregate([
  {
    $match: { status: "PLACED" }
  },
  {
    $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customerInfo"
    }
  },
  {
    // Optional: Flatten 1-to-1 array join using $unwind
    $unwind: {
      path: "$customerInfo",
      preserveNullAndEmptyArrays: true
    }
  }
]);

Performance Tip: Ensure an index exists on the foreignField in the foreign collection. Without an index on foreignField, MongoDB must perform a full collection scan (COLLSCAN) on the target collection for every single document in the input pipeline stream.

---

3. Correlated Subquery Join Syntax (let and pipeline)

When join criteria require complex conditions (e.g., matching multiple fields, applying inequality comparisons, or pre-sorting and limiting joined records), use the let and pipeline syntax.

Syntax

{
  $lookup: {
    from: "<foreignCollection>",
    let: { <var1>: "$<localField1>", <var2>: "$<localField2>" },
    pipeline: [
      { <stage 1> },
      { <stage 2> }
    ],
    as: "<outputArrayField>"
  }
}

Mechanics of Correlated Joins

1. let: Declares variables populated from fields in the current input document. Inside the sub-pipeline, reference these variables using double dollar signs ($$<varName>).
2. pipeline: An independent aggregation pipeline executed against the from collection. Within the sub-pipeline, standard top-level fields refer to the foreign collection, while $$<var> refers to the bound input variable.
3. $expr: Because standard query operators cannot compare a foreign field to a variable, $match inside the sub-pipeline must use the $expr operator.

Example: Joining Only Recent High-Value Orders for Each Customer

db.customers.aggregate([
  {
    $lookup: {
      from: "orders",
      let: { custId: "$_id", minSpend: 100 },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: [ "$customerId", "$$custId" ] },
                { $gte: [ "$totalAmount", "$$minSpend" ] }
              ]
            }
          }
        },
        { $sort: { orderDate: -1 } },
        { $limit: 3 }, // Retrieve only top 3 most recent qualifying orders
        { $project: { customerId: 0 } }
      ],
      as: "topRecentOrders"
    }
  }
]);

---

4. Multi-Faceted Pipelines: The $facet Stage

In standard aggregation, documents flow linearly from one stage to the next. However, search interfaces frequently require displaying multi-faceted metadata—such as total matching item counts, price ranges, manufacturer distributions, and a paginated slice of records—all from a single user query.

The $facet stage enables multi-faceted aggregations by executing multiple independent sub-pipelines in parallel over the same input document stream within a single query.

+-----------------------------------------------------------------------------------------+
|                                 $facet Execution Architecture                           |
|                                                                                         |
|                                 +---> Sub-Pipeline 1: Categorize by Department          |
|                                 |                                                       |
|  Input Filtered Stream ($match) +---> Sub-Pipeline 2: Price Range Distribution ($bucket)|
|                                 |                                                       |
|                                 +---> Sub-Pipeline 3: Paginated Results ($skip, $limit) |
+-----------------------------------------------------------------------------------------+

Syntax

{
  $facet: {
    <outputField1>: [ <stage1>, <stage2>, ... ],
    <outputField2>: [ <stage1>, <stage2>, ... ],
    <outputField3>: [ <stage1>, <stage2>, ... ]
  }
}

Example: E-Commerce Search, Counts, and Pagination in One Query

db.products.aggregate([
  // 1. Initial global filter utilizing indexes
  {
    $match: { inStock: true, category: "Electronics" }
  },
  // 2. Faceted analytical branching
  {
    $facet: {
      // Facet A: Price distribution
      priceStats: [
        {
          $group: {
            _id: null,
            minPrice: { $min: "$price" },
            avgPrice: { $avg: "$price" },
            maxPrice: { $max: "$price" }
          }
        }
      ],
      // Facet B: Top brands breakdown
      brandCounts: [
        { $group: { _id: "$brand", count: { $sum: 1 } } },
        { $sort: { count: -1 } },
        { $limit: 5 }
      ],
      // Facet C: Paginated document payload + total count
      paginatedResults: [
        { $sort: { rating: -1, price: 1 } },
        { $skip: 0 },
        { $limit: 10 },
        { $project: { name: 1, price: 1, rating: 1, brand: 1 } }
      ]
    }
  }
]);

Return Document Structure of $facet

$facet outputs a single document containing an array for each defined facet:

{
  "priceStats": [{ "_id": null, "minPrice": 19.99, "avgPrice": 142.50, "maxPrice": 899.99 }],
  "brandCounts": [
    { "_id": "Sony", "count": 45 },
    { "_id": "Samsung", "count": 38 }
  ],
  "paginatedResults": [
    { "_id": 101, "name": "Wireless Headphones", "price": 79.99, "rating": 4.8, "brand": "Sony" }
  ]
}

Key Restrictions: The output document of $facet is subject to the 16 MB BSON size limit. Additionally, sub-pipelines inside $facet cannot contain nested $facet, $out, or $merge stages.

---

5. Analytical Data Partitioning: $bucket vs. $bucketAuto

Grouping continuous numerical values or dates into discrete ranges is essential for histograms and facet filters.

1. The $bucket Stage (Manual Boundaries)

$bucket groups incoming documents into specific intervals based on user-defined boundary values.

db.products.aggregate([
  {
    $bucket: {
      groupBy: "$price", // Field or expression to bucket by
      boundaries: [ 0, 50, 100, 500, 1000 ], // Lower bound inclusive, upper bound exclusive [0, 50), [50, 100)...
      default: "Other / Premium", // Bucket for values outside the boundaries
      output: {
        itemCount: { $sum: 1 },
        products: { $push: "$title" },
        averagePrice: { $avg: "$price" }
      }
    }
  }
]);

2. The $bucketAuto Stage (Automatic Boundaries)

$bucketAuto automatically calculates boundaries to divide the document stream into a specified number of evenly distributed buckets using quantiles.

db.customers.aggregate([
  {
    $bucketAuto: {
      groupBy: "$totalLifetimeSpend",
      buckets: 4, // Partition into 4 evenly populated quartiles
      output: {
        customerCount: { $sum: 1 },
        minSpend: { $min: "$totalLifetimeSpend" },
        maxSpend: { $max: "$totalLifetimeSpend" }
      }
    }
  }
]);

Comparison: $bucket vs. $bucketAuto