Hiprup

What is the $lookup stage in aggregation?

$lookup is the aggregation stage that performs a left outer join with another collection. The closest thing MongoDB has to a SQL JOIN.

  • Inputs — from (other collection), localField, foreignField, as (output array name).

  • Output — matched documents are added as an array under the as field.

  • Sub-pipeline form — let + pipeline for complex join conditions and projections.

  • Performance caveat — can be slow on sharded collections; index the foreign field.

  • Use sparingly — if you constantly need $lookup, the data model probably belongs embedded.

// Basic $lookup (field equality)
db.orders.aggregate([
  { $lookup: {
    from: 'products',          // Collection to join
    localField: 'productId',   // Field in orders
    foreignField: '_id',       // Field in products
    as: 'product'              // Output field (array)
  }},
  { $unwind: '$product' },     // Flatten array to object
  { $project: {
    orderDate: 1,
    'product.name': 1,
    'product.price': 1,
    quantity: 1,
    total: { $multiply: ['$quantity', '$product.price'] }
  }}
]);

// Pipeline $lookup (advanced — multiple conditions)
db.orders.aggregate([
  { $lookup: {
    from: 'inventory',
    let: { productId: '$productId', qty: '$quantity' },
    pipeline: [
      { $match: {
        $expr: {
          $and: [
            { $eq: ['$productId', '$$productId'] },
            { $gte: ['$stock', '$$qty'] }  // Only if enough stock
          ]
        }
      }}
    ],
    as: 'availableInventory'
  }}
]);

Basic $lookup joins orders with products on productId/_id. $unwind converts the product array to a single object. $project reshapes the output and calculates the total. Pipeline $lookup uses let/pipeline for complex joins: matches productId AND checks if stock >= order quantity. $$productId references the let variable.

$lookup = LEFT OUTER JOIN. Always produces an array (use $unwind for one-to-one). Basic syntax for simple field equality.

Pipeline syntax for complex conditions (multiple fields, filters). Performance: $lookup is expensive on large collections — consider embedding for frequently joined data.

What is the $lookup stage in aggregation? | Hiprup