Lookup Aggregations in MongoDB

What is $lookup in MongoDB?

$lookup is an aggregation pipeline operator in MongoDB. It performs a left outer join operation on two collections, combining documents based on a specified field. This operator enables the aggregation framework to retrieve documents from one collection and match them with documents from another collection. The result is an array of merged documents, where each document from the source collection contains an array field populated with matching documents from the target collection. This is particularly useful for creating more complex and comprehensive queries that involve data from multiple collections within a MongoDB database.

Syntax

Here is the syntax for the $lookup aggregation stage for each of the join conditions:

Equality Match with a Single Join Condition:

This syntax performs an equality match on the localField and foreignField fields. If the values of the two fields are equal, the document from the from collection is added to the new field in the output document.

Join Conditions and Subqueries on a Joined Collection:

This syntax performs a join condition. The pipeline field contains an aggregation pipeline that is used to filter the documents from the from collection. Only the documents that match the filter criteria are added to the new field in the output document.

Correlated Subqueries Using Concise Syntax:

This syntax uses a concise syntax to perform a correlated subquery. The let field defines a variable that is used in the pipeline field. The pipeline field is then used to filter the documents from the from collection based on the value of the variable.

Behavior

The lookup in mongodb performs a left outer join on the two collections. This means that all documents in the current collection will be returned, even if there are no matching documents in the from collection.

The lookup in mongodb also supports many options that can be used to control its behavior. These options include:

• select:
  A list of fields to return from the from collection.
• hint:
  A hint that can be used to improve the performance of the join.
• projection:
  A projection that can be used to control the fields that are returned from the current collection.

Examples of $lookup in MongoDB

Perform a Single Equality Join with $lookup

Here is an example of how to perform a single equality join with the lookup in mongodb:

1. Create a collection called orders with the following documents:

2. Create another collection called inventory with the following documents:

3. Run the following aggregation operation on the orders collection:

   This aggregation pipeline will join the orders collection with the inventory collection on the item field. The results of the join will be stored in a new field called inventory.

4. The operation will return the following documents:

   The operation corresponds to this pseudo-SQL statement:

Use $lookup with an Array

Here are the complete steps for the example of using the lookup in mongodb with an array:

1. Create a collection called orders with the following documents:

2. Create another collection called products with the following documents:

3. Run the following aggregation operation on the orders collection:

   This aggregation pipeline will join the orders collection with the products collection on the items array in the orders collection. The results of the join will be stored in a new field called products.

4. The operation will return the following documents:

Use $lookup with $mergeObjects

Here are the complete steps for the example of using the lookup in mongodb with the $mergeObjects operator:

1. Create a collection called orders with the following documents:

2. Create another collection called products with the following documents:

3. Run the following aggregation operation on the orders collection:

   This aggregation pipeline will join the orders collection with the products collection on the items array in the orders collection. It will then use the $mergeObjects operator to merge the products document with a document that has the name "Order details".

4. The operation will return the following documents:

Perform Multiple Joins and a Correlated Subquery with $lookup

Here are the complete steps for the example of performing multiple joins and a correlated subquery with the lookup in mongodb:

1. Create a collection called orders with the following documents:

2. Create another collection called products with the following documents:

3. Create another collection called customers with the following documents:

4. Run the following aggregation operation on the orders collection:

   This aggregation pipeline will join the orders collection with the products collection on the items array in the orders collection. It will then join the resulting documents with the customers collection on the customer_id field. The results of the join will be filtered to only include documents where the products.price is greater than 1000 and the customers.name is John Doe.

5. The operation will return the following documents:

Perform an Uncorrelated Subquery with $lookup

Here are the complete steps for the example of performing an uncorrelated subquery with the lookup in mongodb:

1. Create a collection called orders with the following documents:

2. Create another collection called products with the following documents:

3. Run the following aggregation operation on the orders collection:

   This aggregation pipeline will join the orders collection with the products collection on the items array in the orders collection. The $lookup stage will use an uncorrelated subquery to filter the documents in the products collection to only include documents where the price is greater than 1000.

4. The operation will return the following documents:

   The operation corresponds to this pseudo-SQL statement:

Perform a Concise Correlated Subquery with $lookup

Here are the complete steps for the example of performing a concise correlated subquery with the lookup in mongodb:

1. Create a collection called orders with the following documents:

2. Create another collection called products with the following documents:

3. Run the following aggregation operation on the orders collection:

   This aggregation pipeline will join the orders collection with the products collection on the items array in the orders collection. The $lookup stage will use a concise correlated subquery to filter the documents in the products collection to only include documents where the customer_id of the document in the orders collection matches the customer_id in the let clause.

4. The operation will return the following documents:

   The operation corresponds to this pseudo-SQL statement:

Related Articles

Learn more to understand more $lookup in MongoDB:

• Aggregation in MongoDB
• MongoDB Aggregation Pipeline
• What is Aggregation Framework?
• How to Use $unwind Operator in MongoDB