MongoDB Lookup - What a MongoDB Lookup Does and How To Use It

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

Introduction

In this article we’ll talk about the $lookup in MongoDB. We’ll talk about what it does and how to execute one. We’ll go over an example in the MongoDB Shell so you can gain a concrete understanding.

Prerequisites

  • You should have MongoDB installed on your system.
  • You can follow along using the MongoDB Shell or with whatever driver you’re using.

What $lookup does?

It’s difficult to describe what a $lookup does but we’re gonna do our best here and then we’ll add a demonstration that should clarify the concept.

If you’re familiar with SQL terminology or syntax, a $lookup does a left outer join and adds the joined documents into an array on each of the of the from documents. We understand that’s complex so for now just focus on two things 1. It does a Join and 2. It adds an array of joined documents. Keep those two things in mind while we do the demonstration and you’ll get it.

The Demonstration

Let’s jump straight into the demo. Let’s make two collections to start out with and then we’ll show you what the $lookup does.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
anonymous:grocerydb >db.createCollection("products");
{ "ok" : 1 }
anonymous:grocerydb >db.createCollection("orders");
{ "ok" : 1 }

db.products.insert([
   { "name" : "almonds", "quantity" : 120 },
   { "name" : "bread", "quantity" : 80 },
   { "name" : "cheddar", "quantity" : 60 },
   { "name" : "soda", "quantity" : 70 }
]);

db.orders.insert([
   { "product" : "bread", "quantity" : 2, "price" : 1.50 },
   { "product" : "cheddar", "quantity" : 1, "price" : 3.50 },
]);

We’ve made two collections, one for the products and another for the orders of those products.

Now how would we join these two sets of data? We can use the aggregate function along with $lookup! Let’s say we want to look up to join the orders with the products, so for every order we could see the quantity of that product remaining. How would we do that?

1
2
3
4
5
6
7
8
9
10
11
12
db.orders.aggregate([

    // Join with the products collection like this:
    {
        $lookup:{
            from: "products", // The collection you want to join to
            localField: "product", // The field in orders you want to join on
            foreignField: "name", // The field in products you want to join on
            as: "product_docs" // What you want to call the array that stores the joined documents from products
        }
    }
])

This is the result: ` {
“_id”:ObjectId(“5d13e3e7076abb04b910347e”), “product”:”bread”, “quantity”:1, “price”:1.5, “product_docs”:[


1
2
3
4
5
  {  
     "_id":ObjectId("5d13e3d6076abb04b910347b"),
     "name":"bread",
     "quantity":80
  }

] }{
“_id”:ObjectId(“5d13e3e7076abb04b910347f”), “product”:”cheddar”, “quantity”:1, “price”:3.5, “product_docs”:[


1
2
3
4
5
  {  
     "_id":ObjectId("5d13e3d6076abb04b910347c"),
     "name":"cheddar",
     "quantity":60
  }

] } `

As you can see we still have our orders collection but it’s got a new field product_docs which contains the document of the product that was purchased! Now we can see the quantity of that product!

Why you need to do this?

Mentally we like to put different data into logical buckets eg. products, users, orders etc. But these data are not separate, they are related, orders are for products, and users make orders so we’re always gonna to need to join the data. Joining the data so that we can tell who ordered what product and how many orders were made on what is essential. So get familiar with $lookup because joining collections data together is essential in any application.

The SQL Equivalent

If you’re still having a hard time picturing it we can take a look at what the equivalent join would be in SQL syntax:

1
2
3
4
5
SELECT *, product_docs
FROM orders
WHERE product_docs IN (SELECT *
FROM products
WHERE name = orders.product);

Conclusion

As with joins, $lookups can get increasingly complex and you’ll want to explore the documentation more on how to accomplish what you need. We hope that this article though gave you the foundation for understanding the $lookup command. Remember that it’s great for doing what you’d typically do in joins. It helps you connect the information from two separate collections into one place.

We hope you enjoyed this article and are able to apply it to your application but if you want to move to having your MongoDB data and the complexity of a production environment managed by experts, don’t hesitate to reach out to us at Object Rocket.

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.