Aggregation functions in MongoDB

Posted By : Aftab Alam | 30-Nov-2017

There is a need of today's business to perform aggregate operations on between/among collections in mongoDB. In version 3.2 and later, Aggregation functions in mongoDB allows us to perform some of them.

Let’s suppose that there is a employee collection which holds documents mentioned below.

db.employee.insert([
                                   {"name":"Employee1", "salary":40000},                      
                                   {"name":"Employee2", "salary":30000},                    
                                   {"name":"Employee3", "salary":50000}                    
                               ]);

1. $count is used to get number of documents in collections. It is equivalent to COUNT() function in mysql.

db.employee.aggregate([{$count:"Total No. of employees:"}]);

Output:Total No. of employees: 3

2. $match is used to filter number of documents from a collection. It is equivalent to WHERE in mysql.

db.employee.aggregate([{$match:{salary:{$gte:40000}}}]); or
db.employee.aggregate({$match:{salary:{$gte:40000}}});

Output:
[ 
      {"_id":1, "name":"Employee1", "salary":40000}, 
      {"_id":2, "name":"Employee3", "salary":50000} 
]

3. $project is used to select number of columns from a document. It is similar to SELECT in mysql.

db.employee.aggregate([{$project:{_id:0, name:1, salary:1}}]); or
db.employee.aggregate({$project:{_id:0, name:1, salary:1}});

Output:
[
     {"name":"Employee1", "salary":40000},
     {"name":"Employee2", "salary":30000},
     {"name":"Employee3", "salary":50000}
]
        

4. $skip is used to skip specified number of documents.

db.employee.aggregate([{$skip:1}]); or
db.employee.aggregate({$skip:1});

Output: 
[
    {"_id":2, "name":"Employee2", "salary":30000},
    {"_id":3, "name":"Employee3", "salary":50000}
]


Explanation:- It skips the specified number of documents.

 

5. $limit is used to limit number of document(s). It is similar to LIMIT in mysql.

db.employee.aggregate([{$limit:1}]); or
db.employee.aggregate({$limit:1});

Output:
[
     {"_id":1, name:"Employee1", salary:50000}
]

Explanation:- It limits the number of documnets to be fetched from the collection.

 

6. $sort is used to sort document as per specified column(s). It is similar to ORDER BY in mysql. 

db.employee.aggregate([{$sort:{name:-1}}]); 

Output:
[
     {"_id":"3", name:"Employee3", salary:50000},
     {"_id":"1", name:"Employee1", salary:40000},
     {"_id":"2", name:"Employee2", salary:30000}
]

Explanation:- The 1 is used for ascending Order and -1 for descending Order.It sorts documents by salary column name in employee collection.

 

7. $unwind is used to unwind document(s) in embeded relationship. This document is stored in a student collection.

{_id:10, name:"Employee4", salary:50000, courses:["Java", "JavaScript", "MySQL"]}
db.student.aggregate([{$unwind:"$course"}]); or
db.student.aggregate({$unwind:"$course"});

Output:
[
     {_id:10, name:"Employee4", salary:50000, course: "Java"},
     {_id:10, name:"Employee4", salary:50000, course: "JavaScript"},
     {_id:10, name:"Employee4", salary:50000, course: "MySQL"}
]

Explanation:- For applied scnerio, Employee bearing id 10 named as Employee4 has many courses.
If one wants to get same number of documents as one having number of courses,
it is sorted by $unwind aggregate function in MongoDB. The output is displaying same number of documents as one has number courses.

 

8. $group is used for multiple purposes.

8.1. To get total number of documents in a collection

db.employee.aggregate([{$group:{_id:null, "Total No. Of Employees:":{$sum:1}}}]); or
db.employee.aggregate({$group:{_id:null, "Total No. Of Employees":{$sum:1}}});

Output: {"_id":null, "Total No. Of Employees":3}

Explanation:- It gets total number of employees in employee collection. In this example, there are only three employees. If one(1) as value is passed in $sum function then it gets total number of documents in a collection.

 

8.2. To get sum of all documents in a collection.

db.employee.aggregate([{$group:{_id:null, "Total Salary":{$sum:"$salary"}}}]);

Output: {_id:null, "Total Salary": 120000}

Explanation:- It gets sum of total number of employees on the basis of salary column.
In this example, salary column of employee collection is used to calculate some of all employees in the collection.

 

8.3. To get Average of salary of employees in a collection.

db.employee.aggregate([{$group:{_id:null, "Average Salary":{$avg:"$salary"}}}]);

Output: {_id:null, "Total Salary": 40000}


Explanation:- It gets average salary of total number of employees in the collection using salary column.
In this example, salary column of employee collection is used to calculate average salary in the collection.

 

8.4. To get Max Salary of an employee in collection.

db.employee.aggregate([{$group:{_id:null, "Max Salary":{$max:"$salary"}}}]);

Output: {_id:null, “Total Salary”: 50000}

 

Explanation:- It gets maximum salary in the employee collection.
In this example, salary column of employee collection is used to calculate maximum salary in the collection.

 

8.5. To get Min Salary of an employee in collection.

db.employee.aggregate([{$group:{_id:null, "Min Salary":{$min:"$salary"}}}]);

Output: {_id:null, "Total Salary": 30000}

 

Explanation:- It gets minimum salary in the employee collection.
In this example, salary column of employee collection is used to calculate minimum salary in the collection.

 

8.6. To get count by a column name

db.employee.aggregate([{$group:{_id:"$name", "Total No. Of Employees":{$sum:1}}}]); or
db.employee.aggregate({$group:{_id:"$name", "Total No. Of Employees":{$sum:1}}});

Output: 
[
    {"_id":"Employee1", "Total No. Of Employees":1},
    {"_id":"Employee2", "Total No. Of Employees":1},
    {"_id":"Employee3", "Total No. Of Employees":1}
]

 

Explanation:- It gets total number of employees on the basis of specified column.
In this example, name column is used to calculate to total number of employees in the collection. There may be scnerio where multiple employees with same may exist.

 

9. $lookup is used to perform join between two collections.

[
    {"_id":"Employee1", "otal Salary Group By name":40000},
    {"_id":"Employee2", "Total Salary Group By name":30000},
    {"_id":"Employee3", "Total Salary Group By name":50000}
]

student collection:-
[
    {_id:10, name:"Employee 1", student_id:1}, 
    {_id:11, name:"Employee 2", student_id:2},
    {_id:12, name:"Employee 3", student_id:3}
]

student_address collection:-
[
    {_id:20, address:"Address 1", student_id:1},
    {_id:21, address:"Address 1", student_id:1},
    {_id:22, address:"Address 2", student_id:2},
    {_id:23, address:"Address 2", student_id:2},
    {_id:24, address:"Address 3", student_id:3},
    {_id:25, address:"Address 3", student_id:3}
]

db.student.aggregate([
{
   $lookup:{
     localField:"student_id",
     from:"student_address",
     foreignField:"student_id",
     as:"student_info"
   }
}
]);

Output:
[
{
   _id:10,
   name:"Employee 1",
   student_id:1,
   student_info:[
   {_id:20, address:"Address 1",
    student_id:1},
   {_id:21, address:"Address 1",
   student_id:1}
  ]
},
{
  _id:11,
  name:"Employee 2",
  student_id:2,
  student_info:[
  {_id:22, address:"Address 2",
  student_id:2},
  {_id:23, address:"Address 2",
  student_id:2}
  ]
},
{
  _id:12,
  name:"Employee 3",
  student_id:3,
  student_info:[
  {_id:24, address:"Address 3",
  student_id:3},
  {_id:25, address:"Address 3",
  student_id:3}
  ]
}
]

Explanation1:- The $lookup aggregate function takes a json object which includes many properties.

localField: This is the id from current collection that would be used to lookup documents. In this example that is student_id.

from: This is the another collection with whcih we want ot perform join operation. In this example this is student_address.

foriegnField: This is the id from another collection which is used to perform join operation between/among collection(s).

as: This is the name given to matched number of documents after joining operations. In this example, student_info would be given name.

Explanation2:- While joining opertion is being performed between/among collections. It finds matched documents and all documents that are found in joined collection put into an array and this array can be found with specified property(student_info) in first collection document. There are two documents matched in student_address collection corresponding to each document in student collection. So it gets both matched documents from student_address collection and place with specified key(student_info) in document of student collection.

About Author

Author Image
Aftab Alam

Aftab has worked on multiple technologies in front-end as well as in back-end.

Request for Proposal

Name is required

Comment is required

Sending message..