How to Join Collections in MongoDB Using $lookup Aggregation?

Question

0

Coming from a relational database background, I need to query related documents stored in separate collections. How does the $lookup operator work in MongoDB's aggregation pipeline to join two collections?

Understanding $lookup

The $lookup stage performs a left outer join to an unsharded collection in the same database, filtering in documents from the joined collection for processing.

localField: The field from the input documents passed into the $lookup stage.
foreignField: The field from the documents in the target collection.
as: The name of the new array field added to input documents containing matches.

Example Aggregation Pipeline

db.orders.aggregate([
    {
        $lookup: {
            from: "products",
            localField: "productId",
            foreignField: "_id",
            as: "productDetails"
        }
    }
]);

1 Answer

Write Your Answer

Answer 1

In MongoDB, although document databases are inherently schema-less and designed for denormalized data models, there are many scenarios where relational-style joins are required. MongoDB provides the $lookup stage in its Aggregation Framework to perform left outer joins between collections within the same database.

Understanding the $lookup Aggregation Stage

The $lookup operator takes documents from an input collection (the primary collection being queried) and combines them with documents from another collection (the joined collection) based on a specified matching field or condition.

Basic $lookup Syntax

The standard syntax for a basic equality join between two collections is structured as follows:

{
    "$lookup": {
        "from": "<collection_to_join>",
        "localField": "<field_from_input_docs>",
        "foreignField": "<field_from_from_collection_docs>",
        "as": "<output_array_field>"
    }
}

Step-by-Step Practical Example

Consider a database with two collections: orders and products.

An order document in the orders collection look like this:

{
    "_id": 101,
    "item_id": 5001,
    "quantity": 2,
    "customer": "John Doe"
}

A product document in the products collection looks like this:

{
    "_id": 5001,
    "name": "Wireless Mouse",
    "price": 29.99
}

Executing the Join Query

To join the orders collection with the products collection based on the matching product ID, use the following aggregation pipeline:

db.orders.aggregate([
    {
        "$lookup": {
            "from": "products",
            "localField": "item_id",
            "foreignField": "_id",
            "as": "product_details"
        }
    }
])

Output Result

The aggregation query appends an array named product_details to each order document:

[
    {
        "_id": 101,
        "item_id": 5001,
        "quantity": 2,
        "customer": "John Doe",
        "product_details": [
            {
                "_id": 5001,
                "name": "Wireless Mouse",
                "price": 29.99
            }
        ]
    }
]

Advanced $lookup with Pipeline and Search Conditions

MongoDB also allows uncorrelated subqueries and complex join conditions using custom pipeline stages inside $lookup.

db.orders.aggregate([
    {
        "$lookup": {
            "from": "products",
            "let": {
                "order_item": "$item_id",
                "order_qty": "$quantity"
            },
            "pipeline": [
                {
                    "$match": {
                        "$expr": {
                            "$and": [
                                { "$eq": ["$_id", "$$order_item"] },
                                { "$gte": ["$stock", "$$order_qty"] }
                            ]
                        }
                    }
                }
            ],
            "as": "matching_stock_products"
        }
    }
])

Key Points to Remember

The result of a $lookup operator is always returned as an array field in the output document.
If no matching documents are found in the target collection, the output array will be empty ([]).
You can follow a $lookup stage with an $unwind stage to flatten the array into individual document properties if needed.
Ensure indexes exist on foreignField to optimize query performance during aggregation.