Lookup in MongoDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

If you plan to perform any aggregation operations in MongoDB, it’s important to understand the aggregation pipeline and its various stages. Documents that enter the aggregation pipeline are transformed in some way at each stage; the transformed results are then passed to the next stage of the process. In this article, we’ll focus on the $lookup stage of the pipeline. Let’s discuss how to perform a lookup in MongoDB and check out an example of how this stage plays a part in aggregation.

Prerequisite

Before we begin our discussion of lookup in MongoDB, let’s review some prerequisites that are essential for this tutorial:

  • You’ll need to make sure that MongoDB is installed and configured on your machine.
  • You should have at least some basic knowledge in MongoDB before attempting the examples in this article.

What is MongoDB $lookup?

In this section, we’ll introduce you to the $lookup stage in MongoDB with a basic overview of the stage and its syntax.

In MongoDB, the $lookup stage allows you to perform a “join” on collections that exist in the same database. When you use $lookup, it filters documents from the joined collection. It will return documents from this joined collection in the form of a subarray of the original collection that was used in the aggregation pipeline. The subarray will be passed to the next stage for processing.

Shown below is the basic syntax of the $lookup stage:

1
2
3
4
5
6
7
8
9
{
   $lookup:
     {
       from: <collection_to_be_joined>,
       localField: <field_taken_from_the_input_documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <the_output_array_field>
     }
}

There’s quite a bit going on in this syntax. Let’s take a closer look at each component of $lookup:

  • from – This represents the collection with which you plan to join the original collection. The collection must reside in the same database as the original, or local, collection.
  • localField – This represents the field on which we want to join in the original collection.
  • foreignField – This represents the field on which we want to join in the ‘from’ collection. The $lookup will attempt to perform an equality match between the foreignField and localField of the input documents. If there’s no foreignField in the from collection, the $lookup will treat the values as null.
  • as – This represents the name of the new array that holds the output of the $lookup. This array field is composed of the matching documents found in the from collection. Keep in mind that if the given name already exists as a field in the input document, that field will be overwritten.

Create Sample Dataset

Before we look at the implementation of the $lookup stage, let’s create the sample dataset that we’ll use in the example:

1
2
3
4
5
db.product_order.insertMany([
    {_id:1,item_name: "pr00121", price: 21, quantity: 2},
    {_id:2,item_name: "pr00124", price: 32, quantity: 1},
    {_id:3,item_name: "pr00126", price: 12, quantity: 1}
]);
1
2
3
4
5
6
7
8
db.product_inventory.insertMany([
    {_id : 1, sku: "pr00121", description: "product 01", onhand: 200},
    {_id : 2, sku: "pr00122", description: "product 02", onhand: 90},
    {_id : 3, sku: "pr00123", description: "product 03", onhand: 45},
    {_id : 4, sku: "pr00124", description: "product 04", onhand: 37},
    {_id : 5, sku: "pr00125", description: "product 05", onhand: 250},
    {_id : 6, sku: "pr00126", description: "product 06", onhand: 19}
]);

We now have two collections in our database named ‘product_order’ and ‘product_inventory’.

MongoDB Join with $lookup

In this section, we’ll perform an aggregation operation using the product_order documents and product_inventory documents. Our join fields will be the item_name from the product_order collection and the sku field from the product_inventory collection:

1
2
3
4
5
6
7
8
9
10
11
db.product_order.aggregate([
    {
      $lookup:
        {
          from: "product_inventory",
          localField: "item_name",
          foreignField: "sku",
          as: "inventory_listing"
        }
   }
]).pretty();

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
{
        "_id" : 1,
        "item_name" : "pr00121",
        "price" : 21,
        "quantity" : 2,
        "inventory_listing" : [
                {
                        "_id" : 1,
                        "sku" : "pr00121",
                        "description" : "product 01",
                        "onhand" : 200
                }
        ]
}
{
        "_id" : 2,
        "item_name" : "pr00124",
        "price" : 32,
        "quantity" : 1,
        "inventory_listing" : [
                {
                        "_id" : 4,
                        "sku" : "pr00124",
                        "description" : "product 04",
                        "onhand" : 37
                }
        ]
}
{
        "_id" : 3,
        "item_name" : "pr00126",
        "price" : 12,
        "quantity" : 1,
        "inventory_listing" : [
                {
                        "_id" : 6,
                        "sku" : "pr00126",
                        "description" : "product 06",
                        "onhand" : 19
                }
        ]
}

Conclusion

Understanding the stages of the aggregation pipeline is essential if you plan to perform aggregation operations in MongoDB. In this article, we took an in-depth look at the $lookup stage of the pipeline and reviewed an example of its implementation. With these instructions and examples, you’ll be able to use $lookup in MongoDB for your own aggregation operations.Ω

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.