The $lookup stage in MongoDB's aggregation framework performs a left outer join to a collection in the same database to filter in documents from the “joined” collection for processing. It allows for joining data across collections, which is particularly useful for combining related data stored in different collections.

Syntax

{
  $lookup: {
    from: <collection>,
    localField: <field>,
    foreignField: <field>,
    as: <newField>
  }
}

• $lookup: The operator used to specify the lookup stage.
• from: The name of the collection to join.
• localField: The field from the input documents.
• foreignField: The field from the documents of the “joined” collection.
• as: The name of the new array field to add to the input documents.

Example Setup

First, let's insert some documents into the orders and customers collections to work with:

db.customers.insertMany([
  { _id: 1, name: "Alice", age: 28 },
  { _id: 2, name: "Bob", age: 34 },
  { _id: 3, name: "Charlie", age: 25 }
])

db.orders.insertMany([
  { order_id: 1, product: "apple", quantity: 10, customer_id: 1 },
  { order_id: 2, product: "banana", quantity: 5, customer_id: 2 },
  { order_id: 3, product: "orange", quantity: 8, customer_id: 1 },
  { order_id: 4, product: "apple", quantity: 15, customer_id: 3 },
  { order_id: 5, product: "banana", quantity: 7, customer_id: 2 }
])

Example 1: Basic Usage of $lookup

Join the orders collection with the customers collection to add customer details to each order.

Pipeline:

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "_id",
      as: "customerDetails"
    }
  }
])

Explanation:

• from: Specifies the customers collection to join.
• localField: The customer_id field in the orders collection.
• foreignField: The _id field in the customers collection.
• as: The name of the new array field to store the joined data (customerDetails).

This command adds customer details to each order, resulting in documents like:

{
  "order_id": 1,
  "product": "apple",
  "quantity": 10,
  "customer_id": 1,
  "customerDetails": [
    { "_id": 1, "name": "Alice", "age": 28 }
  ]
}

Example 2: Joining and Unwinding

Join the orders collection with the customers collection and unwind the customerDetails array.

Pipeline:

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "_id",
      as: "customerDetails"
    }
  },
  { $unwind: "$customerDetails" }
])

Explanation:

• $lookup: Joins the orders collection with the customers collection.
• $unwind: Deconstructs the customerDetails array field from the joined documents, outputting a document for each element in the array.

This results in documents with flattened customer details:

{
  "order_id": 1,
  "product": "apple",
  "quantity": 10,
  "customer_id": 1,
  "customerDetails": { "_id": 1, "name": "Alice", "age": 28 }
}

Example 3: Using $lookup with Multiple Conditions

Join the orders collection with the customers collection and include only customers older than 30 years.

Pipeline:

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      let: { customer_id: "$customer_id" },
      pipeline: [
        { $match: { $expr: { $and: [{ $eq: ["$_id", "$$customer_id"] }, { $gt: ["$age", 30] }] } } }
      ],
      as: "customerDetails"
    }
  },
  { $unwind: "$customerDetails" }
])

Explanation:

• let: Defines variables to use in the lookup's pipeline.
• pipeline: Specifies the conditions for the join. It filters customers where _id matches customer_id from orders and age is greater than 30.
• $unwind: Deconstructs the customerDetails array.

This results in orders with customer details where the customers are older than 30:

{
  "order_id": 2,
  "product": "banana",
  "quantity": 5,
  "customer_id": 2,
  "customerDetails": { "_id": 2, "name": "Bob", "age": 34 }
}

Use Cases

1. Data Enrichment: Enhance documents with additional information from related collections, such as adding customer details to orders.
2. Comprehensive Reporting: Generate detailed reports by combining data from multiple collections, such as sales reports including customer demographics.
3. Data Integration: Integrate and correlate data from different sources within the database, such as linking transactions to customer profiles.

The $lookup stage is a powerful tool in MongoDB's aggregation framework for performing joins across collections. By understanding how to use the $lookup stage effectively, you can enrich your data, generate comprehensive reports, and integrate information from different sources.