$lookup with pipeline (advanced join) in MongoDB

Introduction

$lookup with pipeline lets you join data from two collections in MongoDB with more control. It helps when you want to filter, transform, or match data during the join.

You want to join two collections but only include matching documents with specific conditions.

You need to reshape or filter the joined data before adding it to the main documents.

You want to perform complex matching that simple localField and foreignField can't handle.

You want to join and aggregate data from another collection in one query.

Syntax

MongoDB

db.collection.aggregate([
  {
    $lookup: {
      from: "otherCollection",
      let: { var1: "$field1", var2: "$field2" },
      pipeline: [
        { $match: { $expr: { /* expression using $$var1, $$var2 */ } } },
        { /* other pipeline stages */ }
      ],
      as: "joinedField"
    }
  }
])

Use let to define variables from the current collection's fields.

Inside pipeline, use $expr and $$ to access these variables for matching.

Examples

Join orders with products where the order's item matches the product name, and only include product name and price.

MongoDB

db.orders.aggregate([
  {
    $lookup: {
      from: "products",
      let: { order_item: "$item" },
      pipeline: [
        { $match: { $expr: { $eq: ["$product_name", "$$order_item"] } } },
        { $project: { product_name: 1, price: 1, _id: 0 } }
      ],
      as: "order_details"
    }
  }
])

Join students with their grades but only include grades where the score is 70 or higher.

MongoDB

db.students.aggregate([
  {
    $lookup: {
      from: "grades",
      let: { student_id: "$id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$studentId", "$$student_id"] } } },
        { $match: { score: { $gte: 70 } } }
      ],
      as: "passing_grades"
    }
  }
])

Sample Program

This query joins each employee with their department info by matching departmentId in employees to _id in departments. It only includes the department's name and location.

MongoDB

db.employees.aggregate([
  {
    $lookup: {
      from: "departments",
      let: { dept_id: "$departmentId" },
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", "$$dept_id"] } } },
        { $project: { name: 1, location: 1, _id: 0 } }
      ],
      as: "department_info"
    }
  }
])

Important Notes

Remember that $lookup with pipeline can be slower than simple joins because it runs a mini-aggregation for each document.

Use $expr inside $match to compare fields and variables.

The result of $lookup is always an array, even if it contains one or zero documents.

Summary

$lookup with pipeline lets you do advanced joins with filtering and transformations.

You define variables from the main collection using let and use them inside the pipeline.

The joined data is returned as an array in the specified as field.