How to Use the $lookup Function in MongoDB

Introduction

When you’re querying data in MongoDB, there are times when you need information that’s stored in documents across multiple collections. The $lookup function makes it easy to “join” multiple collections, returning results that contain fields from all specified collections. In this tutorial, we’ll take a closer look at the $lookup function in MongoDB and check out some examples of how it’s used.

Prerequisite

Before proceeding with this tutorial, be sure that MongoDB is installed and configured on your machine.

$lookup Overview

As we mentioned earlier, the $lookup function in MongoDB allows us to join documents on collections that reside in the same database. The $lookup function will return the documents as if they came from this ‘joined’ collection, in the form of a sub-array of the target original collection. This function can handle both uncorrelated sub-queries and equality matches.

Create Sample Dataset

Let’s begin by creating a sample dataset that we can use in our examples:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
db.address.insertMany(
    [
        {
            "name": "rommel",
            "blk_no": 12,
            "street" : "dewey street",
            "city" : "olongapo"
        },
        {
            "name": "gary",
            "blk_no": 15,
            "street" : "gordon street",
            "city" : "olongapo"
        }
    ]
);

We should see output that looks like the following:

1
2
3
4
5
6
7
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("5e2934cdbf4122213c8171a1"),
                ObjectId("5e2934cdbf4122213c8171a2")
        ]
}

Now we’ll insert documents into another collection:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
db.userInfo.insertMany(
    [
        {
            "contact_name": "rommel",
            "age": 37,
            "sex" : "male",
            "citizenship" : "Filipino"
        },
        {
            "contact_name": "gary",
            "age": 32,
            "sex" : "male",
            "citizenship" : "Filipino"
        }
    ]
);

Our output should look like this:

1
2
3
4
5
6
7
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("5e2934cdbf4122213c8171a1"),
                ObjectId("5e2934cdbf4122213c8171a2")
        ]
}

Notice that the name field in the address collection has the same values as the contact_name field in the userInfo collection.

$lookup Equality Match

Now that we have our sample dataset, we can look at an example of how to use the $lookup function in MongoDB:

1
2
3
4
5
6
7
8
9
10
db.userInfo.aggregate([
    { $lookup:
        {
           from: "address",
           localField: "contact_name",
           foreignField: "name",
           as: "address"
        }
    }
]).pretty();

Let’s take a more detailed look at this query:

  • The from field indicates the MongoDB collection with which we’d like to join.
  • The localField specifies the field local to the collection on which we’re performing our query.
  • The foreignField field indicates the target field from the other collection with which we intend to join.
  • The as phrase will specify the name of our output array in the results.

The output should look something like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
{
        "_id" : ObjectId("5e2935ebbf4122213c8171a5"),
        "contact_name" : "rommel",
        "age" : 37,
        "sex" : "male",
        "citizenship" : "Filipino",
        "address" : [
                {
                        "_id" : ObjectId("5e2934cdbf4122213c8171a1"),
                        "name" : "rommel",
                        "blk_no" : 12,
                        "street" : "dewey street",
                        "city" : "olongapo"
                }
        ]
}
{
        "_id" : ObjectId("5e2935ebbf4122213c8171a6"),
        "contact_name" : "gary",
        "age" : 32,
        "sex" : "male",
        "citizenship" : "Filipino",
        "address" : [
                {
                        "_id" : ObjectId("5e2934cdbf4122213c8171a2"),
                        "name" : "gary",
                        "blk_no" : 15,
                        "street" : "gordon street",
                        "city" : "olongapo"
                }
        ]
}

$lookup in Pipeline and with Condition Example

In this section, we’ll look at another example of how to use the $lookup aggregation on pipelines with a condition:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
db.userInfo.aggregate([
 { $lookup:
     {
       from: "address",
       let: { the_city: "$city", the_name: "$name"},
       pipeline: [
            { $match:
                { $expr:
                    { $and:
                        [
                           { $gt: [ "$city", "$$the_city"] },
                           { $eq: ["$$the_name", "$contact_name" ] }
                        ]
                    }
                }
            }
        ],
        as: "address"
        }
 }
]).pretty();

There’s a lot going on in this command, so let’s review each part of it in a bit more detail. First, notice that we replaced the localField and foreField fields with the let and pipeline fields, respectively.

  • The let field is optional, but it can come in handy when we need to define variables to use within the pipeline.

  • The pipeline field will execute the join against the collection.

Take a closer look at these two lines in particular:

1
2
{ $gt: [ "$city", "$$the_city"] },
{ $eq: ["$$the_name", "$contact_name" ] }

Notice that we used the double dollar sign $$— this allows us to reference the variables that we defined in the let field. We can reference a foreign field using the single dollar sign $.

The output should look something like the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
{
        "_id" : ObjectId("5e2935ebbf4122213c8171a5"),
        "contact_name" : "rommel",
        "age" : 37,
        "sex" : "male",
        "citizenship" : "Filipino",
        "address" : [
                {
                        "_id" : ObjectId("5e2934cdbf4122213c8171a1"),
                        "name" : "rommel",
                        "blk_no" : 12,
                        "street" : "dewey street",
                        "city" : "olongapo"
                },
                {
                        "_id" : ObjectId("5e2934cdbf4122213c8171a2"),
                        "name" : "gary",
                        "blk_no" : 15,
                        "street" : "gordon street",
                        "city" : "olongapo"
                }
        ]
}
{
        "_id" : ObjectId("5e2935ebbf4122213c8171a6"),
        "contact_name" : "gary",
        "age" : 32,
        "sex" : "male",
        "citizenship" : "Filipino",
        "address" : [
                {
                        "_id" : ObjectId("5e2934cdbf4122213c8171a1"),
                        "name" : "rommel",
                        "blk_no" : 12,
                        "street" : "dewey street",
                        "city" : "olongapo"
                },
                {
                        "_id" : ObjectId("5e2934cdbf4122213c8171a2"),
                        "name" : "gary",
                        "blk_no" : 15,
                        "street" : "gordon street",
                        "city" : "olongapo"
                }
        ]
}

Conclusion

When you need to join two MongoDB collections and retrieve fields from documents stored in both of them, the $lookup function can help you target the right data and get the results you want. In this article, we provided a basic overview of the $lookup function in MongoDB and walked through some examples of how to use the function. With these examples and instructions, you’ll be able to use this function and create more effective queries in your own MongoDB environment.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.