Home > Programming Blog > MongoDB

MongoDB Tutorial: Aggregate Method Example

by Didin J. on Sep 16, 2019 MongoDB Tutorial: Aggregate Method Example

MongoDB Tutorial: a complete example of the aggregate function to calculates aggregate values for the data in a collection or a view

In this MongoDB tutorial, we will show you a nearly complete example of calculates aggregate values for the data in a collection or a view using MongoDB Aggregate function or method. MongoDB aggregation operators were similar to SQL query terms, function, and concepts. Here, we want to show you an example of comparation with SQL queries. If you are getting used to SQL queries, you will see the difference and similarity by the examples.

Table of Contents:


Syntax or Definition

The MongoDB aggregate syntax simple like this.

db.collection.aggregate(pipeline, options)

That syntax calculates the aggregate of a collection by pipeline and options. The aggregation pipeline is a sequence of data aggregation operations or stages. We can compare this aggregation pipeline with this SQL terms function and concepts.

SQL Terms, Functions, and Concepts  MongoDB Aggregation Operators
WHERE $match
GROUP BY  $group
HAVING $match
SELECT $project
ORDER BY $sort
LIMIT $limit
SUM() $sum
COUNT() $sortByCount
JOIN $lookup
SELECT INTO NEW_TABLE $out
MERGE INTO TABLE $merge (Available starting in MongoDB 4.2)

    
Preparation

We will use the popular and legendary Northwind database which is converted to the MongoDB database. You can clone or download it from our GitHub. After cloned or downloaded, restore the MongoRestore command and make sure your MongoDB server is running. We are running MongoDB daemon manually, so, we need to open a new Terminal tab to run it.

mongod

In the previous Terminal tab type this command to restore the Products collection from the Northwind MongoDB database.

mongorestore --db your-db-name -c products ~/northwind-mongo/dump/products.bson

Now, enter the Mongo console then check the Products collection.

mongo
use your-db-name
db.products.find()

Now, we are ready to practice using MongoDB aggregate method.


Group By and Calculate Sum Example

The below example will show you how to Group products by SupplierID then calculate a sum of UnitPrice for each SupplierID. The result will sort ascending by the SupplierID.

db.products.aggregate([
                     { $group: { _id: "$SupplierID", TotalPrice: { $sum: "$UnitPrice" } } },
                     { $sort: { _id: 1 } }
                   ])

Result:

{ "_id" : 1, "TotalPrice" : 47 }
{ "_id" : 2, "TotalPrice" : 81.4 }
{ "_id" : 3, "TotalPrice" : 95 }
{ "_id" : 4, "TotalPrice" : 138 }
{ "_id" : 5, "TotalPrice" : 59 }
{ "_id" : 6, "TotalPrice" : 44.75 }
{ "_id" : 7, "TotalPrice" : 177.85 }
{ "_id" : 8, "TotalPrice" : 112.7 }
{ "_id" : 9, "TotalPrice" : 30 }
{ "_id" : 10, "TotalPrice" : 4.5 }
{ "_id" : 11, "TotalPrice" : 89.13 }
{ "_id" : 12, "TotalPrice" : 223.39000000000001 }
{ "_id" : 13, "TotalPrice" : 25.89 }
{ "_id" : 14, "TotalPrice" : 79.3 }
{ "_id" : 15, "TotalPrice" : 60 }
{ "_id" : 16, "TotalPrice" : 46 }
{ "_id" : 17, "TotalPrice" : 60 }
{ "_id" : 18, "TotalPrice" : 281.5 }
{ "_id" : 19, "TotalPrice" : 28.049999999999997 }


Group By and Get Average Example

The below example will show you how to Group products by SupplierID then get the average of UnitPrice for each SupplierID. The result will sort ascending by the SupplierID.

db.products.aggregate([
                     { $group: { _id: "$SupplierID", AvgPrice: { $avg: "$UnitPrice" } } },
                     { $sort: { _id: 1 } }
                   ])

Result:

{ "_id" : 1, "AvgPrice" : 15.666666666666666 }
{ "_id" : 2, "AvgPrice" : 20.35 }
{ "_id" : 3, "AvgPrice" : 31.666666666666668 }
{ "_id" : 4, "AvgPrice" : 46 }
{ "_id" : 5, "AvgPrice" : 29.5 }
{ "_id" : 6, "AvgPrice" : 14.916666666666666 }
{ "_id" : 7, "AvgPrice" : 35.57 }
{ "_id" : 8, "AvgPrice" : 28.175 }
{ "_id" : 9, "AvgPrice" : 15 }
{ "_id" : 10, "AvgPrice" : 4.5 }
{ "_id" : 11, "AvgPrice" : 29.709999999999997 }
{ "_id" : 12, "AvgPrice" : 44.678000000000004 }
{ "_id" : 13, "AvgPrice" : 25.89 }
{ "_id" : 14, "AvgPrice" : 26.433333333333334 }
{ "_id" : 15, "AvgPrice" : 20 }
{ "_id" : 16, "AvgPrice" : 15.333333333333334 }
{ "_id" : 17, "AvgPrice" : 20 }
{ "_id" : 18, "AvgPrice" : 140.75 }
{ "_id" : 19, "AvgPrice" : 14.024999999999999 }
{ "_id" : 20, "AvgPrice" : 26.483333333333334 }


Get Minimum Values Example

The below example will show you how to Group products by SupplierID then get the minimum values of UnitPrice for each SupplierID. The result will sort ascending by the SupplierID. This operation will use $min operator.

db.products.aggregate([
                     { $group: { _id: "$SupplierID", AvgPrice: { $min: "$UnitPrice" } } },
                     { $sort: { _id: 1 } }
                   ])

Result:

{ "_id" : 1, "AvgPrice" : 10 }
{ "_id" : 2, "AvgPrice" : 17 }
{ "_id" : 3, "AvgPrice" : 25 }
{ "_id" : 4, "AvgPrice" : 10 }
{ "_id" : 5, "AvgPrice" : 21 }
{ "_id" : 6, "AvgPrice" : 6 }
{ "_id" : 7, "AvgPrice" : 15 }
{ "_id" : 8, "AvgPrice" : 9.2 }
{ "_id" : 9, "AvgPrice" : 9 }
{ "_id" : 10, "AvgPrice" : 4.5 }
{ "_id" : 11, "AvgPrice" : 14 }
{ "_id" : 12, "AvgPrice" : 7.75 }
{ "_id" : 13, "AvgPrice" : 25.89 }
{ "_id" : 14, "AvgPrice" : 12.5 }
{ "_id" : 15, "AvgPrice" : 2.5 }
{ "_id" : 16, "AvgPrice" : 14 }
{ "_id" : 17, "AvgPrice" : 15 }
{ "_id" : 18, "AvgPrice" : 18 }
{ "_id" : 19, "AvgPrice" : 9.65 }
{ "_id" : 20, "AvgPrice" : 14 }


Get Maximum Values Example

The below example will show you how to Group products by SupplierID then get the maximum values of UnitPrice for each SupplierID. The result will sort ascending by the SupplierID. This operation will use $max operator.

db.products.aggregate([
                     { $group: { _id: "$SupplierID", AvgPrice: { $max: "$UnitPrice" } } },
                     { $sort: { _id: 1 } }
                   ])

Result:

{ "_id" : 1, "AvgPrice" : 19 }
{ "_id" : 2, "AvgPrice" : 22 }
{ "_id" : 3, "AvgPrice" : 40 }
{ "_id" : 4, "AvgPrice" : 97 }
{ "_id" : 5, "AvgPrice" : 38 }
{ "_id" : 6, "AvgPrice" : 23.25 }
{ "_id" : 7, "AvgPrice" : 62.5 }
{ "_id" : 8, "AvgPrice" : 81 }
{ "_id" : 9, "AvgPrice" : 21 }
{ "_id" : 10, "AvgPrice" : 4.5 }
{ "_id" : 11, "AvgPrice" : 43.9 }
{ "_id" : 12, "AvgPrice" : 123.79 }
{ "_id" : 13, "AvgPrice" : 25.89 }
{ "_id" : 14, "AvgPrice" : 34.8 }
{ "_id" : 15, "AvgPrice" : 36 }
{ "_id" : 16, "AvgPrice" : 18 }
{ "_id" : 17, "AvgPrice" : 26 }
{ "_id" : 18, "AvgPrice" : 263.5 }
{ "_id" : 19, "AvgPrice" : 18.4 }
{ "_id" : 20, "AvgPrice" : 46 }


Insert the Array of Values from Other Fields Example

The below example will show you how to insert the array of the values from other fields for each SupplierID. The result will sort ascending by the SupplierID. This operation will use the $push operator.

db.products.aggregate([
                     { $group: { _id: "$SupplierID", AvgPrice: { $max: "$UnitPrice" }, ReorderLevel: { $push: "$ReorderLevel" } } },
                     { $sort: { _id: 1 } }
                   ])

Result:

{ "_id" : 1, "AvgPrice" : 19, "ReorderLevel" : [ 10, 25, 25 ] }
{ "_id" : 2, "AvgPrice" : 22, "ReorderLevel" : [ 0, 20, 0, 0 ] }
{ "_id" : 3, "AvgPrice" : 40, "ReorderLevel" : [ 25, 10, 0 ] }
{ "_id" : 4, "AvgPrice" : 97, "ReorderLevel" : [ 0, 0, 5 ] }
{ "_id" : 5, "AvgPrice" : 38, "ReorderLevel" : [ 30, 0 ] }
{ "_id" : 6, "AvgPrice" : 23.25, "ReorderLevel" : [ 5, 5, 0 ] }
{ "_id" : 7, "AvgPrice" : 62.5, "ReorderLevel" : [ 10, 0, 0, 5, 30 ] }
{ "_id" : 8, "AvgPrice" : 81, "ReorderLevel" : [ 5, 0, 5, 15 ] }
{ "_id" : 9, "AvgPrice" : 21, "ReorderLevel" : [ 25, 25 ] }
{ "_id" : 10, "AvgPrice" : 4.5, "ReorderLevel" : [ 0 ] }
{ "_id" : 11, "AvgPrice" : 43.9, "ReorderLevel" : [ 30, 30, 0 ] }
{ "_id" : 12, "AvgPrice" : 123.79, "ReorderLevel" : [ 0, 30, 25, 15, 0 ] }
{ "_id" : 13, "AvgPrice" : 25.89, "ReorderLevel" : [ 15 ] }
{ "_id" : 14, "AvgPrice" : 34.8, "ReorderLevel" : [ 20, 0, 25 ] }
{ "_id" : 15, "AvgPrice" : 36, "ReorderLevel" : [ 20, 0, 15 ] }
{ "_id" : 16, "AvgPrice" : 18, "ReorderLevel" : [ 15, 15, 10 ] }
{ "_id" : 17, "AvgPrice" : 26, "ReorderLevel" : [ 20, 25, 5 ] }
{ "_id" : 18, "AvgPrice" : 263.5, "ReorderLevel" : [ 15, 5 ] }
{ "_id" : 19, "AvgPrice" : 18.4, "ReorderLevel" : [ 30, 10 ] }
{ "_id" : 20, "AvgPrice" : 46, "ReorderLevel" : [ 15, 0, 25 ] }


Insert the Array of Unique Values from Other Fields Example

The below example will show you how to insert the array of the unique values from other fields for each SupplierID. The result will sort ascending by the SupplierID. This operation will use $addToSet operator.

db.products.aggregate([
                     { $group: { _id: "$SupplierID", AvgPrice: { $max: "$UnitPrice" }, ReorderLevel: { $addToSet: "$ReorderLevel" } } },
                     { $sort: { _id: 1 } }
                   ])

Result:

{ "_id" : 1, "AvgPrice" : 19, "ReorderLevel" : [ 25, 10 ] }
{ "_id" : 2, "AvgPrice" : 22, "ReorderLevel" : [ 20, 0 ] }
{ "_id" : 3, "AvgPrice" : 40, "ReorderLevel" : [ 0, 10, 25 ] }
{ "_id" : 4, "AvgPrice" : 97, "ReorderLevel" : [ 5, 0 ] }
{ "_id" : 5, "AvgPrice" : 38, "ReorderLevel" : [ 0, 30 ] }
{ "_id" : 6, "AvgPrice" : 23.25, "ReorderLevel" : [ 0, 5 ] }
{ "_id" : 7, "AvgPrice" : 62.5, "ReorderLevel" : [ 30, 5, 0, 10 ] }
{ "_id" : 8, "AvgPrice" : 81, "ReorderLevel" : [ 15, 0, 5 ] }
{ "_id" : 9, "AvgPrice" : 21, "ReorderLevel" : [ 25 ] }
{ "_id" : 10, "AvgPrice" : 4.5, "ReorderLevel" : [ 0 ] }
{ "_id" : 11, "AvgPrice" : 43.9, "ReorderLevel" : [ 0, 30 ] }
{ "_id" : 12, "AvgPrice" : 123.79, "ReorderLevel" : [ 25, 30, 15, 0 ] }
{ "_id" : 13, "AvgPrice" : 25.89, "ReorderLevel" : [ 15 ] }
{ "_id" : 14, "AvgPrice" : 34.8, "ReorderLevel" : [ 0, 25, 20 ] }
{ "_id" : 15, "AvgPrice" : 36, "ReorderLevel" : [ 15, 0, 20 ] }
{ "_id" : 16, "AvgPrice" : 18, "ReorderLevel" : [ 10, 15 ] }
{ "_id" : 17, "AvgPrice" : 26, "ReorderLevel" : [ 5, 25, 20 ] }
{ "_id" : 18, "AvgPrice" : 263.5, "ReorderLevel" : [ 5, 15 ] }
{ "_id" : 19, "AvgPrice" : 18.4, "ReorderLevel" : [ 10, 30 ] }
{ "_id" : 20, "AvgPrice" : 46, "ReorderLevel" : [ 25, 0, 15 ] }


Get the First Value for Each Group

The below example will show you how to get the first value of ReorderLevel for each SupplierID. The result will sort ascending by the SupplierID. This operation will use $first operator.

db.products.aggregate([
                     { $group: { _id: "$SupplierID", AvgPrice: { $max: "$UnitPrice" }, ReorderLevel: { $first: "$ReorderLevel" } } },
                     { $sort: { _id: 1 } }
                   ])

Result:

{ "_id" : 1, "AvgPrice" : 19, "ReorderLevel" : 10 }
{ "_id" : 2, "AvgPrice" : 22, "ReorderLevel" : 0 }
{ "_id" : 3, "AvgPrice" : 40, "ReorderLevel" : 25 }
{ "_id" : 4, "AvgPrice" : 97, "ReorderLevel" : 0 }
{ "_id" : 5, "AvgPrice" : 38, "ReorderLevel" : 30 }
{ "_id" : 6, "AvgPrice" : 23.25, "ReorderLevel" : 5 }
{ "_id" : 7, "AvgPrice" : 62.5, "ReorderLevel" : 10 }
{ "_id" : 8, "AvgPrice" : 81, "ReorderLevel" : 5 }
{ "_id" : 9, "AvgPrice" : 21, "ReorderLevel" : 25 }
{ "_id" : 10, "AvgPrice" : 4.5, "ReorderLevel" : 0 }
{ "_id" : 11, "AvgPrice" : 43.9, "ReorderLevel" : 30 }
{ "_id" : 12, "AvgPrice" : 123.79, "ReorderLevel" : 0 }
{ "_id" : 13, "AvgPrice" : 25.89, "ReorderLevel" : 15 }
{ "_id" : 14, "AvgPrice" : 34.8, "ReorderLevel" : 20 }
{ "_id" : 15, "AvgPrice" : 36, "ReorderLevel" : 20 }
{ "_id" : 16, "AvgPrice" : 18, "ReorderLevel" : 15 }
{ "_id" : 17, "AvgPrice" : 26, "ReorderLevel" : 20 }
{ "_id" : 18, "AvgPrice" : 263.5, "ReorderLevel" : 15 }
{ "_id" : 19, "AvgPrice" : 18.4, "ReorderLevel" : 30 }
{ "_id" : 20, "AvgPrice" : 46, "ReorderLevel" : 15 }


Get the Last Value for Each Group

The below example will show you how to get the last value of ReorderLevel for each SupplierID. The result will sort ascending by the SupplierID. This operation will use $last operator.

db.products.aggregate([
                     { $group: { _id: "$SupplierID", AvgPrice: { $max: "$UnitPrice" }, ReorderLevel: { $last: "$ReorderLevel" } } },
                     { $sort: { _id: 1 } }
                   ])

Result:

{ "_id" : 1, "AvgPrice" : 19, "ReorderLevel" : 25 }
{ "_id" : 2, "AvgPrice" : 22, "ReorderLevel" : 0 }
{ "_id" : 3, "AvgPrice" : 40, "ReorderLevel" : 0 }
{ "_id" : 4, "AvgPrice" : 97, "ReorderLevel" : 5 }
{ "_id" : 5, "AvgPrice" : 38, "ReorderLevel" : 0 }
{ "_id" : 6, "AvgPrice" : 23.25, "ReorderLevel" : 0 }
{ "_id" : 7, "AvgPrice" : 62.5, "ReorderLevel" : 30 }
{ "_id" : 8, "AvgPrice" : 81, "ReorderLevel" : 15 }
{ "_id" : 9, "AvgPrice" : 21, "ReorderLevel" : 25 }
{ "_id" : 10, "AvgPrice" : 4.5, "ReorderLevel" : 0 }
{ "_id" : 11, "AvgPrice" : 43.9, "ReorderLevel" : 0 }
{ "_id" : 12, "AvgPrice" : 123.79, "ReorderLevel" : 0 }
{ "_id" : 13, "AvgPrice" : 25.89, "ReorderLevel" : 15 }
{ "_id" : 14, "AvgPrice" : 34.8, "ReorderLevel" : 25 }
{ "_id" : 15, "AvgPrice" : 36, "ReorderLevel" : 15 }
{ "_id" : 16, "AvgPrice" : 18, "ReorderLevel" : 10 }
{ "_id" : 17, "AvgPrice" : 26, "ReorderLevel" : 5 }
{ "_id" : 18, "AvgPrice" : 263.5, "ReorderLevel" : 5 }
{ "_id" : 19, "AvgPrice" : 18.4, "ReorderLevel" : 10 }
{ "_id" : 20, "AvgPrice" : 46, "ReorderLevel" : 25 }


Show Detailed Information of Aggregate Method

The previous example of the aggregate method can describe with the detailed information using explain() method.

db.products.explain().aggregate([
                     { $group: { _id: "$SupplierID", $round: { TotalPrice: { $sum: "$UnitPrice" } } } },
                     { $sort: { _id: 1 } }
                   ])

Result:

{
    "waitedMS" : NumberLong(0),
    "stages" : [
        {
            "$cursor" : {
                "query" : {

                },
                "fields" : {
                    "SupplierID" : 1,
                    "UnitPrice" : 1,
                    "_id" : 0
                },
                "queryPlanner" : {
                    "plannerVersion" : 1,
                    "namespace" : "mongo-examples.products",
                    "indexFilterSet" : false,
                    "parsedQuery" : {
                        "$and" : [ ]
                    },
                    "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                            "$and" : [ ]
                        },
                        "direction" : "forward"
                    },
                    "rejectedPlans" : [ ]
                }
            }
        },
        {
            "$group" : {
                "_id" : "$SupplierID",
                "TotalPrice" : {
                    "$sum" : "$UnitPrice"
                }
            }
        },
        {
            "$sort" : {
                "sortKey" : {
                    "_id" : 1
                }
            }
        }
    ],
    "ok" : 1
}


That it's, a few examples of the MongoDB Aggregate Methods.

That just the basic. If you need more deep learning about MongoDB or related you can take the following cheap course:

Thanks!

Related Articles

Programming Blog
HTML 5 Tutorial Java Flutter Tutorial Ionic Framework React Native Javascript MongoDB Miscellaneous Angular Groovy and Grails Vue.js React.js ASP.NET Core CSS 3 Node.js
All Articles
Popular Articles:
Zizo Wireless