MongoDB Basics – Aggregation and Group Examples Tutorial

MongoDB Basics – Aggregation and Group Examples Tutorial

In this tutorial we learn about how to use Aggregation operations to process data matching a certain criteria and perform some operation to return computed results. MongoDB provides three different ways to perform aggregation: the aggregation pipeline, the map-reduce function, and single purpose aggregation methods and commands.

Show All Documents in Employee Collection

For this example, let’s assume these are all of the names in the employee collection.

> db.employee.find({}, {_id:0})
{"first_name":"John", "last_name":"Smith", "pay_rate":57000, "active":1}
{"first_name":"Jack", "last_name":"Walder", "pay_rate":36000, "active":1}
{"first_name":"Robert", "last_name":"Valle", "pay_rate":72000, "active":1}
{"first_name":"Michael", "last_name":"Jones", "pay_rate":72000, "active":0}
{"first_name":"John", "last_name":"Holden", "pay_rate":94500, "active":1}
{"first_name":"Olivia", "last_name":"Wilde", "pay_rate":124500, "active":1}
{"first_name":"Amaury", "last_name":"Valdes", "pay_rate":59800, "active":1}
{"first_name":"John", "last_name":"Fitzgerald", "pay_rate":79750, "active":1}
{"first_name":"John", "last_name":"Henry", "pay_rate":43850, "active":1}
{"first_name":"David", "last_name":"LaSalle", "pay_rate":58500, "active":1}
{"first_name":"Halle", "last_name":"Gululi", "pay_rate":85000, "active":1}
{"first_name":"Julie", "last_name":"Mehaul", "pay_rate":73200, "active":1}
{"first_name":"Jennifer", "last_name":"Stevens", "pay_rate":84000, "active":1}
{"first_name":"David", "last_name":"Jackson", "pay_rate":87000, "active":1}
{"first_name":"Jennifer", "last_name":"Shields", "pay_rate":43000, "active":1}
{"first_name":"Jennifer", "last_name":"Roberts", "pay_rate":94000, "active":1}
>

Collection Aggregation using Group by and Calculating a Total Sum

db.collection.aggregate(pipeline, options)

The following aggregation operation will select documents that have an active flag set to 1, it then groups the matching documents by the first_name field and calculates the total for each first_name field from the sum of the pay_rate field, and sorts the results by the total field in descending order as denoted with the -1.

Please Note
Although this code works well we might want to add one additional change so that we are able to know how many employees are contributing to the aggregate total from all of the pay_rates. The next example will help with that.
db.employee.aggregate(
  [ {$match: { active: 1 } },
    {$group: { _id: "$first_name", total: {$sum: "$pay_rate"}}},
    {$sort: { total: -1 } }
  ])
  
{ "_id" : "John", "total" : 275100 }
{ "_id" : "Jennifer", "total" : 221000 }
{ "_id" : "David", "total" : 145500 }
{ "_id" : "Olivia", "total" : 124500 }
{ "_id" : "Halle", "total" : 85000 }
{ "_id" : "Julie", "total" : 73200 }
{ "_id" : "Robert", "total" : 72000 }
{ "_id" : "Amaury", "total" : 59800 }
{ "_id" : "Jack", "total" : 36000 }
>

Collection Aggregation using Group by and Calculating a Count

Now with the latest change we are able to sum the total number of documents that make up each aggregate total. Lets look at one example. If we pick “Jennifer”, we can see that the total amount of $221,000 is now as a result of 3 employees whose aggregate pay_rate equals $221,000. Looking at the full dataset provided earlier in this post, we see that Jennifer Stevens is at $84,000, Jennifer Shields is at $43,000 and Jennifer Roberts is at $94,000 — their aggregate is the $221,000 calculated by MongoDB’s aggregate method.

> db.employee.aggregate(
  [ { $match: { active: 1 } },
    { $group: { _id: "$first_name", 
                total: { $sum: "$pay_rate" } , 
                count: { $sum: 1 } } },
    { $sort: { total: -1 } }
  ])
  
{ "_id" : "John", "total" : 275100, "count" : 4 }
{ "_id" : "Jennifer", "total" : 221000, "count" : 3 }
{ "_id" : "David", "total" : 145500, "count" : 2 }
{ "_id" : "Olivia", "total" : 124500, "count" : 1 }
{ "_id" : "Halle", "total" : 85000, "count" : 1 }
{ "_id" : "Julie", "total" : 73200, "count" : 1 }
{ "_id" : "Robert", "total" : 72000, "count" : 1 }
{ "_id" : "Amaury", "total" : 59800, "count" : 1 }
{ "_id" : "Jack", "total" : 36000, "count" : 1 }
>

Modifying Document Fields in Aggregation using $Project

Consider the device collection containing the following documents:

For this example we will consider using sample data from the Global Universal Device Identier Database maintained by the FDA for all medical devices. The FDA is establishing a unique device identification system to adequately identify medical devices through their distribution and use.
This identifier has several parts associated with it. For this example, we will be using $project to help us with this endeavor.

> db.device.find({}, {_id:0})
{
  "udi" : "(01)51022222233336(11)141231(17)150707(10)A213B1(21)1234",
  "brand" : "ProbeChek ALK Negative Control Slides",
  "model" : "06N38-005",
  "company" : "Abbott Molecular Inc."
}
{
  "udi" : "(01)2762873839773(11)130630(17)130815(10)C25D63(21)7738",
  "brand" : "BLOOD COLLECTION RESERVOIR",
  "model" : "120 µm Filter",
  "company" : "sorin group italia srl"
}
{
  "udi" : "(01)07332414112370(11)140330(17)140831(10)G62H22(21)1126",
  "brand" : "Blood Circuit Set",
  "model" : "UF 500-HCT",
  "company" : "GAMBRO UF SOLUTIONS, INC"
}
>

Suppressing Fields, adding new Fields using $substr, and changing Case of Output Documents

Include Specific Fields in Output Documents

For my example, I would like to include the brand, udi, and model fields. To do that I will set the value of those three fields to 1.

Suppressing _id Field in Output Documents

The _id field is included in the results by default. To suppress it from showing up in the output documents created by the aggregate method we set the value of _id to 0.

Modifying the Case of Fields in Output Documents

In our sample data, you will notice that the company field consisted of inconsistent case. To address this issue I decided to convert to the case to uppercase using MongoDB’s $toUpper string function. Once the aggregation executes you will note that company values are all convert to uppercase.

Create new Fields from other data in the Output Documents

In this example, we are adding some new fields inside the udi-details object comprised from the elements that compose the Universal Device Identier (UDI) field.

>db.device.aggregate(
 [
  {
   $project: {
    _id:0,
    brand: 1,
    udi: 1,
    "udi-details": {
     "device-id": { $substr: [ "$udi", 4, 14 ] },
     "mfg-date": { $substr: [ "$udi", 22, 6 ] },
     "exp-date": { $substr: [ "$udi", 32, 6 ] },
     "lot-num": { $substr: [ "$udi", 42, 6 ] },
     "serial-num": { $substr: [ "$udi", 52, 4] }
    },
    company: { $toUpper : "$company"},
    model: 1
   }
  }
 ]
)
{
  "udi" : "(01)51022222233336(11)141231(17)150707(10)A213B1(21)1234",
  "brand" : "ProbeChek ALK Negative Control Slides",
  "model" : "06N38-005",
  "udi-details" : {
    "device-id" : "51022222233336",
    "mfg-date" : "141231",
    "exp-date" : "150707",
    "lot-num" : "A213B1",
    "serial-num" : "1234"
  },
  "company" : "ABBOTT MOLECULAR INC."
}, 
{
  "udi" : "(01)27628273839773(11)130630(17)130815(10)C25D63(21)7738",
  "brand" : "BLOOD COLLECTION RESERVOIR",
  "model" : "120 µm Filter",
  "udi-details" : {
    "device-id" : "27628273839773",
    "mfg-date" : "130630",
    "exp-date" : "130815",
    "lot-num" : "C25D63",
    "serial-num" : "7738"
  },
  "company" : "SORIN GROUP ITALIA SRL"
}, 
{
  "udi" : "(01)07332414112370(11)140330(17)140831(10)G62H22(21)1126",
  "brand" : "Blood Circuit Set",
  "model" : "UF 500-HCT",
  "udi-details" : {
    "device-id" : "07332414112370",
    "mfg-date" : "140330",
    "exp-date" : "140831",
    "lot-num" : "G62H22",
    "serial-num" : "1126"
  },
  "company" : "GAMBRO UF SOLUTIONS, INC"
}
>

That’s It!

I hope you enjoyed this tutorial. It was certainly a lot of fun putting it together and testing it out. Please continue to share the love and like us so that we can continue bringing you quality tutorials. Happy Coding!!!

mongodb_aggregation

Related Posts

Please Share Us on Social Media

Facebooktwitterredditpinterestlinkedinmail

Written by

Amaury holds a Masters degree in Computer Science and is a passionate Senior Java Software Engineer and Architect. He founded avaldes.com in late 2008 as a forum to share ideas and passion for Java, Spring, Jax-RS, REST, AngularJS, ExtJS, MongoDB, Sybase, Oracle. and DB2 He has good experience in Java/J2EE Web-Application development for Banking, Telecom, Retail and E-Commerce Domains. He is also very passionate about fitness and weight training.

Leave a Reply

Your email address will not be published. Required fields are marked *