MongoDB Aggregation Pipeline Queries vs SQL Queries
Rate this tutorial
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 "" your work into MongoDB Query Language (MQL). More specifically, in this post, I will be walking through translating the MongoDB Aggregation Pipeline from SQL.
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.
Alright, now that we've covered the basics of MongoDB Aggregations, let's jump into some examples.
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:
|1||lo-fi chill hop songs to study to||Silicon Infinite||2.99||A|
|2||Moon Rocks||Silicon Infinite||1.99||B|
|4||But First Coffee||562||3|
|8||One Note Dinner Party||1242||2|
The MongoDB examples assume one collection
albumsthat 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: . 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 .
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 , , , , , and others.
If you want to get better at using the MongoDB Aggregation Framework, be sure to check out . 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 . It's the best place to get your MongoDB questions answered.