MongoDB Aggregate Pipeline

Main Page >> MongoDB >>MongoDB Workbook >> Aggregation Pipeline

Aggregation Pipeline

The aggregation pipeline is a framework for data aggregation modelled on the concept of data processing pipelines. What this means, is documents enter a multi-stage pipeline that transforms the documents into aggregated results.

This is similar to using GROUP BY in SQL, where you might aggregate the average grades of all students taking a module.

The power of the aggregation pipeline is to do processing on the data.

The MongoDB aggregation pipeline consists of stages and each stage transforms the documents as they pass through the pipeline. A stage can generate new documents or filter out documents. A stage can also appear several times in the pipeline.

The aggregate() method is used for aggregation and each pipeline stage appear in an array. Documents pass through the stages in sequence.

The syntax is:

db.collectionName.aggregate( [ { <stage> }, ... ] )

The pipeline for instance, could:

• project out certain details from each document, such as sal and comm;
• group the projected details by a certain field and then use an aggregate function. For example, group by the deptno and then count the number of occurrences;
• sort the results in order;
• limit the results to a certain number, such as the first 10;

These are represented by the following operators: $project,$group, $sort or $limit.

A number of operations exist for the aggregation pipeline, details of which can be found in the MongoDB manual:

https://docs.mongodb.com/manual/reference/operator/aggregation/

$group

$group will take a set of input documents, group them by a specified key and then apply an aggregate function to each group.

For example, to sum the salaries found in the emp collection:

db.emp.aggregate ( [
{ 	$group: 
       { _id: "$deptno", total: {$sum: "$sal"} } }
])

This is similar to the SQL command:

SELECT deptno, sum AS total FROM emp
GROUP BY deptno;

$lookup

$lookup can be used to provide a left outer join between two collections, such as dept and emp.

The $lookup stage does an equality match between a column from the input documents with a column from the joined collection.

In this example, the dept collection provides the input documents and the lookup will be performed in the emp collection. deptno in both documents is used to join the collections:

db.dept.aggregate([ {
     $lookup:  {
         from: "emp",
         localField: "deptno",
         foreignField: "deptno",
         as: "employees"
       }
  }
]).pretty()

This is the equivalent of the SQL statement:

SELECT * 
FROM dept 
LEFT OUTER JOIN emp
ON dept.deptno = emp.deptno;

The mgr field holds the empno of each employee's manager. To provide a list of employees the who they manage involved joining a collection to itself:

 db.emp.aggregate([ {
      $lookup:  {
          from: "emp",
          localField: "empno",
          foreignField: "mgr",
          as: "manages"
        }
   }
 ]).pretty()

An empty set [] will be returned for the manages field for any employees who are not managers.

Complex query

A more complex aggregate pipeline groups the emp collection by deptno and carries out several calculations:

db.emp.aggregate(
  [
     {$group : {
         _id : { _id : "$deptno"},
         averageSalary: { $avg: "$sal" },
         totalSal: { $sum: { $sum: "$sal"} },
         totalAnnualSal: { $sum: { $multiply: [ "$sal", 12 ] } },
         count: { $sum: 1 }
       }
     }
  ]
)

The calculations are:

• averageSalary returns the average salary
• totalSal returns the sum of the salary by department
• $multiply will multiple the sal by 12 to give the total annual salary by department
• $sum: 1 can be used to aggregate a value of one for each document processed, so here will produce count of employees by department

$match

In a larger collection you may not want all the documents processed in the query. $match can be used to filter the documents required and passes them to the next stage in the pipeline.

The following query is a repeat of above, but this time only processes documents where the salary is £3000 or above:

db.emp.aggregate(
  [
  {$match: {sal : {$gte:3000}}},
  {$group : {
       _id : { _id : "$deptno"},
       averageSalary: { $avg: "$sal" },
       totalSal: { $sum: { $sum: "$sal"} },
       totalAnnualSal: { $sum: { $multiply: [ "$sal", 12 ] } },
       count: { $sum: 1 }
       }
     }
  ]
)

$sort

The order of the results returned can depend on how the data was stored. When dealing with a large set of results you typically would want them sorted in some way. This can be achieved using the $sort command.

The syntax is:

{$sort: {field1:<sort order>, field2: <sort order> .... }}

Where <sort order> can have one of the following values:

• 1 to specify ascending order
• -1 to specify descending order

The previous query can be extended to sort by the _id, which represented the deptno:

db.emp.aggregate(
  [
  {$match: {sal : {$gte:3000}}},
  {$group : {
       _id : { _id : "$deptno"},
       averageSalary: { $avg: "$sal" },
       totalSal: { $sum: { $sum: "$sal"} },
       totalAnnualSal: { $sum: { $multiply: [ "$sal", 12 ] } },
       count: { $sum: 1 }
       }
     },
    {$sort: {_id: 1}}
  ]
)

The above is also an example of a pipeline with multiple stages: $match, then $group and finally $sort.

Other Functions

Count

Documents can also be counted using the count function.

Lets count how many employees are in department 10:

db.emp.count({deptno: 10})

You can also add count() to a find query to count the records returned, instead of listing them:

db.dept.find({dname:"SALES"}).count()

Distinct

Sometimes you want to find the distinct values for a specified column (similar to distinct in SQL):

db.emp.distinct("deptno")

Exercise 2.3

Try the lookup with the collections swapped:

db.emp.aggregate([ {
      $lookup:  {
          from: "dept",
          localField: "deptno",
          foreignField: "deptno",
          as: "dept"
        }
   }
 ])

Explain how the results differ to the previous example.

Next Step

Updating the collection