$lookup Examples | MongoDB

Preface: New to MongoDB's aggregation pipeline? Check out our 5-minute guide before diving into these $lookup examples. An understanding of indexing and its performance implications will also be helpful.

The $lookup stage in MongoDB’s aggregation framework allows you to join documents from two collections within the same database. We'll explore both basic equality joins and more complex queries using pipeline operators.

Key Takeaways

• The $lookup stage can perform basic and complex joins between collections.
• Equality joins use localField and foreignField.
• Pipeline example employs let and pipeline for more complex conditions.
• Understanding aggregation pipelines enhances data analysis capabilities.

Here are our sample data collections:

Post Collection

{
    "title": "my first post",
    "author": "Jim",
    "likes": 5
},
{
    "title": "my second post",
    "author": "Jim",
    "likes": 2
},
{
    "title": "hello world",
    "author": "Joe",
    "likes": 3
}

Comment Collection

{
    "postTitle": "my first post",
    "comment": "great read",
    "likes": 3
},
{
    "postTitle": "my second post",
    "comment": "good info",
    "likes": 0
},
{
    "postTitle": "my second post",
    "comment": "i liked this post",
    "likes": 12
},
{
    "postTitle": "hello world",
    "comment": "not my favorite",
    "likes": 8
},
{
    "postTitle": "my last post",
    "comment": null,
    "likes": 0
}

We have two collections: posts and comments. The postTitle field in the comments collection aligns with the title field in the posts collection.

$lookup Example: Basic Equality Match

db.posts.aggregate([
    { $lookup: {
        from: "comments",
        localField: "title",
        foreignField: "postTitle",
        as: "comments"
    }}
])

In this example, the $lookup stage matches documents based on the equality of specified fields, retrieving related documents in an embedded array comments in each resulting document from the posts collection.

{
    "title": "my first post",
    "author": "Jim",
    "likes": 5,
    "comments": [
        {
            "postTitle": "my first post",
            "comment": "great read",
            "likes": 3
        }
    ]
}

Each post document now contains an array of all matching comments.

$lookup Example: Pipeline Use with Conditions

db.posts.aggregate([
    { $lookup: {
        from: "comments",
        let: { post_likes: "$likes", post_title: "$title" },
        pipeline: [
            { $match:
                { $expr:
                    { $and:[
                        { $gt: [ "$likes", "$$post_likes" ] },
                        { $eq: [ "$$post_title", "$postTitle" ] }
                    ]}
                }
            }
        ],
        as: "comments"
    }}
])

This more complex example uses let to define variables that can be referenced in the pipeline stages. The pipeline allows for matching documents based on conditions that involve multiple fields and even comparisons across collections.

{
    "title": "my second post",
    "author": "Jim",
    "likes": 2,
    "comments": [
        {
            "postTitle": "my second post",
            "comment": "i liked this post",
            "likes": 12
        }
    ]
}

This query identifies comments that not only match by title but also have more likes than the respective post, providing a richly detailed view of your data.

FAQ

What is the performance impact of using $lookup?

Using $lookup, especially with the pipeline option, can be computationally expensive as it may involve scanning multiple documents across collections. Indexing related fields can help mitigate performance issues.

Can $lookup be used across databases?

No, $lookup can only join collections within the same database.

Is there a limit to using $lookup with pipelines?

While $lookup with pipelines offers significant flexibility, keep in mind that it can affect performance. Optimizing queries and leveraging indexes where possible is advisable.

How can I improve $lookup performance?

Ensure your join fields are indexed and try to limit the returned dataset size by filtering and projecting only necessary data in the pipeline stages.