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).

Source

What Will I Learn?

Aggregate function in MongoDB
Some examples of use cases with Aggregate function
Comparison with SQL query language

Requirements

MongoDB installed
Basic CRUD operation in MongoDB
Basic understanding of Node.js and JavaScript
Experience in using SQL (for better understanding in this post)

Difficulty

Advanced

Tutorial Contents

What is aggregation?

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.

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.

Aggregation in MongoDB

The basic aggregation API looks like this:

db.users.aggregate()

So, the operation that we want to perform are being inserted into aggregate().

Some of the common `aggregate()` method comparison between MongoDB and SQL

MongoDB	SQL	Name
$sum	SUM()	Get the Sum
$avg	AVG()	Get the Average
$min	MIN()	Get the Minimum value
$max	MAX()	Get the Maximum value
$first	TOP/LIMIT	Get the first value
$last	TOP/LIMIT with ID desc	Get the last value
$group	GROUP BY	Grouping of data
distinct()	DISTINCT()	Find distinct data

Example 1: Find total posts by each author with $group and $sum

In this example, I am showcasing how to query out total post by each author.

Create the database

I create 3 insert query to save the data.

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
    });

To show out all the data, run db.users.find().pretty().

Aggregation with $group and $sum

Run the following aggregation:

db.users.aggregate([{$group: {_id: "$author", total_posts: {$sum:1}}}])

So the first group is 'GROUP BY' in SQL query, and the total_posts is a new variable, where it content $sum: 1 which means that whenever they find an object contain author, they will increment by 1.

The return value is in the format stated in the query: {"_id": "author", "total_posts": 2}

Which is equivalent to following SQL queries
:

SELECT 
  author,
  COUNT(*) AS total_posts
FROM 
  users
GROUP BY
  author

Example 2: Find all tags with distinct()

In this example, the data can be used back in Example 1.

Aggregation with Distinct()

db.users.distinct("tags");

This query will execute and find all distinct tags (non-repeating tags), and show case it out.

Which is equivalent to following SQL queries:

SELECT
  DISTINCT tags
FROM 
  users

To get count of that certain tag, you can use find() and count().

db.users.find({'tags': 'blog'}).count();

In SQL, it looks like this:

SELECT
  COUNT(DISTINCT tags)
FROM 
  users

Example 3: Sorting data with sort()

Using the same data in Example 1, we can sort the data base on upvotes.

Run the following query and the data will be sorted based on upvotes (descending order)

db.users.find().sort({upvotes: -1}).pretty();

sort() is the function for sorting, and in the sort function, we pass in upvotes as the data to sort with the setup of -1 meaning that we want it to be descending. To make it sort in ascending, simply just change -1 to 1.

In SQL wise, it looks like this:

SELECT *
FROM users
SORT BY
    upvotes DESC

Final Thoughts

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.

Curriculum

Posted on Utopian.io - Rewarding Open Source Contributors

Sort:

Trending

[-]

laxam (57) 7 years ago

Thank you for the contribution. It has been approved.

You can contact us on Discord.
[utopian-moderator]

$0.02

1 vote

superoo7 (64) 7 years ago (edited)

To the moderator moderating this post, I made a mistake on not posting on my main account @superoo7

For next post onwards, I will post on my account.

$0.00

legendchew (61) 7 years ago

So moving forward no tutorial in @superoo7-dev?

superoo7 (64) 7 years ago

I might post science related stuff here like Electronics

Noted & thanks @superoo7.

teammalaysia (45) 7 years ago

You’ve been upvoted by TeamMalaysia community. Do checkout other posts made by other TeamMalaysia authors at http://steemit.com/created/teammalaysia

To support the growth of TeamMalaysia Follow our upvotes by using steemauto.com and follow trail of @myach

Vote TeamMalaysia witness bitrocker2020 using this link vote for witness

utopian-io (71) 7 years ago

Hey @superoo7-dev I am @utopian-io. I have just upvoted you!

Achievements

You have less than 500 followers. Just gave you a gift to help you succeed!
This is your first accepted contribution here in Utopian. Welcome!

Community-Driven Witness!

I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!

Vote for my Witness With SteemConnect
Proxy vote to Utopian Witness with SteemConnect
Or vote/proxy on Steemit Witnesses

Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x

Aggregation with MongoDB (Comparison with SQL)