- tl;dr
- Hotkeys
Aggregation Editor is the stage-by-stage MongoDB aggregate query builder in Studio 3T.
With this tool you can not only add, edit, and move stages but also define operators and check inputs and outputs at each stage. This makes debugging easier and ensures that your query is accurate each step of the way. Download it here.
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)
Toggle query auto-completion – Ctrl + Space (^ + Space)
Basics
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 aggregate query results in either Table, Tree, or JSON view. It is part of the greater Collection Tab.
The other tabs Query Code and Explain will be covered later in the tutorial.
Build a MongoDB aggregate query
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. Now, we need to apply a $match operator to find all customers who own a pet manatee.
The $match
operator acts as a filter that reviews the input set of documents and outputs only those that match the given criteria.
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"
}
The Aggregation Editor also has an auto-completion feature, which in this example would suggest both "pet"
and "manatee"
.
Check the input of a stage
To double-check the input of a stage:
- Button – Click on the Show the input to a selected stage button
- Right-click – Right-click anywhere in the Pipeline Editor and choose Show the input to this stage button
- Hotkey – Press F6
In this example it’s the precise set of documents that were scanned for a value match “manatee” in the field “pet”. Clicking 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
- Right-click – Right-click anywhere in the Pipeline Editor and choose Show the output of a selected stage button
- Hotkey – Press F7
Showing the output makes it easier to double-check that the 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 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. An additional stage, for example, could group the results by city 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:
For a third and final stage, a $sort
operator can be applied to sort the results in descending order:
{
totalPets: -1
}
Duplicate a stage
Choose the stage you want to clone and:
- Button – Click on the Duplicate the selected stage button in the toolbar
- Right-click – Right-click on your target stage and choose Duplicate Selected Stage
- Hotkey – Press Alt + K on Windows or Option + K on Mac
Move a stage
Select the stage to move in the Pipeline tab, or the 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 select Move This Stage Up/Down
Enable or disable a stage
To temporarily enable or disable stages in your pipeline, simply check or uncheck the Include in pipeline checkbox as needed:
Delete a stage
Select 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 MongoDB aggregate 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 viewed in either Table, Tree, or JSON view. Find the full documentation here.
The aggregate query then helps us to answer our initial question:
From the results 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 in function to a web browser.
Click on the Retain button in the toolbar to allow for multiple tabs. Unclick it to allow only 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 select Clear Result Tabs
Generate JavaScript, Java, Python, and C# code from MongoDB aggregate 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 aggregate query’s equivalent code:
- Execute the aggregation query
- Click on the Query Code tab
- Select the target language
Read about Query Code in full here.
View the full MongoDB aggregate query
To see the full MongoDB aggregation query instead of viewing them line-by-line as stages:
- Click on the Query Code tab
- Choose mongo shell
Open the aggregate query in IntelliShell
All mongo shell code generated by Query Code can be opened directly in a separate IntelliShell tab by clicking on the Open IntelliShell icon.
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 these query plans.
Explain-type results can also be accessed by clicking on the Explain (lightbulb) button in the toolbar.
Open and save aggregate 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 aggregate queries
The copy and paste function is extremely helpful, especially when working across Studio 3T’s various 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 butto
- Right-click – Right-click anywhere in the Pipeline Editor and choose Paste Aggregate Query
Specify query options
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, you can set query options in the Options tab.
Note that these options only became available in MongoDB 2.6, so in MongoDB 2.4 or earlier the Options tab is not shown.