Multi-table join ($lookup)

Multi-Table Joins ($lookup)

MongoDB, as a document-oriented database, typically stores related data in the form of nested documents. However, in real-world business scenarios with complex data relationships, JOIN-like operations similar to those in SQL are still needed. The $lookup stage provides the capability for cross-collection join queries, allowing documents from other collections to be brought into the current processing pipeline during aggregation.

Basic Syntax of $lookup

The basic syntax structure of the $lookup operator is as follows:

{
  $lookup: {
    from: "<target collection>",
    localField: "<input document field>",
    foreignField: "<target collection field>",
    as: "<output array field>"
  }
}

Typical example: Joining orders and products collections

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

Advanced Join Conditions

Multi-Field Joins

When multiple fields need to be combined as join conditions, the let and pipeline parameters can be used:

db.orders.aggregate([
  {
    $lookup: {
      from: "inventory",
      let: { 
        productId: "$product_id",
        warehouse: "$warehouse" 
      },
      pipeline: [
        { 
          $match: { 
            $expr: { 
              $and: [
                { $eq: ["$product", "$$productId"] },
                { $eq: ["$location", "$$warehouse"] }
              ]
            }
          }
        }
      ],
      as: "inventory_items"
    }
  }
])

Nested Document Joins

Joining fields within nested documents:

db.users.aggregate([
  {
    $lookup: {
      from: "departments",
      localField: "employment.department_id",
      foreignField: "_id",
      as: "department_info"
    }
  }
])

Join Result Processing

Unwinding Joined Arrays

Using $unwind to expand join results:

db.orders.aggregate([
  { $lookup: { ... } },
  { $unwind: "$product_details" }
])

Filtering Join Results

Performing secondary filtering on join results within the pipeline:

db.orders.aggregate([
  {
    $lookup: {
      from: "products",
      let: { pid: "$product_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", "$$pid"] } } },
        { $project: { name: 1, price: 1 } }
      ],
      as: "product_info"
    }
  }
])

Performance Optimization Strategies

Index Design

Ensure join fields are indexed:

db.products.createIndex({ _id: 1 })
db.orders.createIndex({ product_id: 1 })

Sharded Collection Joins

Considerations for joining across sharded collections:

db.orders.aggregate([
  {
    $lookup: {
      from: "products",
      localField: "product_id",
      foreignField: "_id",
      as: "matched_products",
      shardedCollection: true  // Explicitly declare target as a sharded collection
    }
  }
])

Complex Join Scenarios

Multi-Level Join Queries

Implementing three-level joins (user → order → product):

db.users.aggregate([
  {
    $lookup: {
      from: "orders",
      localField: "_id",
      foreignField: "user_id",
      as: "user_orders"
    }
  },
  { $unwind: "$user_orders" },
  {
    $lookup: {
      from: "products",
      localField: "user_orders.product_id",
      foreignField: "_id",
      as: "ordered_products"
    }
  }
])

Self-Referencing Joins

Handling tree-structured data:

db.categories.aggregate([
  {
    $lookup: {
      from: "categories",
      localField: "_id",
      foreignField: "parent_id",
      as: "children"
    }
  }
])

Alternative Solutions Comparison

Application-Level JOIN

Implementing similar functionality in Node.js:

async function getOrdersWithProducts() {
  const orders = await db.collection('orders').find().toArray();
  const productIds = orders.map(o => o.product_id);
  const products = await db.collection('products')
    .find({ _id: { $in: productIds } })
    .toArray();
  
  return orders.map(order => ({
    ...order,
    product: products.find(p => p._id.equals(order.product_id))
  }));
}

Reference Design Patterns

Example of pre-storing related data:

// Order document design
{
  _id: ObjectId("..."),
  user_id: ObjectId("..."),
  products: [
    {
      product_id: ObjectId("..."),
      name: "Smartphone",
      price: 3999
    }
  ]
}

Practical Case Analysis

E-commerce platform order query implementation:

db.orders.aggregate([
  { $match: { status: "completed" } },
  {
    $lookup: {
      from: "users",
      localField: "user_id",
      foreignField: "_id",
      as: "user_info"
    }
  },
  { $unwind: "$user_info" },
  {
    $lookup: {
      from: "products",
      localField: "items.product_id",
      foreignField: "_id",
      as: "product_list"
    }
  },
  {
    $project: {
      order_id: 1,
      total: 1,
      "user_info.name": 1,
      "user_info.email": 1,
      products: {
        $map: {
          input: "$items",
          as: "item",
          in: {
            $mergeObjects: [
              "$$item",
              {
                $arrayElemAt: [
                  {
                    $filter: {
                      input: "$product_list",
                      cond: { $eq: ["$$this._id", "$$item.product_id"] }
                    }
                  },
                  0
                ]
              }
            ]
          }
        }
      }
    }
  }
])