FEATURES
RESOURCES
FEEDBACK & SUPPORT
ABOUT US
PRICING
Previous Next
How to Do MongoDB Aggregation Queries Easily In this post, we’re going to take a look at how to do MongoDB aggregation queries with the Aggregation Editor in Studio 3T (formerly MongoChef). We’re going to build a query based on the freely available housing data from the City of Chicago Data Portal. Create the MongoDB Aggregation Query Identify the Question MongoDB Aggregation Pipeline Operators Add a New Stage Group Results Translate MongoDB Aggregation Queries to JavaScript, Java, Python, C#, and the mongo Shell Language Specify Query Options Explain Cursor Allow Disk Use Custom Collation Older Studio 3T Versions Share the MongoDB Aggregation Query Download our MongoDB GUI now if you haven’t already. It’s available for Windows, Mac, and Linux, and the Aggregation Editor feature is available on all three editions – Core, Pro, and Enterprise.
DOWNLOAD STUDIO 3T
GET DOWNLOAD LINK FOR LATER Prefer to watch? Check out our video tutorial.
Open the Aggregation Editor Once we’ve opened up Studio 3T and connected to the database, we can select the collection we wish to query:
We can open the Aggregation Editor by clicking the large ‘Aggregate‘ button in the main tool bar at the top. Then, select ‘Open Aggregation Screen‘ from the right-click context menu or press the ‘F4‘ shortcut key. We now have an empty MongoDB aggregation query ready to be filled up, so let’s get cracking!
Identify the Question We Want to Answer The question we want to ask of our data is simple:
Which zip codes have the greatest number of senior housing units available? To think how we’ll answer this and how we’ll form our query, let’s take a look at the data. Click ‘Execute full pipeline‘ (executing an empty pipeline simply shows the contents of the collection).
If you prefer a JSON view of the data (and Studio 3T supports dynamically switching between table, tree and JSON views of your result data), it’s included below: { "_id" : ObjectId("544f9533d4c6dc758c28fde4"), "community_area" : { "name" : "Albany Park", "number" : 14 }, "property" : { "type" : "Senior", "name" : "Mayfair Commons" }, "address" : "4444 W. Lawrence Ave.", "zip_code" : "60630", "phone_number" : "773-205-7862", "management_company" : "Metroplex, Inc.", "units" : 97, "location" : { "x_coordinate" : 1145674.7538177613, "y_coordinate" : 1931569.979044555, "latitude" : 41.9682242321, "longitude" : -87.7397474866, "description" : "4444 W Lawrence Ave\n(41.968224232060564, -87.73974748655358)" } } OK, so we can see we have the fields we need. We can check "property.type" to see that it’s senior housing, and "zip_code" and "units" give us the zip code and number of available units there are, respectively. To answer our question, we need to combine these into the right aggregation query. But first, let’s get familiar with MongoDB aggregation pipeline operators.
MongoDB Aggregation Pipeline Operators A full list of the supported operators and their meaning is available here: http://docs.mongodb.org/manual/meta/aggregation-quick-reference/. This link is always readily available by clicking ‘Operator Quick Reference’ in the app. Additionally, here’s a list of the new aggregation pipeline operators that came out with MongoDB 3.4, which both IntelliShell and the Aggregation Editor fully support. OPERATOR
DESCRIPTION Categorizes incoming documents into groups, called buckets, based on a specified expression and
$bucket
bucket boundaries. Categorizes incoming documents into a specific number of groups, called buckets, based on a specified
$bucketAuto
expression. Bucket boundaries are automatically determined in an attempt to evenly distribute the documents into the specified number of buckets. Processes multiple aggregation pipelines within a single stage on the same set of input documents.
$facet
Enables the creation of multi-faceted aggregations capable of characterizing data across multiple dimensions, or facets, in a single stage. Categorizes or groups incoming documents by a specified expression to compute the count for each
$sortByCount
group. Output documents are sorted in descending order by the count.
$graphLookup
Performs a recursive search on a collection. To each output document, adds a new array field that contains the traversal results of the recursive search for that document.
Now we’re ready to create the first stage of our query where we’ll match against the senior property type
Add a New Stage Click ‘Add New Stage‘ and you’ll see a new stage in the ‘Pipeline‘ tab.
Double click the new stage to edit it (or simply select the ‘Stage 1‘ tab):
The screenshot above jumps ahead a little bit as the stage specification has already been filled, but let’s break down each piece in turn. First, notice the ‘$match‘ in the combo box. It’s here where we select the stage’s ‘operator’. A stage operator defines what the stage actually does. The ‘$match‘ operator takes the input set of documents and outputs only those that match the given criteria. It is essentially a filter. For convenience, the specification of the Stage 1 ‘$match‘ operator is repeated below: { "property.type": "Senior" } In the stage’s specification, we can see that we are matching against the "Senior" property type, meaning only documents with a value of "Senior" for the field "property.type" will be passed onto the (yet to be created) next stage of the MongoDB aggregation pipeline for further processing. We can check the output of this and any other stage at any time by clicking ‘Show output from the selected stage‘. Similarly, we can see the input of any stage at any time by clicking ‘Show input to the selected stage‘. This is a really nice and convenient feature, as it makes keeping track of the precise form of the data we are working at each stage in the aggregation pipeline really easy. We can see in the ‘Stage 1 output‘ tab that we have the results we need from this stage, and so let’s go on and create the next.
Grouping Results We now need a way to group together the results from Stage 1 on zip code and then add up each of the available units figures. The ‘$group‘ operator is exactly what we need for this.
The Stage 2 ‘$group‘ operator specification is repeated below: { _id: "$zip_code", totalUnits: { $sum: "$units" } } The specification of Stage 2 states that the output of this stage will be documents that have an “_id" with a distinct zip code as a value and so will group together documents input to this stage that have the same zip code, and a “totalUnits" field whose value is the sum of all the "units" field values from each of the documents in the group. We can see the input to and output from tabs for this stage in the screenshot and can confirm that a reduction has taken place – of the 70 documents input to this stage, there were 36 distinct zip codes, and so the corresponding 36 documents are output from this stage.
Finding the Answer As we want to know the zip codes that have the greatest number of senior housing units available, it would be convenient to sort the results from the greatest to the least total units available. To do this, we’ll create a third stage using the ‘$sort‘ operator with the following specification, giving us exactly what we want: { totalUnits: -1 }
Going back to the ‘Pipeline‘ tab we can see the result of the execution of the full query, as well as the full query itself, all in one one place. We can see we have the expected number of results from the full aggregation pipeline, and we can now answer our question – we have a list of the zip codes that have the greatest number of senior housing units available. Wasn’t that easy? It gets even easier.
Translate MongoDB Aggregation Queries to JavaScript, Java, Python, C#, and the mongo Shell Language We’ve added Query Code – the Studio 3T feature that lets you automatically translate MongoDB and SQL queries to JavaScript, Python, Java, C#, and the mongo shell language – to Aggregation Editor, which means you can now do the same with aggregation queries. Generate driver code in five languages simply by clicking on the Query Tab. Here it is in action:
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.
Explain We’ve moved the Explain tab right next to Query Code, so you can quickly review how your aggregation queries have been processed by the pipeline.
Cursor With MongoDB 3.6, the cursor option is mandatory for all aggregation queries, which means that in Studio 3T, aggregation queries will always be run with the cursor option.
Allow Disk Use Allow Disk Use enables writing to temporary files. You can select it under the Options tab, which will then allow aggregation operations to write data to the _tmp subdirectory in the dbPath directory.
Custom Collation Under the Options tab, you can also customize your queries’ collation, which is the way searching and sorting is performed.
Query Options in 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 if you are connected to a MongoDB 2.4 or earlier instance, the ‘Options‘ tab is not shown.
Share MongoDB Aggregation Queries The aggregation queries you have created can be saved to and loaded from file, so not only can you reload them in future sessions, but you can also share them with other colleagues and users.
You can also easily copy and paste aggregate queries to and from the clipboard.
Handy References It can take a bit of time to master all the different operators available in the MongoDB aggregation pipeline, so links to the MongoDB Aggregation Pipeline Quick Reference and the Aggregation Section of the MongoDB Manual are always available within a click’s reach directly in the app itself via the ‘Operator Quick Reference‘ and ‘Aggregation Tutorial‘ links, respectively. It won’t be too long before you’re masterfully producing complex MongoDB aggregation queries of your own! OK, that’s it for this post. As a helpful complement, I recommend reading our short guide on how to temporarily disable and enable aggregation pipeline stages. I hope you feel the same delight as we do about the amazing new features, convenience and boost to productivity the new Aggregation Screen in Studio 3T offers. Please do check out Studio 3T, the best MongoDB client. A little example of the rich code completion and easy in-line editing experience it offers is shown below:
Please also check out our Schema Explorer and IntelliShell tools at Studio3T.com, as I expect they’ll also help in making you a much more powerful and productive MongoDB user. As always, you can always find the latest tips and updates in our What’s New section. We’re always very keen to hear about your experiences and ideas for Studio 3T. If you’d like to tell us about them please visit our feedback page or click the ‘Feedback‘ tool bar button in the app.
TRY AGGREGATION EDITOR
GET DOWNLOAD LINK FOR LATER Editor’s Note: This post was originally published in September 2015. By Graham Thomson | November 7th, 2017 | Blog, General | 2 Comments
About the Author: Graham Thomson It all started with a Dragon 32… and has led to here, the most exciting place to be in software and databases right now. After helping hunt for the Higgs at CERN, and building large-scale, intelligent systems in industry, Graham is now doing the best job of all – building world-class tools that empower you to achieve world-class performance. Graham holds a Ph.D. in Computer Science from the University of Strathclyde.
RECENT TWEETS
LATEST BLOG POSTS
NAVIGATION
Studio 3T helps thousands of MongoDB
As an old Robomongo user I would like to
MongoDB Schema Discovery and
Features
developers and administrators with their
thank @Studio3T for donating Enterprise
Exploration
Knowledge Base
everyday jobs by providing the finest
license to @GDI_FDN It helps us qu…
MongoDB tools on the market. We guarantee
twitter.com/i/web/status/9…
MongoDB Dropped ACID – And It’s Kind
Testimonials
the best compatibility with current and legacy
3 days ago
of a Big Deal
Download
What’s New
releases of MongoDB, continue to deliver
Pricing
new features with every new software
@0xDUDE @GDI_FDN Thank you for the
release, and provide high quality support.
feedback, Victor. Definitely made our day :-)
The Main Types of NoSQL Databases
Compare Preferred Resellers
We're happy to help and are really…
What’s New in Studio 3T 2018.2 | Query
Feedback & Support
twitter.com/i/web/status/9…
Code Added to Aggregation Editor,
FAQ
3 days ago
Export Comparison Results, and More
Career About Us
Debugging #MongoDB aggregation queries
LDAP and Kerberos: Two Authentication
bugging you lately? Studio 3T's Aggregation
Methods Available in MongoDB
Editor can help.Break down yo…
Enterprise
twitter.com/i/web/status/9… 3 days ago
What’s New in Studio 3T 2018.1 | Extended SQL Query Support, Retained Expired Ops, and More MongoDB Tutorial Series: How to Develop an Effective Indexing Strategy How to Grant MongoDB Roles to Multiple Users How to Prevent a MongoDB Connection Timeout 6 Tips for Adding MongoDB to Your Database Architecture
Press Releases Privacy Policy Impressum
DOWNLOAD
