Performing joins in MongoDB with $lookup
Tags: mongodb, lookup, joins, v32
Published: 2017-11-09 17:42:03

---
title: "Performing joins in MongoDB with $lookup"
published: true
tags: MongoDB,$lookup,JOINS,v3.2
canonical_url: https://www.kenwalger.com/blog/nosql/mongodb/performing-joins-mongodb-lookup/
cover_image: https://thepracticaldev.s3.amazonaws.com/i/miayxnzo1bex4g95zp6w.png
---

Eventually, it seems, looking up data in multiple [MongoDB](https://www.mongodb.com) collections at the same time becomes necessary. Okay,&nbsp;_necessary_, might be strongly phrased. “Very helpful may be better. Until version 3.2 of MongoDB doing joins wasn’t possible, but with that version came the `$lookup` [aggregation stage operator](https://docs.mongodb.com/manual/reference/operator/aggregation/).&nbsp;The introduction of `$lookup` allows for left outer joins to be performed on collections in the same database which&nbsp;are&nbsp;not&nbsp;[sharded](https://www.kenwalger.com/blog/nosql/mongodb-horizontal-scaling-sharding/).

For our data for this post let’s utilize the concept of recipes. I’ll lay out some basic traditional SQL database tables and walk through the SQL syntax for a join.

![Recipe table layout](https://i1.wp.com/www.kenwalger.com/blog/wp-content/uploads/2017/08/recipe_tables.png?resize=840%2C472&ssl=1)

### SQL Syntax

Let’s have a quick look at what a left outer join looks like in SQL with a couple of different syntaxes in SQL.

###### Standard SQL

```
SELECT recipe_type.decription, recipes.title
FROM recipe_type
LEFT OUTER JOIN recipes
ON recipe_type.id = recipes.recipe_type_id
```

###### Oracle

```
SELECT recipe_type.description, recipes.title
FROM recipe_type, recipes
WHERE recipe_type.id = recipes.recipe_type_id(+)
```

The results of a left outer join for these two tables will contain _all_ of the rows from our “left table (recipe\_type).

### $lookup for Joins

![Concept of a Left Outer Join](https://i0.wp.com/www.kenwalger.com/blog/wp-content/uploads/2017/08/recipe_join_venn_diagram.png?resize=840%2C472&ssl=1)<figcaption>Concept of a Left Outer Join</figcaption>

Our join from `$lookup` then should be the same from a conceptual standpoint as our SQL cousin. Assuming we have a collection of recipe types and one of recipes&nbsp;our `$lookup` stage would look like the following:

```
{
  $lookup:
    {
      from: "recipes",
      localField: "id",
      foreignField: "recipe_type_id",
      as: "recipe_categories"
    }
}
```

Our `$lookup` stage then is doing an equality match between the two documents based on the `id` and `recipe_type_id` fields. It will add a new array field to the document in our aggregation pipeline document and passes it to the next stage of the pipeline.

Now, given our knowledge of [schema design](https://dev.to/kenwalger/schema-design-considerations-in-mongodb-6g0-temp-slug-8140812) and [document models](https://www.kenwalger.com/blog/nosql/document-model/) in MongoDB we may not have a need for this exact join as these two collections of data&nbsp;_might_ be embedded in one or the other collection. However, depending on your data access patterns and application needs, the collections may be utilizing references.

### Wrap Up

I hope this provided a useful, and quick, introduction to the `$lookup` aggregation stage and how it is possible to do joins in MongoDB. While you may not find yourself using it all of the time, it is a great tool to have at the ready when it is needed.

There are several MongoDB specific terms in this post. I created a [MongoDB Dictionary](https://www.echoskillstore.com/MongoDB-Dictionary/45103) skill for the [Amazon Echo](https://www.amazon.com/gp/product/B01DFKC2SO/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=B01DFKC2SO&linkCode=as2&tag=kenwalgersite-20&linkId=f9e513223de2525a72b95cf9561db55b)&nbsp;line of products. Check it out and you can say “Alexa, ask MongoDB what is a document? and get a helpful response.

* * *

_Follow me on Twitter [@kenwalger](https://www.twitter.com/kenwalger) to get the latest updates on my postings._

[![Facebook](https://i1.wp.com/www.kenwalger.com/blog/wp-content/plugins/social-media-feather/synved-social/image/social/regular/96x96/facebook.png?resize=48%2C48&ssl=1 "Share on Facebook")](http://www.facebook.com/sharer.php?u=https%3A%2F%2Fwww.kenwalger.com%2Fblog%2Fnosql%2Fmongodb%2Fperforming-joins-mongodb-lookup%2F&t=Performing%20joins%20in%20MongoDB%20with%20%24lookup&s=100&p%5Burl%5D=https%3A%2F%2Fwww.kenwalger.com%2Fblog%2Fnosql%2Fmongodb%2Fperforming-joins-mongodb-lookup%2F&p%5Bimages%5D%5B0%5D=https%3A%2F%2Fi1.wp.com%2Fwww.kenwalger.com%2Fblog%2Fwp-content%2Fuploads%2F2017%2F08%2Ffeature-2-e1504105105445.png%3Ffit%3D125%252C125%26ssl%3D1&p%5Btitle%5D=Performing%20joins%20in%20MongoDB%20with%20%24lookup "Share on Facebook")[![twitter](https://i0.wp.com/www.kenwalger.com/blog/wp-content/plugins/social-media-feather/synved-social/image/social/regular/96x96/twitter.png?resize=48%2C48&ssl=1 "Share on Twitter")](https://twitter.com/intent/tweet?url=https%3A%2F%2Fwww.kenwalger.com%2Fblog%2Fnosql%2Fmongodb%2Fperforming-joins-mongodb-lookup%2F&text=Hey%20check%20this%20out "Share on Twitter")[![google_plus](https://i1.wp.com/www.kenwalger.com/blog/wp-content/plugins/social-media-feather/synved-social/image/social/regular/96x96/google_plus.png?resize=48%2C48&ssl=1 "Share on Google+")](https://plus.google.com/share?url=https%3A%2F%2Fwww.kenwalger.com%2Fblog%2Fnosql%2Fmongodb%2Fperforming-joins-mongodb-lookup%2F "Share on Google+")[![reddit](https://i1.wp.com/www.kenwalger.com/blog/wp-content/plugins/social-media-feather/synved-social/image/social/regular/96x96/reddit.png?resize=48%2C48&ssl=1 "Share on Reddit")](http://www.reddit.com/submit?url=https%3A%2F%2Fwww.kenwalger.com%2Fblog%2Fnosql%2Fmongodb%2Fperforming-joins-mongodb-lookup%2F&title=Performing%20joins%20in%20MongoDB%20with%20%24lookup "Share on Reddit")[![linkedin](https://i2.wp.com/www.kenwalger.com/blog/wp-content/plugins/social-media-feather/synved-social/image/social/regular/96x96/linkedin.png?resize=48%2C48&ssl=1 "Share on Linkedin")](http://www.linkedin.com/shareArticle?mini=true&url=https%3A%2F%2Fwww.kenwalger.com%2Fblog%2Fnosql%2Fmongodb%2Fperforming-joins-mongodb-lookup%2F&title=Performing%20joins%20in%20MongoDB%20with%20%24lookup "Share on Linkedin")[![mail](https://i2.wp.com/www.kenwalger.com/blog/wp-content/plugins/social-media-feather/synved-social/image/social/regular/96x96/mail.png?resize=48%2C48&ssl=1 "Share by email")](mailto:?subject=Performing%20joins%20in%20MongoDB%20with%20%24lookup&body=Hey%20check%20this%20out:%20https%3A%2F%2Fwww.kenwalger.com%2Fblog%2Fnosql%2Fmongodb%2Fperforming-joins-mongodb-lookup%2F "Share by email")

The post [Performing joins in MongoDB with $lookup](https://www.kenwalger.com/blog/nosql/mongodb/performing-joins-mongodb-lookup/) appeared first on [Blog of Ken W. Alger](https://www.kenwalger.com/blog).