Aggregation Editor

Aggregation Editor is a feature that lets users build an aggregation query one stage at a time.

Operators can be defined and inputs and outputs can be checked at each stage, to make debugging easier and to ensure that the aggregation pipeline is accurate each step of the way.

Open Aggregation Editor – F4
Execute Full Pipeline – F5
Show Input to this Stage – F6
Show Output from this Stage – F7
Open Aggregate Query – Ctrl + O (⌘+ O)
Save Aggregate Query – Ctrl + S (⌘+ S)
Save Aggregate Query As – Shift + Ctrl + S (Shift + ⌘+ S)

Basics

The Aggregation Editor.

There are three ways to open Aggregation Editor:

Button

Click on the Aggregate button in the global toolbar

Right-click

Right-click on a target collection and choose Open Aggregation Screen

Hotkey

Press F4

Aggregation Editor has two main sections: the Pipeline Editor and the Result Tab.

Pipeline Editor

The Pipeline Editor is where the aggregation query is edited (e.g. stages are added, operators are chosen).

It has three main tabs:

Pipeline

Shows the full aggregation pipeline, all stages included.

Stage

Shows the operator and specified query for each stage. Each added stage generates a new tab.

Options

Find settings for disk use and custom collation.

Result Tab

The Result tab displays the aggregation query results in either Table, Tree, or JSON view. It is part of the greater Collections Tab.

The other tabs Query Code and Explain will be covered later in the tutorial.

Build an aggregation query

The dataset Customers will be used in this tutorial to illustrate the feature’s capabilities. Download it here.

Add a new stage

All aggregation queries start with a question that needs to be answered, which in this tutorial is:

Which city has the highest number of pet manatees?

A stage operator defines what the stage actually does. Looking at the Customers data set, a $match operator needs to be applied to find all customers who own a pet manatee.

The $match operator acts as a filter that takes the input set of documents and outputs only those that match the given criteria.

Find the complete list of supported MongoDB aggregation operators here. This list is also readily available in Studio 3T through the Operator Quick Reference link in the toolbar.

To add a new stage:

Button

Click on the Add New Stage (+) button

Right-click

Right-clicking anywhere in the Pipeline Editor and choose Add New Stage

This will generate a stage in the Pipeline tab and a Stage tab right next to it.

Edit a stage

Now that Stage 1 has been created, it’s ready to be edited.

To edit a stage:

Button

Click on the Edit button

Pipeline tab

Under the Pipeline tab, double-click on the stage to be edited

Stage tab

Click on the relevant stage tab directly

Choose the desired stage operator from the drop-down menu, which in this case is $match.

State the query, which in this example is:

{
"pet" : "manatee"
}

Check the input of a stage

To double-check the input of a stage, which in the example is the precise set of documents that were scanned for a value match “manatee” for the field “pet”:

Button

Click on the Show the input to a selected stage button

Pipeline tab

Right-click anywhere in the Pipeline Editor and choose Show the input to this stage button

Stage tab

Press F6

A quick click on Count Documents shows that 1001 documents were scanned, which is exactly the starting amount of documents in the Customers collection.

Check the output of a stage

To show the output of a stage:

Button

Click on the Show the output of a selected stage button

Pipeline tab

Right-click anywhere in the Pipeline Editor and choose Show the output of a selected stage button

Stage tab

Press F7

The ability to show the output makes it easier to double-check that results are accurate (e.g. no customers should appear who own a pet cat), before they’re carried over as the input of a later stage.

The Count Documents feature can again be used to check for a smaller result count.

Add a stage before or after a selected stage

It is also possible to place an additional stage before or after any selected stage:

Button

Click on the arrow next to the (+) icon and choose Add New Stage Before Selected Stage or Add New Stage After Selected Stage

Right-click

Right-click anywhere in the Pipeline Editor and choosing Add New Stage Before Selected Stage or Add New Stage After Selected Stage

The current output only contains customers with a pet manatee. As a next step, the results can be grouped by city in a second stage using the $group operator and the query:

{
_id:"$address.city", totalPets: { $sum: "$number_pets"}
}

Which gives us the following results, not sorted in any order:

As a third and final stage, a $sort operator can be applied to sort the results in descending order:

{
totalPets: -1
}

Move a stage

Moving a stage.

Choose the stage to be moved in the Pipeline tab or its corresponding Stage tab and:

Button

Click on the up and down arrows in the toolbar

Right-click

Right-click anywhere in the Pipeline Editor and choose Move This Stage Up/Down

Enable or disable a stage

To temporarily enable or disable stages from your pipeline, check or uncheck the Include in pipeline checkbox as needed:

Delete a stage

Choose the stage to be deleted in the Pipeline tab or its corresponding Stage tab and:

Button

Click on the Delete button

Right-click

Right-click anywhere in the Pipeline Editor and choose Delete Selected Stage

Execute full pipeline

Now that the aggregation query is complete and all stages ($match, $group, and $sort) are in the correct order, the full pipeline can be executed:

Button

Click on the Execute full pipeline (play) button

Right-click

Right-click anywhere in the Pipeline Editor and choose Execute full pipeline

Hotkey

Press F5

Check query results

The Result Tab displays the query results which can be edited in-place, and can be viewed in either Table, Tree, or JSON view. Find the full documentation here.

The aggregation query then helps us to answer our initial question:

It looks like Berlin has the highest number of pet manatees, with 99 living across the German capital.

View multiple result tabs

The Result Tab can show either one tab, meaning results from previous queries aren’t kept, or multiple tabs, which is similar to how a browser works.

Click on the Retain button in the toolbar to trigger multiple tabs. Unclick it to stick with one tab.

Clear all result tabs

To close all result tabs:

Button

Click on the Clear button in the toolbar

Right-click

Right-click anywhere in the Pipeline Editor and choose Clear Result Tabs

Generate JavaScript, Java, Python, and C# code from aggregation queries

Query Code is a feature available in Studio 3T that translates aggregation queries to JavaScript (Node.js), Java (2.x and 3.x driver API), Python, C#, and the mongo shell language.

To view a MongoDB aggregation query’s equivalent code:

  1. Execute the aggregation query
  2. Click on the Query Code tab
  3. Choose the target language

Read about Query Code in full here.

View the full MongoDB query

To see the full MongoDB aggregation query instead of viewing them line-by-line as stages:

  1. Click on the Query Code tab
  2. Choose mongo shell

Explain the full pipeline

The Explain Tab is part of the greater Collection Tab (full documentation here), which shows information on query plans and execution statistics of said query plans.

Explain results can also be accessed by clicking on the Explain (lightbulb) button in the toolbar.

Open and save aggregation queries

To save your aggregation query as a JavaScript file:

Button

Click on the Save icon. Alternatively, click on the arrow to find the Save As function.

Right-click

Right-click anywhere in the Pipeline Editor and choose Save Aggregate Query (As)

Hotkey

Save Aggregate Query – Ctrl + S (⌘+ S)
Save Aggregate Query As – Shift + Ctrl + S (Shift + ⌘+ S) 

Copy and paste aggregation queries

The copy and paste function is extremely helpful, especially when working in between Studio 3T features (for example, going from SQL Query to the View Editor).

To copy an aggregation query:

Button

Click on the Copy button

Right-click

Right-click anywhere in the Pipeline Editor and choose Copy Aggregate Query

To paste an aggregation query:

Button

Click on the Paste button

Right-click

Right-click anywhere in the Pipeline Editor and choose Paste Aggregate Query

Specify query options

We’ve made a few changes to how query options are handled which were shipped with Studio 3T 2018.2. If you’re using an older version of Studio 3T, we suggest upgrading to the latest version, or jump ahead to the Older Studio 3T versions section.

The Options tab is where disk use and custom collation settings can be set.

Allow disk use

Allow Disk Use enables writing to temporary files, which will then allow aggregation operations to write data to the _tmp subdirectory in the dbPath directory.

Custom collation

Customize your queries’ collation, which is the way searching and sorting is performed.

Read more about collation here.

Older Studio 3T versions

In older versions of Studio 3T, query options can be set in the Options tab.

Note that these options only became available in MongoDB 2.6, so on MongoDB 2.4 or an earlier instance, the Options tab is not shown.

Options MongoDB Aggregation Query

Updated on September 27, 2018

Was this article helpful?

Related Articles