MongoDB Arithmetic Operators
MongoDB provides a rich set of arithmetic operators that are primarily used as expressions within the aggregation pipeline to perform mathematical calculations on numeric values. These expressions operate on the current document in the pipeline and conduct in-memory transformations of documents.
Here’s a comprehensive explanation of the 14 arithmetic operators you’ve queried:
$add Operator
The $add operator is used to sum numeric values. It can take one or more argument expressions.
Syntax:
{ $add: [ <expression1>, <expression2>, ... ] }Example: To calculate a totalPay field by adding the values of salary and bonus from documents in an employees collection:
db.employees.aggregate([
{
"$project": {
"totalPay": { "$add": ["$salary", "$bonus"] } // Adds salary and bonus to create totalPay
}
}
])$subtract Operator
The $subtract operator calculates the difference between two numbers. It takes two argument expressions, subtracting the second from the first.
Syntax:
{ $subtract: [ <expression1>, <expression2> ] }Example: To calculate netPay by subtracting 401k contributions from totalPay:
db.employees.aggregate([
{
"$project": {
"netPay": { "$subtract": ["$totalPay", "$401k"] } // Subtracts 401k from totalPay
}
}
])$multiply Operator
The $multiply operator computes the product of one or more numbers. It accepts any number of argument expressions.
Syntax:
{ $multiply: [ <expression1>, <expression2>, ... ] }Example: To calculate totalCost by multiplying quantity and price:
db.orders.aggregate([
{
"$project": {
"totalCost": { "$multiply": ["$quantity", "$price"] }
}
}
])$divide Operator
The $divide operator returns the result of dividing the first number by the second. It accepts two argument expressions.
Syntax:
{ $divide: [ <expression1>, <expression2> ] }Example: To calculate the unitPrice by dividing totalCost by quantity:
db.products.aggregate([
{
"$project": {
"unitPrice": { "$divide": ["$totalCost", "$quantity"] }
}
}
])$abs Operator
The $abs operator returns the absolute value of a number.
Syntax:
{ $abs: <expression> }Example: To get the absolute value of a temperatureChange field:
db.sensorReadings.aggregate([
{
"$project": {
"absoluteChange": { "$abs": "$temperatureChange" }
}
}
])$floor Operator
The $floor operator returns the largest integer less than or equal to the specified expression. Effectively, it rounds a number down to the nearest integer.
Syntax:
{ $floor: <expression> }Example: To round down a gpa field to the nearest whole number:
db.students.aggregate([
{
"$project": {
"floorGPA": { "$floor": "$gpa" }
}
}
])$ceil Operator
The $ceil operator returns the smallest integer greater than or equal to the specified expression. Effectively, it rounds a number up to the nearest integer.
Syntax:
{ $ceil: <expression> }Example: To round up a progressPercentage field to the next whole number:
db.tasks.aggregate([
{
"$project": {
"ceilProgress": { "$ceil": "$progressPercentage" }
}
}
])$mod Operator
The $mod operator returns the remainder of the first number divided by the second. It takes two argument expressions. It is important to note that the $mod operator may not use an index for queries.
Syntax:
{ $mod: [ <expression1>, <expression2> ] }Example: To find order subtotals that are evenly divisible by 3:
db.orders.find({
"subtotal": { "$mod": } // Finds documents where subtotal divided by 3 has a remainder of 0
})$sqrt Operator
The $sqrt operator calculates the square root of a number. It accepts a single argument expression that must resolve to a non-negative number.
Syntax:
{ $sqrt: <expression> }Example: To calculate the square root of a distanceSquared field:
db.points.aggregate([
{
"$project": {
"distance": { "$sqrt": "$distanceSquared" }
}
}
])$pow Operator
The $pow operator raises a number to a specified exponent. It accepts two argument expressions: the first is the base number, and the second is the non-negative exponent.
Syntax:
{ $pow: [ <expression1>, <expression2> ] }Example: To calculate the volume of a cube given its sideLength:
db.cubes.aggregate([
{
"$project": {
"volume": { "$pow": ["$sideLength", 3] } // Raises sideLength to the power of 3
}
}
])$exp Operator
The $exp operator raises Euler’s number (e) to the specified exponent.
Syntax:
{ $exp: <expression> }Example: To calculate the exponential growth based on a growthRate field:
db.data.aggregate([
{
"$project": {
"exponentialValue": { "$exp": "$growthRate" }
}
}
])$log Operator
The $log operator calculates the logarithm of a number in a specified base. It accepts two argument expressions: the first must resolve to a non-negative number (the input for the logarithm), and the second must resolve to a positive number (the base).
Syntax:
{ $log: [ <expression1>, <expression2> ] }Example: To calculate the logarithm of a value in base 2:
db.numbers.aggregate([
{
"$project": {
"logBase2": { "$log": ["$value", 2] }
}
}
])$log10 Operator
The $log10 operator calculates the logarithm base 10 of a number. It accepts a single argument expression that must resolve to a non-negative number.
Syntax:
{ $log10: <expression> }Example: To calculate the base 10 logarithm of a magnitude field:
db.events.aggregate([
{
"$project": {
"logMagnitude": { "$log10": "$magnitude" }
}
}
])$ln Operator
The $ln operator calculates the natural logarithm (base e) of a number. It accepts a single argument expression that must resolve to a non-negative number.
Syntax:
{ $ln: <expression> }Example: To calculate the natural logarithm of a population field:
db.countries.aggregate([
{
"$project": {
"naturalLogPopulation": { "$ln": "$population" }
}
}
])MongoDB’s Aggregation Framework and the Stage
Data records are processed by MongoDB’s aggregation framework via a pipeline of steps, or operations. Documents are fed into each step, which then transforms them before sending them on to the next.
The $group stage is one of the most basic phases in this process. Its primary function is to classify input documents according to a given _id expression. This _id expression generates a single document at the $group stage for each unique value.
In the $group stage, the _id field can be any valid phrase, such as “$by_user” or “$dept”, a compound key (many fields), or null to group all documents.
Basic example of using $group to count each user’s tutorials, comparable to SELECT by_user, count(*) FROM mycol GROUP BY by_user SQL query:
db.mycol.aggregate([
{
$group : {
_id : "$by_user", // Groups documents by the 'by_user' field
num_tutorial : {$sum : 1} // For each occurrence, increments 'num_tutorial' by 1
}
}
])This would result in output like this, showing a count for each distinct user:
{ "_id" : "tutorials point", "num_tutorial" : 2 }
{ "_id" : "Neo4j", "num_tutorial" : 1 }Pipeline Optimisation Considerations
Reducing the quantity and size of documents as early in your aggregation pipeline as possible is usually a good idea for best results. This implies:
- Removing unnecessary documents early on by using a $match stage. This makes the procedure much faster by enabling MongoDB to utilise indexes.
- Limiting the fields passed to later stages with a $project stage helps reduce document size, particularly for large documents.
It is crucial to keep in mind that the $group stage is regarded as a “roadblock” action as, in most cases, it must gather all of the input documents for a group before processing and moving on to the following step. In the same way, a $sort stage is a “roadblock” process that gathers all documents in order to appropriately sort them. Each shard in a sharded environment is initially subjected to $group operations, after which the output is routed to the mongos for final grouping and other pipeline processing. Operations on sharded datasets are supported by the aggregation pipeline.
MongoDB will error if a pipeline stage exceeds a memory limit (e.g., 100 megabytes), however the allowDiskUse option allows aggregation pipeline stages to write data to temporary files for larger datasets. The aggregate() method’s explanation option, which displays information like index usage, can be used to examine how well your aggregation query works.
MongoDB is a fair dinkum choice for sophisticated data crunching because it allows you to execute strong and efficient data analysis right within the database by carefully designing the pipeline and strategically combining the $group stage with different accumulator operators.