$lookup Examples | MongoDB

February 08, 2019

Preface: New to the aggregation pipeline? Check out our 5 minute explanation before diving into these $lookup examples....

Also be sure to understand indexing and it's performance implications before reviewing these examples...

$lookup allows you to perform joins on collections in the same database. $lookup works by returning documents from a "joined" collection as a sub-array of the original collection.

$lookup supports both basic equality matches as well as uncorrelated sub-queries. We'll provide an example of each scenario.

The examples are based on this sample data...

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
}

Notice how we have two collections posts and comments. The postTitle field in the comments collection corresponds to the title field in the posts collection.

Both comments and posts have likes.

$lookup example: equality match

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

Notice how $lookup takes a document with the following fields:

from: the collection we want to join with
localField: the field we want to join by in the local collection (the collection we are running the query on)
foreignField: the field we want to join by in the foreign collection (the collection we want to join with)
as: the name of the output array for the results

This query returns the following..

{
    "title" : "my first post",
    "author" : "Jim",
    "likes" : 5,
    "comments" : [
        {
            "postTitle" : "my first post",
            "comment" : "great read",
            "likes" : 3
        }
    ]
},
{
    "title" : "my second post",
    "author" : "Jim",
    "likes" : 2,
    "comments" : [
        {
            "postTitle" : "my second post",
            "comment" : "good info",
            "likes" : 0
        },
        {
            "postTitle" : "my second post",
            "comment" : "i liked this post",
            "likes" : 12
        }
    ]
},
{
    "title" : "hello world",
    "author" : "Joe",
    "likes" : 3,
    "comments" : [
        {
            "postTitle" : "hello world",
            "comment" : "not my favorite",
            "likes" : 8
        }
    ]
}

Notice how the original 3 documents from the post collection are returned with an additional field comments.

For each post, this new comments array has all of the documents from the comments collection whose postTitle field matches the post's title field.

$lookup example: pipeline example with condition

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"
        }
 }
])

Notice how $lookup takes slightly different fields. Specifically the localField and foreignField fields have been replaced with:

let (optional): an expression defining variables to use in the pipeline stage. This is how you access fields from the input collection in the pipeline stage.
pipeline: an aggregation pipeline to execute on the collection to join

Notice how we define two variables in the let expression. We define post_likes and post_title so that we can reference the input documents in the pipeline stage.

This is the only way for us to make comparisons across the different collections...

{ $gt: [ "$likes", "$$post_likes"] },
{ $eq: ["$$post_title", "$postTitle" ] }

Notice how we use $$ to refer to the variables we defined in let. We reference fields in the foreign collection with a single $.

This query returns the following..

{
    "title" : "my first post",
    "author" : "Jim",
    "likes" : 5,
    "comments" : []
},
{
    "title" : "my second post",
    "author" : "Jim",
    "likes" : 2,
    "comments" : [
        {
            "postTitle" : "my second post",
            "comment" : "i liked this post",
            "likes" : 12
        }
    ]
},
{
    "title" : "hello world",
    "author" : "Joe",
    "likes" : 3,
    "comments" : [
        {
            "postTitle" : "hello world",
            "comment" : "not my favorite",
            "likes" : 8
        }
    ]
}

Like the first example, this query returns the comments whose postTitle matches the title field for each post. This query adds an additional condition for returning comments whose likes count is greater than the corresponding post's likes.

By running this query, we now know if any posts have comments with more likes than the post itself :).

Conclusion

You've now seen two examples of using $lookup in the aggregation pipeline. Remember that $lookup can be used for both equality checks (first example) as well as more complex sub-queries (second example).

Depending on which type of query you run, $lookup will take slightly different parameters (let, pipeline vs localField, foreignField).

For more on the aggregation pipeline, be sure to check out this 5 minute guide to the MongoDB aggregation framework.

Comment