How to do a Join using the MongoDB Shell

Introduction

If you’re coming from a relational database and trying MongoDB for the first time, you might be wondering how you do a join in a NoSQL database technology. Joins are when you have two tables or collections with a common field where you can gather information from both tables/collections with one join query. For example if you have a users collection/table and a comments collection/table, then a join would let you create a query that would allow you to query the comments made by each user. Now let’s get started and take a look at how to do a join in MongoDB.

Prerequisites

  • To follow along with this tutorial you should have MongoDB installed and running.
  • You should also have two collections with a common field to join on or can follow along with our example dataset and adapt as necessary.

Goal

Join the users collection and comments collection to show what comments each user made. The collections we are joining are shown here.

use demoDatabase
> db.users.find()
{ "_id" : ObjectId("5c7997e7ad685885625abce3"), "userId" : 1, "name" : "Al" }
{ "_id" : ObjectId("5c7997e7ad685885625abce4"), "userId" : 2, "name" : "Betty" }
{ "_id" : ObjectId("5c7997e7ad685885625abce5"), "userId" : 3, "name" : "Cameron" }

> db.comments.find()
{ "_id" : ObjectId("5c7997edad685885625abceb"), "userId" : 1, "comment" : "Hi, I'm Al and I love comments." }
{ "_id" : ObjectId("5c7997edad685885625abcec"), "userId" : 1, "comment" : "Hi, it's Al again. I really do love comments." }
{ "_id" : ObjectId("5c7997edad685885625abced"), "userId" : 2, "comment" : "I'm Betty. This is my first comment on this site." }
{ "_id" : ObjectId("5c7997edad685885625abcee"), "userId" : 3, "comment" : "This is Cameron. I enjoyed reading your website." }

As you can see in our dataset there is a common field userId on both collections which allows us to match up each user with their comments.

Use aggregate() with $lookup

Use the aggregate() method with the $lookup stage. The lookup stage is defined below as:

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

Let’s break this definition into its pieces and explain them one by one:

  • from: Selects the collection to join with, for us it will be the comments collection.
  • localField: Select the field from the selected collection that you will used to join on. For us, this is our userId field.
  • foreignField: Select the field on the from collection that you want to join to. For us, this is again the userId field, since this field exists on both collections.
  • as: Sets the name of the output array field of where to store the results.

Now let’s put it all together with this command:

db.users.aggregate([
   {
     $lookup:
       {
         from: "comments",
         localField: "userId",
         foreignField: "userId",
         as: "combined"
       }
  }
]).pretty()

Which returns the joined result:

{
        "_id" : ObjectId("5c7997e7ad685885625abce3"),
        "userId" : 1,
        "name" : "Al",
        "combined" : [
                {
                        "_id" : ObjectId("5c7997edad685885625abceb"),
                        "userId" : 1,
                        "comment" : "Hi, I'm Al and I love comments."
                },
                {
                        "_id" : ObjectId("5c7997edad685885625abcec"),
                        "userId" : 1,
                        "comment" : "Hi, it's Al again. I really do love comments."
                }
        ]
}
{
        "_id" : ObjectId("5c7997e7ad685885625abce4"),
        "userId" : 2,
        "name" : "Betty",
        "combined" : [
                {
                        "_id" : ObjectId("5c7997edad685885625abced"),
                        "userId" : 2,
                        "comment" : "I'm Betty. This is my first comment on this site."
                }
        ]
}
{
        "_id" : ObjectId("5c7997e7ad685885625abce5"),
        "userId" : 3,
        "name" : "Cameron",
        "combined" : [
                {
                        "_id" : ObjectId("5c7997edad685885625abcee"),
                        "userId" : 3,
                        "comment" : "This is Cameron. I enjoyed reading your website."
                }
        ]
}

As you can see we were returned our initial users collection but it now has a combined field which contains every comment made by the user. You’ll notice that Al our user with userId = 1, has two comments in the combined field which means this kind of join works on many-to-one relationships.

Conclusion

In this tutorial we joined two collections in MongoDB that had a many-to-one relationship. We were able to join our users collection and with our comments collection with an aggregation method that resulted in a set that showed us every comment made by every user. Joins can be a tough topic to wrap your mind around so we hope that this tutorial helped you understand how to do joins in MongoDB and apply it to your specific application.

Pilot the ObjectRocket platform free for 30 Days

It's easy to get started. Imagine the time you'll save by not worrying about database management. Let's do this!

PILOT FREE FOR 30 DAYS

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.