In MongoDB, the $lookup operator is a powerful tool used for performing left outer joins between documents from two collections. This operator empowers MongoDB users to merge data from disparate collections based on specified criteria and offers a versatile method to enhance and analyze data across multiple documents.

In this article, We will learn about MongoDB Aggregation $lookup along with multiple examples in detail.

MongoDB Aggregation $lookup

• The $lookup operator in MongoDB allows us to perform a left outer join between documents from two collections.
• This means we can perform the query of documents from one collection with data from another collection based on a specified matching condition.
• It’s useful for combining data from different collections for analysis or reporting purposes.
• The operator can handle cases where there are no matching documents in the foreign collection, providing flexibility in data retrieval.

Syntax:

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

{
  $lookup: {
    from: <foreignCollection>,
    localField: <fieldInInputDocument>,
    foreignField: <fieldInForeignDocument>,
    as: <outputArrayField>
  }
}

Explanation:

• from: The name of the foreign collection to join with.
• localField: The field from the input documents that will be used for matching.
• foreignField: The field from the foreign collection that will be used for matching.
• as: The name of the output array field that will contain the joined documents.

Let’s set up an Environment:

To understand MongoDB Aggregation $lookup we need a collection and some documents on which we will perform various operations and queries. Here we will consider a collection called orders and customers which contains various information shown as below:

Collection: orders

[
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a86e"),
    "order_number": "ORD001",
    "customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
    "customer_details": [
      {
        "_id": ObjectId("60f9d7ac345b7c9df348a86d"),
        "name": "John Doe",
        "email": "[email protected]"
      }
    ]
  },
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a86f"),
    "order_number": "ORD002",
    "customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
    "customer_details": [
      {
        "_id": ObjectId("60f9d7ac345b7c9df348a86d"),
        "name": "John Doe",
        "email": "[email protected]"
      }
    ]
  },
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a870"),
    "order_number": "ORD003",
    "customer_id": ObjectId("60f9d7ac345b7c9df348a86e"),
    "customer_details": [
      {
        "_id": ObjectId("60f9d7ac345b7c9df348a86e"),
        "name": "Alice Smith",
        "email": "[email protected]"
      }
    ]
  },
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a871"),
    "order_number": "ORD004",
    "customer_id": ObjectId("60f9d7ac345b7c9df348a86f"),
    "customer_details": [
      {
        "_id": ObjectId("60f9d7ac345b7c9df348a86f"),
        "name": "Bob Johnson",
        "email": "[email protected]"
      }
    ]
  },
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a872"),
    "order_number": "ORD005",
    "customer_id": ObjectId("60f9d7ac345b7c9df348a86f"),
    "customer_details": [
      {
        "_id": ObjectId("60f9d7ac345b7c9df348a86f"),
        "name": "Bob Johnson",
        "email": "[email protected]"
      }
    ]
  }
]

Collection: customers

[
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a86d"),
    "name": "John Doe",
    "email": "[email protected]"
  },
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a86e"),
    "name": "Alice Smith",
    "email": "[email protected]"
  },
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a86f"),
    "name": "Bob Johnson",
    "email": "[email protected]"
  }
]

Example of MongoDB Aggregation $lookup

Example 1: Perform a Single Equality Join with $lookup

Let’s Retrieve orders from the orders collection along with corresponding customer details from the customers collection based on matching customer_id and _id.

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

Output:

[
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a86e"),
    "order_number": "ORD001",
    "customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
    "customer_details": [
      {
        "_id": ObjectId("60f9d7ac345b7c9df348a86d"),
        "name": "John Doe",
        "email": "[email protected]"
      }
    ]
  },
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a86f"),
    "order_number": "ORD002",
    "customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
    "customer_details": [
      {
        "_id": ObjectId("60f9d7ac345b7c9df348a86d"),
        "name": "John Doe",
        "email": "[email protected]"
      }
    ]
  },
  // Other order documents with appended customer details...
]

Explanation: This query performs a left outer join ($lookup) between the orders collection and the customers collection. It matches documents from orders where customer_id equals _id in customers, appending the matched customer details into an array field named customer_details within each order document.

Example 2: Use $lookup with an Array

By default, the $lookup operator includes an array field (as) in the output documents, even if no matches are found in the foreign collection. This array field will be empty ([]) for unmatched documents.

Example:

Continuing from the previous example, suppose there are orders with customer_id values that do not exist in the customers collection. The $lookup operator will still include these orders in the output, with an empty customer_details array for unmatched documents.

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      let: { customerId: "$customer_id" },
      pipeline: [
        {
          $match: {
            $expr: { $in: ["$_id", "$$customerId"] }
          }
        }
      ],
      as: "customer_details"
    }
  }
])

Output:

[
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a86e"),
    "order_number": "ORD001",
    "customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
    "customer_details": [
      {
        "_id": ObjectId("60f9d7ac345b7c9df348a86d"),
        "name": "John Doe",
        "email": "[email protected]"
      }
    ]
  },
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a86f"),
    "order_number": "ORD002",
    "customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
    "customer_details": [
      {
        "_id": ObjectId("60f9d7ac345b7c9df348a86d"),
        "name": "John Doe",
        "email": "[email protected]"
      }
    ]
  },
]

Explanation: In this example, $lookup is used with an array and a pipeline ($match and $expr) to join orders with customers. It matches customer_id from orders with _id in customers, appending customer details into customer_details.

Example 3: Use $lookup with $mergeObjects

We can include multiple $lookup stages in an aggregation pipeline to perform multiple join operations with different foreign collections.

Example:

Suppose we want to enrich orders documents with details from both customers and products collections. We can include multiple $lookup stages to achieve this:

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

Output:

[
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a86e"),
    "order_number": "ORD001",
    "customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
    "customer_info": [
      {
        "_id": ObjectId("60f9d7ac345b7c9df348a86d"),
        "name": "John Doe",
        "email": "[email protected]"
      }
    ],
    "customer_details": {
      "_id": ObjectId("60f9d7ac345b7c9df348a86d"),
      "name": "John Doe",
      "email": "[email protected]"
    }
  },
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a86f"),
    "order_number": "ORD002",
    "customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
    "customer_info": [
      {
        "_id": ObjectId("60f9d7ac345b7c9df348a86d"),
        "name": "John Doe",
        "email": "[email protected]"
      }
    ],
    "customer_details": {
      "_id": ObjectId("60f9d7ac345b7c9df348a86d"),
      "name": "John Doe",
      "email": "[email protected]"
    }
  },
]

Explanation: Here, $lookup is followed by $mergeObjects to combine fields from customers with fields in orders. It merges matched customer details into a single customer_details object within each order document.

Example 4: Perform Multiple Joins and a Correlated Subquery with $lookup

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      let: { customerId: "$customer_id" },
      pipeline: [
        {
          $match: {
            $expr: { $in: ["$_id", "$$customerId"] }
          }
        },
        {
          $lookup: {
            from: "products",
            localField: "_id",
            foreignField: "customer_id",
            as: "products_ordered"
          }
        }
      ],
      as: "customer_details"
    }
  }
])

Output:

[
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a86e"),
    "order_number": "ORD001",
    "customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
    "customer_details": [
      {
        "_id": ObjectId("60f9d7ac345b7c9df348a86d"),
        "name": "John Doe",
        "email": "[email protected]",
        "products_ordered": [
          {
            "_id": ObjectId("60f9d7ac345b7c9df348a870"),
            "customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
            "product_name": "Product A"
          },
          // Other products ordered by John Doe
        ]
      }
    ]
  },
  {
    "_id": ObjectId("60f9d7ac345b7c9df348a86f"),
    "order_number": "ORD002",
    "customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
    "customer_details": [
      {
        "_id": ObjectId("60f9d7ac345b7c9df348a86d"),
        "name": "John Doe",
        "email": "[email protected]",
        "products_ordered": [
          {
            "_id": ObjectId("60f9d7ac345b7c9df348a870"),
            "customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
            "product_name": "Product B"
          },
          // Other products ordered by John Doe
        ]
      }
    ]
  },
]

Explanation: This complex query uses multiple $lookup stages within a pipeline. It first matches customer_id from orders with _id in customers, then performs a subquery $lookup to match customer _id with customer_id in products. It enriches order documents with nested arrays of products ordered by each customer.

Conclusion

The MongoDB Aggregation $lookup operator is essential for performing data joins across collections, enabling powerful data aggregation and enrichment capabilities within MongoDB queries. By leveraging $lookup, MongoDB users can streamline data integration and analysis tasks, making it a versatile tool for diverse application scenarios.

FAQs about MongoDB Aggregation $lookup

Can $lookup handle nested arrays within documents?

Yes, $lookup can handle nested arrays, allowing you to perform joins on fields deeply nested within documents.

What happens if there are multiple matches in the foreign collection for a single document in the primary collection?

The $lookup operator will include all matching documents from the foreign collection in the output array field (as), resulting in an array of matched documents per input document.

Is there a performance impact when using $lookup in MongoDB aggregation?

Performance can vary based on the size of collections, indexes, and network latency, so it’s advisable to benchmark queries and consider appropriate indexing strategies for optimal performance