Aggregation with MongoDB (Comparison with SQL)

In this MongoDB tutorial series, I will explain using aggregate in MongoDB (some comparison with SQL for those who are familiar with SQL). If you are not familiar with MongoDB you can check out the previous tutorial at the bottom (curriculum).

<p dir="auto"><center><br /> <img src="https://images.hive.blog/768x0/https://steemitimages.com/0x0/https://steemit-production-imageproxy-thumbnail.s3.amazonaws.com/U5drSSY2U5mS2yr8wxHWzYt2ssSTGDN_1680x8400" srcset="https://images.hive.blog/768x0/https://steemitimages.com/0x0/https://steemit-production-imageproxy-thumbnail.s3.amazonaws.com/U5drSSY2U5mS2yr8wxHWzYt2ssSTGDN_1680x8400 1x, https://images.hive.blog/1536x0/https://steemitimages.com/0x0/https://steemit-production-imageproxy-thumbnail.s3.amazonaws.com/U5drSSY2U5mS2yr8wxHWzYt2ssSTGDN_1680x8400 2x" /><br /><br /> <a href="https://blog.serverdensity.com/mongodb/" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">Source<br /> <h4>What Will I Learn? <ul> <li>Aggregate function in MongoDB <li>Some examples of use cases with Aggregate function <li>Comparison with SQL query language <h4>Requirements <ul> <li>MongoDB installed <li>Basic CRUD operation in MongoDB <li>Basic understanding of Node.js and JavaScript <li>Experience in using SQL (for better understanding in this post) <h4>Difficulty <p dir="auto">Advanced <hr /> <h4>Tutorial Contents <h1>What is aggregation? <p dir="auto">In a database, when aggregation is carried out, it will process the data records requested and return the desired result. The way it return the result is by grouping the values from the documents which fulfill the condition provided in the query. <p dir="auto">In normal SQL, is quite common to have aggregate function with clauses like 'SELECT', 'HAVING', 'GROUP BY', 'SUM', 'AVG' and etc. These aggregate function can be also used in MongoDB just that the syntax are different. <h1>Aggregation in MongoDB <p dir="auto">The basic aggregation API looks like this: <p dir="auto"><code>db.users.aggregate() <p dir="auto">So, the operation that we want to perform are being inserted into <code>aggregate(). <h2>Some of the common <code>aggregate() method comparison between MongoDB and SQL <div class="table-responsive"><table> <thead> <tr><th style="text-align:center">MongoDB<th style="text-align:center">SQL<th style="text-align:center">Name <tbody> <tr><td style="text-align:center">$sum<td style="text-align:center">SUM()<td style="text-align:center">Get the Sum <tr><td style="text-align:center">$avg<td style="text-align:center">AVG()<td style="text-align:center">Get the Average <tr><td style="text-align:center">$min<td style="text-align:center">MIN()<td style="text-align:center">Get the Minimum value <tr><td style="text-align:center">$max<td style="text-align:center">MAX()<td style="text-align:center">Get the Maximum value <tr><td style="text-align:center">$first<td style="text-align:center">TOP/LIMIT<td style="text-align:center">Get the first value <tr><td style="text-align:center">$last<td style="text-align:center">TOP/LIMIT with ID desc<td style="text-align:center">Get the last value <tr><td style="text-align:center">$group<td style="text-align:center">GROUP BY<td style="text-align:center">Grouping of data <tr><td style="text-align:center">distinct()<td style="text-align:center">DISTINCT()<td style="text-align:center">Find distinct data <h3>Example 1: Find total posts by each author with $group and $sum <p dir="auto">In this example, I am showcasing how to query out total post by each author. <h4>Create the database <p dir="auto">I create 3 insert query to save the data. <pre><code>db.users.insert({ title: 'Blog post #1', body: 'Lorem ipsum dolor sit amet, consectetur.', author: 'superoo7', url: 'post/post#1', tags: ['post', 'blog'], upvotes: 10 }); db.users.insert({ title: 'Blog post #2', body: 'Lorem ipsum dolor sit amet, consectetur.', author: 'johnson', url: 'blog/post#2', tags: ['database', 'blog'], upvotes: 109 }); db.users.insert({ title: 'Blog post #3', body: 'Lorem ipsum dolor sit amet, consectetur.', author: 'superoo7', url: 'post/post#3', tags: ['life', 'blog'], upvotes: 98 }); <p dir="auto">To show out all the data, run <code>db.users.find().pretty(). <h4>Aggregation with $group and $sum <p dir="auto">Run the following aggregation: <p dir="auto"><code>db.users.aggregate([{$group: {_id: "$author", total_posts: {$sum:1}}}]) <p dir="auto"><img src="https://images.hive.blog/768x0/https://steemitimages.com/DQmR5KyxLcvkLpJuKMNooMvsm1X1peyGVoydQ3rTzkRvRNN/image.png" srcset="https://images.hive.blog/768x0/https://steemitimages.com/DQmR5KyxLcvkLpJuKMNooMvsm1X1peyGVoydQ3rTzkRvRNN/image.png 1x, https://images.hive.blog/1536x0/https://steemitimages.com/DQmR5KyxLcvkLpJuKMNooMvsm1X1peyGVoydQ3rTzkRvRNN/image.png 2x" /> <p dir="auto">So the first group is 'GROUP BY' in SQL query, and the <code>total_posts is a new variable, where it content <code>$sum: 1 which means that whenever they find an object contain author, they will increment by 1. <p dir="auto">The return value is in the format stated in the query: <code>{"_id": "author", "total_posts": 2} <p dir="auto">Which is equivalent to following SQL queries<br /> : <pre><code>SELECT author, COUNT(*) AS total_posts FROM users GROUP BY author <h3>Example 2: Find all tags with distinct() <p dir="auto">In this example, the data can be used back in Example 1. <h4>Aggregation with Distinct() <p dir="auto"><code>db.users.distinct("tags"); <p dir="auto"><img src="https://images.hive.blog/768x0/https://steemitimages.com/DQmaFW3fbPnSWo5BYL2HoXvpi28oJZEaCJKNMAmmPsgQh1A/image.png" srcset="https://images.hive.blog/768x0/https://steemitimages.com/DQmaFW3fbPnSWo5BYL2HoXvpi28oJZEaCJKNMAmmPsgQh1A/image.png 1x, https://images.hive.blog/1536x0/https://steemitimages.com/DQmaFW3fbPnSWo5BYL2HoXvpi28oJZEaCJKNMAmmPsgQh1A/image.png 2x" /> <p dir="auto">This query will execute and find all distinct tags (non-repeating tags), and show case it out. <p dir="auto">Which is equivalent to following SQL queries: <pre><code>SELECT DISTINCT tags FROM users <p dir="auto">To get count of that certain tag, you can use <code>find() and <code>count(). <p dir="auto"><code>db.users.find({'tags': 'blog'}).count(); <p dir="auto">In SQL, it looks like this: <pre><code>SELECT COUNT(DISTINCT tags) FROM users <h3>Example 3: Sorting data with sort() <p dir="auto">Using the same data in Example 1, we can sort the data base on upvotes. <p dir="auto">Run the following query and the data will be sorted based on upvotes (descending order) <p dir="auto"><code>db.users.find().sort({upvotes: -1}).pretty(); <p dir="auto"><code>sort() is the function for sorting, and in the <code>sort function, we pass in <code>upvotes as the data to sort with the setup of <code>-1 meaning that we want it to be descending. To make it sort in ascending, simply just change <code>-1 to <code>1. <p dir="auto">In SQL wise, it looks like this: <pre><code>SELECT * FROM users SORT BY upvotes DESC <h1>Final Thoughts <p dir="auto">Although MongoDB is Document based NoSQL, but both SQL and NoSQL shares some common property to do query. In this tutorial, I showcase some similar query can be done in both MongoDB and SQL which is to make those who are familiar to SQL to understand more about how Document-Based NOSQL works. <hr /> <h4>Curriculum <ul> <li><a href="https://utopian.io/utopian-io/@superoo7/getting-started-with-mongodb" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">Getting Started with MongoDB <li><a href="https://utopian.io/utopian-io/@superoo7/mongodb-crud-operation-in-console-and-introduction-to-mongoose-js" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">MongoDB: CRUD operation in console and introduction to mongoose.js <li><a href="https://utopian.io/utopian-io/@superoo7/using-indexes-to-increase-performance-with-mongodb-query" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">Using Indexes to Increase performance with MongoDB Query <p dir="auto"><br /><hr /><em>Posted on <a href="https://utopian.io/utopian-io/@superoo7-dev/aggregation-with-mongodb-comparison-with-sql" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">Utopian.io - Rewarding Open Source Contributors<hr /><p>