Using SQL in MongoDB Aggregation

In MongoDB, you can create aggregate queries that group data into meaningful categories of consolidated information, similar to aggregating data in a SQL or relational database.

However, aggregation in MongoDB differs from SQL in several important ways, and if you’re new to MongoDB, these differences might seem confusing at first.

Aggregation in MongoDB

The process of aggregation in MongoDB requires that you group the data by a common field and then perform some type of operation based on that grouping, such as counting totals, adding amounts, or calculating averages.

This approach is similar to aggregating data in a relational database, in which you add a GROUP BY clause to your SELECT statement and then add an aggregate function to the SELECT clause.

To aggregate data in MongoDB, you will use the aggregate method on the collection that contains the relevant source data, using multiple aggregate operators as arguments to the method. Each operator represents a step in the aggregate pipeline that carries out a specific operation.

Fortunately, Studio 3T simplifies this process by providing the Aggregation Editor, a user-friendly interface that breaks the aggregate query down into manageable stages. The editor makes it easy to build aggregate queries that return specific types of information, while offering a tool that demonstrates how aggregations work.

Studio 3T also provides SQL Query, a feature which makes it possible to run an SQL statement against a MongoDB collection, as though you were querying a relational database. From there, you can export your query to the Aggregation Editor, which provides a user-friendly interface for building an aggregate query one stage at a time.

This way, you can start building your aggregate queries in SQL and then refine them in the mongo shell language, using the Aggregation Editor.

In the next exercise, you’ll use both the SQL Query and Aggregation Editor to extract information about the distribution of pubs in Wales.

Access to SQL Query is included in the 14-day free trial.