TL;DR Summary: MongoDB aggregate functions enable complex data analysis akin to SQL, covering essential functionalities like count and sum. A tutorial illustrates these functions with examples, including the WordCount program and MapReduce techniques.
- Aggregate functions comprise count, sum, average, min, and max.
- Examples include WordCount and distinct operations for data analysis.
This is the second part of the tutorial on how to use NodeJS with MongoDB. Here we switch to using the regular MongoDB shell and commands to make the study of aggregate functions simpler.
To show how to use aggregate functions, we will first explain how to do basic queries. Then we will show how to do the WordCount program, which is what people start with when they are first learning, for example, Apache Spark.
Basically, there are two aggregate functions: aggregate and MapReduce. Aggregate functions are the same as the familiar SQL command:
MapReduce
SQL select xxx, count (xxx) from table group by xxx
The aggregate functions are count, sum, average, min, max, etc.
The data that we used in the first part was smoker survey date. It has a complex structure. Recall that the schema is this:
var schema = new mongoose.Schema({
cachedContents : {
largest : String,
non_nill : Number,
nill : Number,
tip : [{
item : String,
count: Number
}] ,
smallest : String,
format : {
displayStyle : String,
align : String
}
}
});
And the sample data looks like this:
"_id" : ObjectId("59e9a6144bde260fa07e68f1"),
"cachedContents" : {
"nill" : 0,
"non_nill" : 14069,
"largest" : "WY",
"tip" : [
{
"item" : "NC",
"count" : 20,
"_id" : ObjectId("59e9a6144bde260fa07e6905")
},
{
"item" : "ND",
"count" : 19,
"_id" : ObjectId("59e9a6144bde260fa07e6904")
},
{
"item" : "OH",
"count" : 18,
"_id" : ObjectId("59e9a6144bde260fa07e6903")
},
…. (shortened)…..
{
"item" : "WY",
"count" : 2,
"_id" : ObjectId("59e9a6144bde260fa07e68f3")
},
{
"item" : "IN",
"count" : 1,
"_id" : ObjectId("59e9a6144bde260fa07e68f2")
}
]
},
"__v" : 0
}
That is complicated to work with, since it has an array of embedded documents: tip:[{item, count}]. So we will first show how to query this data, then we will make simpler data to show the aggregate and mapreduce functions.
Simple Queries
A simple find query will output all fields. Like this:
db.smokersmodels.find({ "cachedContents.largest" : "2016" }).pretty()
"_id" : ObjectId("59e9a6144bde260fa07e6fda"),
"cachedContents" : {
"nill" : 0,
"non_nill" : 14069,
"largest" : "2016",
"tip" : [
{
"item" : "2014-2015",
"count" : 20,
"_id" : ObjectId("59e9a6144bde260fa07e6fe5")
},
… (and so forth) ….
Notice that we use pretty() to format the output to JSON so that it is easier to read.
Notice too that we put quote marks around the field names. If you look at the instructions from MongoDB they leave then off. But that causes a syntax error when we use the mongo shell. There seems to be some confusion about that if you look at stackoverflow. We are using MongoDB version mongodb-linux-x86_64-debian81-3.4.9.
And we use the dot notation like cachedContents.largest to pull fields nested in our document.
To output specific fields we put them after the query and put the number 1 to indicate which ones to print. Notice here how the output looks when we leave off pretty().
db.smokersmodels.find({ "cachedContents.largest" : "2016" } , { "cachedContents.largest" : 1 })
{ "_id" : ObjectId("59e9a6144bde260fa07e688c"), "cachedContents" : { "largest" : "2016" } }
{ "_id" : ObjectId("59e9a6144bde260fa07e68ad"), "cachedContents" : { "largest" : "2016" } }
{ "_id" : ObjectId("59e9a6144bde260fa07e68e5"), "cachedContents" : { "largest" : "2016" } }
{ "_id" : ObjectId("59e9a6144bde260fa07e693d"), "cachedContents" : { "largest" : "2016" } }
{ "_id" : ObjectId("59e9a6144bde260fa07e69c3"), "cachedContents" : { "largest" : "2016" } }
Simple Aggregation
Use the distinct operation to do simple aggregation:
db.smokersmodels.distinct("cachedContents.largest")
[
"2016",
"WY",
"Wyoming",
"Tobacco Use – Survey Data",
"Cigarette Use (Adults)",
"Smoking Status",
"BRFSS",
"Some Days",
"%",
"Percentage",
"87.9",
"*",
"Data in these cells have been suppressed because of a small sample size",
"14.9",
"85.4",
"90.4",
"39839",
"Overall",
"White",
"All Ages",
"All Grades",
"BEH",
"101BEH"
]
Word Count
Now we make some simpler data to illustrate how to do the WordCount program with MongoDB.
First, create some data:
db.words.insertMany( [
{ word: "the" },
{ word: "rain" },
{ word: "in" },
{ word: "spain" },
{ word: "spain" }
]);
They print it out to see what it looks like:
db.words.find().pretty()
{ "_id" : ObjectId("59eb051e878eb4d6aca74243"), "word" : "the" }
{ "_id" : ObjectId("59eb051e878eb4d6aca74244"), "word" : "rain" }
{ "_id" : ObjectId("59eb051e878eb4d6aca74245"), "word" : "in" }
{ "_id" : ObjectId("59eb051e878eb4d6aca74246"), "word" : "spain" }
{ "_id" : ObjectId("59eb051e878eb4d6aca74247"), "word" : "spain" }
The trick with the WordCount program has always been to make the (key,value) pairs (word, 1) and then sum the number 1:
db.words.aggregate([ {$group: { _id: "$word", cnt: {$sum: 1} } } ])
{ "_id" : "in", "cnt" : 1 }
{ "_id" : "rain", "cnt" : 1 }
{ "_id" : "spain", "cnt" : 2 }
{ "_id" : "the", "cnt" : 1 }
As you can see, the word spain occurs 2 times.
Doing this with mapReduce basically does the same thing. We first create (key,value) pairs (word, 1) using the emit function. Then we use the JavaScript Array.sum function to sum the number 1.
db.words.mapReduce(
function() { emit(this.word,1); },
function(key, values) {return Array.sum(values)}, {
query:{},
} out:"total_matches"
).find()
{ "_id" : "in", "value" : 1 }
{ "_id" : "rain", "value" : 1 }
{ "_id" : "spain", "value" : 2 }
{ "_id" : "the", "value" : 1 }
The result is the same.
Additional Resources
MongoDB Guide
- MongoDB Overview: Getting Started with MongoDB
- How to Install MongoDB on Ubuntu and Mac
- Creating a Database in MongoDB
- MongoDB Shell Basic Commands
- MongoDB Compass: Using the Mongo GUI
- MongoDB Role-Based Access Control (RBAC) Explained
- MongoDB Projection & Projection Operators Explained
- MongoDB Aggregate Functions Explained
- MongoDB Sorting: sort() Method & Examples
- Using Push & Pull Operators in MongoDB
- How To Use MongoDB $unwind
- 23 Common MongoDB Query Operators & How to Use Them
- MongoDB GeoLocation Query Examples
- Introduction to MongoDB Transactions
- MongoDB Indexes: Top Index Types & How to Manage Them
- Top MongoDB Commands You Need to Know
- MongoDB Replication: A Complete Introduction
- How to Setup a MongoDB Cluster
- MongoDB Sharding: Concepts, Examples & Tutorials
- MongoDB Atlas: Setting Up & Using Managed MongoDB
- How To Run MongoDB as a Docker Container
- MongoDB Memory Usage, Management, & Requirements
- How To Use mongodump for MongoDB Backups
- Mongorestore Examples for Restoring MongoDB Backups
- How To Use PyMongo
- How to Use Mongoose for MongoDB & NodeJS
- MongoDB Cheat Sheet
- MongoDB vs Cassandra: NoSQL Databases Compared
- PostgreSQL vs MongoDB: Comparing Databases
- MySQL vs MongoDB: Comparing Databases
- MongoDB Certifications: An Introduction
These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.