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
    • Blog
    • Community
    • Testimonials
    • Whitepapers
    • Reports
  • Contact us
    • Contact
    • Sales Support
    • Feedback and Support
    • Careers
    • About Us
  • Store
    • Buy Now
    • Preferred Resellers
    • Team Pricing
  • Download
  • My 3T
search

Academy 3T

  • Explore our courses
    • MongoDB 101: Getting Started
    • MongoDB 201: Querying MongoDB Data
    • MongoDB 301: Aggregation
  • Get certified

Lesson 5, Exercise 3: Editing a query in the Aggregation Editor

MongoDB 101: Getting Started Using SQL in MongoDB Aggregation Lesson 5, Exercise 3: Editing a query in the Aggregation Editor

In this exercise, you’ll modify the query you imported into the Aggregation Editor in the previous exercise.

As part of this process, you’ll add a stage to the pipeline that limits the documents to those with a name value that contains the term horse. You’ll also change the name of one of the output columns to better reflect the type of data it contains.

Future courses will cover the Aggregation Editor in much more detail. The goal of this section is to introduce you to the Aggregation Editor and demonstrate how easy it can be to build MongoDB aggregate queries.

To edit the aggregate query

1. In the top section of the Aggregation tab, ensure that the Pipeline tab is selected and then click the Add a new stage to the pipeline button (add symbol).

The stage is inserted at the end of the pipeline, with the $match operator specified.

An operator defines a condition that the documents must match to be included in the results. For this exercise, you’ll stick with the $match operator.

The Aggregation Editor toolbar includes the Operator Quick Reference link, which launches the Studio 3T help article, Supported MongoDB Aggregation Operators and Stages. This can be a handy reference for understanding the differences between the operators available to the pipeline stages.

2. Ensure that the new stage is selected on the Pipeline tab, and then click the up arrow on the toolbar three times to move the stage up to the first position.

Your pipeline should now look similar to the following figure.

3. Go to the Stage 1 tab and type the following code snippet between the two curly braces:

"name" : /.*horse.*/i

The code specifies that only documents with a name value containing the term horse should be returned. Be sure to retain the initial braces when you add the snippet. The final code should look like the following.

{
  "name" : /.*horse.*/i
}

4. Under Stage Output on the bottom-right panel, click the Execute button (Execute entire script).

The Aggregation Editor runs the query up to this point in the pipeline, giving you results similar to those shown in the following figure. Notice that all name values include the term horse.

Being able to run a query at any point along the pipeline can be extremely handy when developing and troubleshooting your aggregations. It provides a quick and easy way to verify specific stages without running the entire query.

5. Go to the Stage 3 tab and, in the third line of code, replace the first instance of the string COUNT(*) with the word amount, retaining the quotation marks.

Be sure that you don’t change the actual aggregation operator, which is preceded by a dollar sign, as in $COUNT(*).

By default, the Aggregation Editor assigns the name COUNT(*) to the output column. Changing the string value in this stage changes the column name to amount. Your Stage 3 code should now look like the following snippet.

{
    "local_authority" : "$_id.local_authority", 
    "amount" : "$COUNT(*)", 
    "_id" : NumberInt(0)
}

6. Click the Execute button (Execute entire script) under the Stage Output panel.

The results should now reflect the execution of the first three pipeline stages:

  • Stage 1 limits the results to those documents with a name value that includes horse.
  • Stage 2 groups the results by the local_authority values and provides the total number of documents in each group.
  • Stage 3 reshapes the results to clearly display the local authorities and the number of pubs associated with each one. Your results should look similar to those in the following figure. At this point in the pipeline, the results are sorted by the name of the local authority.

7. Go to the Stage 4 tab and, in the second line of code replace the string COUNT(*) with amount to ensure that the Stage 4 code references the correct column name. 

Your snippet should now look like the following code:

{
    "amount" : NumberInt(-1)
}

8. Click the Execute button (Execute entire script) – this time on the main toolbar – to execute the full pipeline.

Execute tab

The Aggregation Editor now runs the entire pipeline, giving you the results shown in the following figure. Notice that the Powys local authority has the greatest number of pubs that include the term horse in the name.

9. In the Connection Tree, right-click the pubs database node and click Drop Database.

10. In the Drop Database dialog box, click Drop Database.

11. Close Studio 3T.

Previous Topic
Back to Lesson
Next Lesson
  • Course Home Expand All
    Introduction to MongoDB and Studio 3T
    1 Quiz
    Test your skills: Introduction to MongoDB and Studio 3T
    Introduction to MongoDB Atlas
    1 Topic
    Lesson 2, Exercise: Setting up MongoDB Atlas
    Connecting to MongoDB
    1 Topic | 1 Quiz
    Lesson 3, Exercise: Connecting to MongoDB via Studio 3T
    Test your skills: Connecting to MongoDB
    The MongoDB Basics: Databases, Collections & Documents
    7 Topics | 2 Quizzes
    Lesson 4: Understanding MongoDB Documents: Fields & Data Types
    Test your skills: Understanding MongoDB Documents
    Lesson 4: Comparing MongoDB vs SQL Concepts
    Lesson 4, Exercise 1: Creating a collection from a .json file
    Test your skills: Creating a Collection
    Lesson 4, Exercise 2: Reviewing the collection in different views
    Lesson 4, Exercise 3: Using Visual Query Builder to query data
    Lesson 4, Exercise 4: Updating data directly within the collection
    Post-Basics Course Feedback
    Using SQL in MongoDB Aggregation
    3 Topics | 1 Quiz
    Lesson 5, Exercise 1: Running a SQL aggregate query in MongoDB
    Test your skills: Using SQL in MongoDB Aggregation
    Lesson 5, Exercise 2: Exporting a SQL query to the Aggregation Editor
    Lesson 5, Exercise 3: Editing a query in the Aggregation Editor
    Importing and Exporting MongoDB Data
    3 Topics | 1 Quiz
    Lesson 6, Exercise 1: Importing document data from a .csv file
    Lesson 6, Exercise 2: Exporting document data to a .json file
    Lesson 6, Exercise 3: Exporting document data to a new collection
    Test your skills: Importing and Exporting MongoDB Data
    Running MongoDB Queries on the mongo Shell
    4 Topics | 1 Quiz
    Lesson 7: Introducing IntelliShell
    Lesson 7, Exercise 1: Using IntelliShell to load and run a script file
    Lesson 7, Exercise 2: Using IntelliShell’s auto-completion features to write queries
    Lesson 7, Exercise 3: Running queries in IntelliShell and viewing the results
    Test your skills: Running MongoDB Queries on the mongo Shell
    Course Extras
    Return to MongoDB 101: Getting Started
  • 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
    • White Papers
    • Testimonials
    • Discounts

    Company

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

    © 2023 3T Software Labs Ltd. All rights reserved.

    • Privacy Policy
    • Cookie settings
    • Impressum

    We value your privacy

    With your consent, we and third-party providers use cookies and similar technologies on our website to analyse your use of our site for market research or advertising purposes ("analytics and marketing") and to provide you with additional functions (“functional”). This may result in the creation of pseudonymous usage profiles and the transfer of personal data to third countries, including the USA, which may have no adequate level of protection for the processing of personal data.

    By clicking “Accept all”, you consent to the storage of cookies and the processing of personal data for these purposes, including any transfers to third countries. By clicking on “Decline all”, you do not give your consent and we will only store cookies that are necessary for our website. You can customize the cookies we store on your device or change your selection at any time - thus also revoking your consent with effect for the future - under “Manage Cookies”, or “Cookie Settings” at the bottom of the page. You can find further information in our Privacy Policy.
    Accept all
    Decline all
    Manage cookies
    ✕

    Privacy Preference Center

    With your consent, we and third-party providers use cookies and similar technologies on our website to analyse your use of our site for market research or advertising purposes ("analytics and marketing") and to provide you with additional functions (“functional”). This may result in the creation of pseudonymous usage profiles and the transfer of personal data to third countries, including the USA, which may have no adequate level of protection for the processing of personal data. Please choose for which purposes you wish to give us your consent and store your preferences by clicking on “Accept selected”. You can find further information in our Privacy Policy.

    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.

    Google Analytics

    Google Ads

    Bing Ads

    Facebook

    LinkedIn

    Quora

    Hotjar

    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.

    HubSpot

    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