Skip to content
Studio 3T - The professional GUI, IDE and client for MongoDB
  • Tools
    • Aggregation Editor
    • IntelliShell
    • Visual Query Builder
    • Export Wizard
    • Import Wizard
    • Query Code
    • SQL Query
    • Connect
    • Schema Explorer
    • Compare
    • SQL ⇔ MongoDB Migration
    • Data Masking
    • Task Scheduler
    • Reschema
    • More Tools and Features
  • Solutions
  • Resources
    • Knowledge Base
    • MongoDB Tutorials & Courses
    • Tool/Feature Documentation
    • Reports
    • Case Studies
    • Whitepapers
    • Blog
    • Testimonials
    • Community
  • Contact us
    • Contact
    • Sales Support
    • Feedback and Support
    • Career
    • About Us
  • Store
    • Buy Now
    • Preferred Resellers
    • Team Pricing
  • My License
  • Download
search

Studio 3T Knowledge Base

  • Documentation
  • Tutorials
  • Workshops
Take the fastest route to learning MongoDB. Cover the basics in two hours with MongoDB 101, no registration required.
Start the free course

MongoDB $lookup Example | The MongoDB Aggregation Pipeline

Posted on: 26/11/2019 (last updated: 09/03/2022) by Kathryn Vargas

This MongoDB $lookup tutorial is the first of a three-part aggregation query example that uses  the$lookup, $project & $out stages. Download Studio 3T to get started.

The MongoDB $lookup aggregation stage

The aggregation pipeline stage $lookup makes it possible to join data from an input collection (the collection you’re running the query on) and a lookup collection (the collection you want data from), as long as both collections are on the same database.

Output documents from the lookup collection are added as embedded documents in the input collection.

In this tutorial, we will use the customers dataset as our input collection (download the .json file here) and the publicly-available Chicago affordable housing dataset as our lookup collection (download the .json file here).

Next, import the two datasets following these instructions and make sure that they are on the same database.

We’ll use an equality match example to illustrate the $lookup stage. An equality match requires that the input collection and the lookup collection have a field to match on.

(In our case, this is the address.zip_code field found in customers and the Zip Code field in housing.)

We want to show affordable housing options in the customers dataset as a new embedded field address.zip_code.affordable_housing_options, where there is a zip code match.

MongoDB $lookup function in Aggregation Editor

An aggregation pipeline is meant to have multiple stages.

If you’re building more complex, multi-stage aggregation queries, we recommend using Aggregation Editor.  It lets you build queries stage by stage, check inputs and outputs, add, move, or delete stages as you go, and view your query in full mongo shell code.

Get Aggregation Editor by downloading the latest version of Studio 3T here.

1 – Right-click on the input collection and choose Open Aggregation Editor

In our example, the input collection is customers.

2 – Add a new stage

Click on the green plus icon in the toolbar, or the add a new stage link under Pipeline flow.

This will open a tab called Stage 1.

3 – Choose $lookup from the dropdown menu

4 – Run the aggregation query

Type your command in the Stage Editor section, following the syntax described.

Stage 1 with the MongoDB $lookup will look like:

{
      "from" : "housing",
      "localField" : "address.zip_code", 
      "foreignField" : "Zip Code", 
      "as" : "address.zip_code.affordable_housing_options"
}
  • housing is our lookup collection
  • address.zip_code is the field name in our input collection
  • Zip Code is the field name in our lookup collection
  • We’d like to create a new field address.zip_code.affordable_housing_options in our input collection. This is where we’ll embed the output documents from our lookup collection.

Execute the query by clicking on the play button (Execute full pipeline) in the toolbar.

5 – Check your results

For a quick visual check, the best way is to use Table View.

Click anywhere on the Results tab and choose Show All Embedded Fields. This expands all of address‘s embedded fields in the same view.

You should see our new field, affordable_housing_options.

Remember: We only have affordable housing information for Chicago zip codes, so not all customers will have values for this new field.

Click on the column header to sort the visible results in descending order.

Scroll to the left and you should see Maria Kelly as the first result, with 2 elements.

Right-click on the entry and choose Document > View Document (JSON).

You should see two affordable housing options nested under the zip_code field:

6 – View the full mongo shell code

The Aggregation Editor provides a more convenient interface for building queries, but you can always view the full mongo shell code at any point in the pipeline.

Click on the Query Code tab and choose mongo shell:

This will show you the full MongoDB query which you can also run in IntelliShell, Studio 3T’s built-in mongo shell.

Click on the Open in IntelliShell button:

This directly opens the query in a separate IntelliShell tab:

If you’re a pro at writing MongoDB queries, there’s also the hard(er) way of building aggregation queries: by manually writing them in the shell.

MongoDB $lookup function in mongo shell

In case you haven’t yet, download the customers dataset as the input collection (download) and the publicly-available Chicago affordable housing dataset as the lookup collection (download). Import the two datasets following these instructions and make sure that they are on the same database.

If you’re running a single aggregation query that uses MongoDB $lookup, you can do so directly in IntelliShell using the following syntax:

db.getCollection("insert-database-name-here").aggregate(
    [
        { 
            "$lookup" : {
                      from: "name of lookup collection",
                      localField: "field in the input collection",
                      foreignField: "field in the lookup collection",
                      as: "field name for the output documents"
                      }

        }
   ]
);

1- Right-click on your database and choose Open IntelliShell

Make sure it’s the database containing your collections (in our example, chicago).

2 – Run the aggregate query

In the Editor section, paste the $lookup aggregation query.

Our query in this case is:

db.getCollection("customers").aggregate(
    [
        { 
            "$lookup" : {
                "from" : "housing", 
                "localField" : "address.zip_code", 
                "foreignField" : "Zip Code", 
                "as" : "address.zip_code.affordable_housing_options"
            }
        }
    ]
);

Then, click on Execute entire script in the toolbar.

3 – Check your results

This will give you the same results as Step 5 from Aggregation Editor.

As you will see in later examples, manually writing MongoDB aggregation queries can get quite cumbersome. Debugging, as you add more stages, will prove to be more difficult without a quick way to check inputs and outputs, which is a feature available in Aggregation Editor.

Next, we’ll add a second stage – $project – to filter our documents to only show the fields we want.

Read next: Stage 2 – MongoDB $project example


How helpful was this article?
This article was hideous
This article was bad
This article was ok
This article was good
This article was great
Thank you for your feedback!

About The Author

Kathryn Vargas

When she's not writing about working with MongoDB, Kathryn spends her free time exploring Berlin's food scene, playing the drums, learning languages (current mission: German), and hiking.

Article navigation

Related articles

  • MongoDB $out Example | The MongoDB Aggregation Pipeline
  • MongoDB $project Example | The MongoDB Aggregation Pipeline
  • Adding Lookup Data to the Aggregation Pipeline: Test your skills
  • Adding Lookup Data to the Aggregation Pipeline
  • Exercise 1: Adding lookup data to the aggregation pipeline

Studio 3T

MongoDB Enterprise Certified Technology PartnerSince 2014, 3T has been helping thousands of MongoDB developers and administrators with their everyday jobs by providing the finest MongoDB tools on the market. We guarantee the best compatibility with current and legacy releases of MongoDB, continue to deliver new features with every new software release, and provide high quality support.

Find us on FacebookFind us on TwitterFind us on YouTubeFind us on LinkedIn

Education

  • Free MongoDB Tutorials
  • Connect to MongoDB
  • Connect to MongoDB Atlas
  • Import Data to MongoDB
  • Export MongoDB Data
  • Build Aggregation Queries
  • Query MongoDB with SQL
  • Migrate from SQL to MongoDB

Resources

  • Feedback and Support
  • Sales Support
  • Knowledge Base
  • FAQ
  • Reports
  • Case Studies
  • White Papers
  • Testimonials
  • Discounts

Company

  • About Us
  • Blog
  • Careers
  • Legal
  • Press
  • Privacy Policy
  • EULA

© 2022 3T Software Labs GmbH. All rights reserved.

  • Privacy Policy
  • Cookie settings
  • Impressum
When you click "Accept", you are agreeing to cookies being on your device. They may improve site navigation, site usage analysis, or the relevance of messages. It is up to you which cookies are enabled. Read our Privacy Policy.
Manage cookies
Accept
✕

Privacy Preference Center

A cookie is a small file of letters and numbers that is downloaded on to your computer when you visit a website. Cookies are used by many websites and can do a number of things, eg remembering your preferences, recording what you have put in your shopping basket, and counting the number of people looking at a website. In the form below you can choose which cookies, except for essential cookies, to allow or disable.

Accept all cookies

Manage consent preferences

Essential cookies are strictly necessary to provide an online service such as our website or a service on our website which you have requested. The website or service will not work without them.

Performance cookies allow us to collect information such as number of visits and sources of traffic. This information is used in aggregate form to help us understand how our websites are being used, allowing us to improve both our website’s performance and your experience.

Functional cookies collect information about your preferences and choices and make using the website a lot easier and more relevant. Without these cookies, some of the site functionality may not work as intended.

Social media cookies are cookies used to share user behaviour information with a third-party social media platform. They may consequently effect how social media sites present you with information in the future.

Accept selected