MongoDB
MongoDB Developer Center
chevron-right
Developer Topics
chevron-right
Products
chevron-right
MongoDB
chevron-right

MongoDB Aggregation Pipeline Queries vs SQL Queries

Joe KarlssonPublished Feb 07, 2022 • Updated May 10, 2022
SQLMongoDBAggregation Framework
facebook icontwitter iconlinkedin icon
random alt
Rate this tutorial
star-empty
star-empty
star-empty
star-empty
star-empty
Let's be honest: Many devs coming to MongoDB are joining the community with a strong background in SQL. I would personally include myself in this subset of MongoDB devs. I think it's useful to map terms and concepts you might be familiar with in SQL to help "
translate
" your work into MongoDB Query Language (MQL). More specifically, in this post, I will be walking through translating the MongoDB Aggregation Pipeline from SQL.

What is the Aggregation Framework?

The aggregation framework allows you to analyze your data in real time. Using the framework, you can create an aggregation pipeline that consists of one or more
stages
. Each stage transforms the documents and passes the output to the next stage.
If you're familiar with the Unix pipe |, you can think of the aggregation pipeline as a very similar concept. Just as output from one command is passed as input to the next command when you use piping, output from one stage is passed as input to the next stage when you use the aggregation pipeline.
SQL is a declarative language. You have to declare what you want to see—that's why SELECT comes first. You have to think in sets, which can be difficult, especially for functional programmers. With MongoDB's aggregation pipeline, you can have stages that reflect how you think—for example, "First, let's group by X. Then, we'll get the top 5 from every group. Then, we'll arrange by price." This is a difficult query to do in SQL, but much easier using the aggregation pipeline framework.
The aggregation framework has a variety of
stages
available for you to use. Today, we'll discuss the basics of how to use
$match
,
$group
,
$sort
, and
$limit
. Note that the aggregation framework has many other powerful stages, including
$count
,
$geoNear
,
$graphLookup
,
$project
,
$unwind
, and others.
If you want to check out another great introduction to the MongoDB Aggregation Pipeline, be sure to check out
Introduction to the MongoDB Aggregation Framework
.

Terminology and Concepts

The following table provides an overview of common SQL aggregation terms, functions, and concepts and the corresponding MongoDB
aggregation operators
:
SQL Terms, Functions, and ConceptsMongoDB Aggregation Operators
WHERE
$match
GROUP BY
$group
HAVING
$match
SELECT
$project
LIMIT
$limit
OFFSET
$skip
ORDER BY
$sort
SUM()
$sum
COUNT()
$sum
and
$sortByCount
JOIN
$lookup
SELECT INTO NEW_TABLE
$out
MERGE INTO TABLE
$merge
(Available starting in MongoDB 4.2)
UNION ALL
$unionWith
(
Available starting in MongoDB 4.4
)
Alright, now that we've covered the basics of MongoDB Aggregations, let's jump into some examples.

SQL Setup

The SQL examples assume two tables, album and songs, that join by the song.album_id and the songs.id columns. Here's what the tables look like:
Albums
idnameband_namepricestatus
1lo-fi chill hop songs to study toSilicon Infinite2.99A
2Moon RocksSilicon Infinite1.99B
3FlavourOrganical4.99A
Songs
idtitleplaysalbum_id
1Snow Beats1331
2Rolling By2421
3Clouds31911
4But First Coffee5623
5Autumn9013
6Milk Toast1182
7Purple Mic7192
8One Note Dinner Party12422
I used a site called
SQL Fiddle
, and used PostgreSQL 9.6 for all of my examples. However, feel free to run these sample SQL snippets wherever you feel most comfortable. In fact, this is the code I used to set up and seed my tables with our sample data:

MongoDB Setup

The MongoDB examples assume one collection albums that contains documents with the following schema:
For this post, I did all of my prototyping in a MongoDB Visual Studio Code plugin playground. For more information on how to use a MongoDB Playground in Visual Studio Code, be sure to check out this post:
How To Use The MongoDB Visual Studio Code Plugin
. Once you have your playground all set up, you can use this snippet to set up and seed your collection. You can also follow along with this demo by using the
MongoDB Web Shell
.

Quick Reference

Count all records from albums
SQL
MongoDB
Sum the price field from albums
SQL
MongoDB
For each unique band_name, sum the price field
SQL
MongoDB
For each unique band_name, sum the price field, results sorted by sum
SQL
MongoDB
For band_name with multiple albums, return the band_name and the corresponding album count
SQL
MongoDB
Sum the price of all albums with status A and group by unique band_name
SQL
MongoDB
For each unique band_name with status A, sum the price field and return only where the sum is greater than $5.00
SQL
MongoDB
For each unique band_name, sum the corresponding song plays field associated with the albums
SQL
MongoDB
For each unique album, get the song from album with the most plays
SQL
MongoDB

Wrapping Up

This post is in no way a complete overview of all the ways that MongoDB can be used like a SQL-based database. This was only meant to help devs in SQL land start to make the transition over to MongoDB with some basic queries using the aggregation pipeline. The aggregation framework has many other powerful stages, including
$count
,
$geoNear
,
$graphLookup
,
$project
,
$unwind
, and others.
If you want to get better at using the MongoDB Aggregation Framework, be sure to check out
MongoDB University: M121 - The MongoDB Aggregation Framework
. Or, better yet, try to use some advanced MongoDB aggregation pipeline queries in your next project! If you have any questions, be sure to head over to the
MongoDB Community Forums
. It's the best place to get your MongoDB questions answered.

Resources:


Copy Link
facebook icontwitter iconlinkedin icon
Rate this tutorial
star-empty
star-empty
star-empty
star-empty
star-empty
Related
News & Announcements
MongoDB's New Time Series Collections

May 13, 2022
Article
Designing, Developing, and Analyzing with the New MongoDB Shell

May 16, 2022
Tutorial
Procedure to Allow Non-Root Users to Stop/Start/Restart "mongod" Process

May 16, 2022
Tutorial
Currency Analysis with Time Series Collections #2 — Simple Moving Average and Exponential Moving Average Calculation

May 16, 2022
Table of Contents