MongoDB Mathematical Aggregation Functions with Examples
- Step 1: Insert a Dataset into MongoDB Collection
- First, we'll insert a sample dataset into a MongoDB collection named `orders`. Here’s how you can insert the data using the MongoDB shell or a Node.js script.
- Insert the Dataset:
db.orders.insertMany([
{
"_id": 1,
"customerId": "C001",
"items": 2,
"amount": 500,
"status": "Completed"
},
{
"_id": 2,
"customerId": "C002",
"items": 1,
"amount": 300,
"status": "Completed"
},
{
"_id": 3,
"customerId": "C001",
"items": 4,
"amount": 1200,
"status": "Pending"
},
{
"_id": 4,
"customerId": "C003",
"items": 5,
"amount": 1500,
"status": "Completed"
},
{
"_id": 5,
"customerId": "C002",
"items": 3,
"amount": 900,
"status": "Pending"
},
{
"_id": 6,
"customerId": "C001",
"items": 1,
"amount": 400,
"status": "Completed"
}
])
- Step 2: MongoDB Aggregation Functions
- The $mod operator returns the remainder of the division of one number by another.
- Example: Find orders where the total amount is divisible by 500.
db.orders.aggregate([
{ $project: { divisibleBy500: { $mod: ["$amount", 500] } } },
{ $match: { divisibleBy500: 0 } }
])
- Explanation: This query checks if the amount is divisible by 500.
- Output:
[{ _id: 1, divisibleBy500: 0 }, { _id: 4, divisibleBy500: 0 }]
$abs (Absolute Value)
- The $abs operator returns the absolute value of a number.
- Example: Calculate the absolute value of the difference between two amounts.
db.orders.aggregate([
{
$project: {
absoluteDifference: {
$abs: { $subtract: ["$amount", 1000] }
}
}
}
])
- Explanation: This query calculates the absolute difference between the amount and 1000.
[
{ _id: 1, absoluteDifference: 500 },
{ _id: 2, absoluteDifference: 700 },
{ _id: 3, absoluteDifference: 200 },
{ _id: 4, absoluteDifference: 500 },
{ _id: 5, absoluteDifference: 100 },
{ _id: 6, absoluteDifference: 600 }
]
$round (Round Numbers)
- The $round operator rounds a number to a specified number of decimal places.
- Example: Round the order amount to the nearest integer.
db.orders.aggregate([
{ $project: { roundedAmount: { $round: "$amount" } } }
])
- Explanation: This query rounds the amount to the nearest integer.
[
{ _id: 1, roundedAmount: 500 },
{ _id: 2, roundedAmount: 300 },
{ _id: 3, roundedAmount: 1200 },
{ _id: 4, roundedAmount: 1500 },
{ _id: 5, roundedAmount: 900 },
{ _id: 6, roundedAmount: 400 }
]
$exp (Exponentiation)
- The $exp operator raises e (Euler’s number, approximately 2.718) to the specified exponent.
- Example: Calculate e raised to the power of the amount.
db.orders.aggregate([
{ $project: { expAmount: { $exp: "$amount" } } }
])
- Explanation: This query calculates the exponential value of the amount field for each order.
[
{ _id: 1, expAmount: 1.4035922178528375e+217 },
{ _id: 2, expAmount: 1.9424263952412558e+130 },
{ _id: 3, expAmount: Infinity },
{ _id: 4, expAmount: Infinity },
{ _id: 5, expAmount: Infinity },
{ _id: 6, expAmount: 5.221469689764144e+173 }
]
$log (Natural Logarithm)
- The $log operator returns the natural logarithm (base e) of a number.
- Example: Calculate the natural logarithm of the amount.
db.orders.aggregate([{
$project: { logAmount: { $log: ["$amount", 2] } }
}])
- Explanation: This query calculates the natural logarithm of the amount.
[
{ _id: 1, logAmount: 8.965784284662087 },
{ _id: 2, logAmount: 8.228818690495881 },
{ _id: 3, logAmount: 10.228818690495881 },
{ _id: 4, logAmount: 10.550746785383243 },
{ _id: 5, logAmount: 9.813781191217037 },
{ _id: 6, logAmount: 8.643856189774725 }
]
$log10 (Logarithm Base 10)
- The $log10 operator returns the base 10 logarithm of a number.
- Example: Calculate the base 10 logarithm of the amount.
db.orders.aggregate([
{ $project: { log10Amount: { $log10: "$amount" } } }
])
- Explanation: This query calculates the base 10 logarithm of the amount.
[
{ _id: 1, log10Amount: 2.6989700043360187 },
{ _id: 2, log10Amount: 2.4771212547196626 },
{ _id: 3, log10Amount: 3.0791812460476247 },
{ _id: 4, log10Amount: 3.1760912590556813 },
{ _id: 5, log10Amount: 2.9542425094393248 },
{ _id: 6, log10Amount: 2.6020599913279625 }
]
$pow (Power of a Number)
- The $pow operator raises a number to a specified exponent.
- Example: Raise the amount to the power of 2.
db.orders.aggregate([
{ $project: { squaredAmount: { $pow: ["$amount", 2] } } }
])
- Explanation: This query raises the amount field to the power of 2 for each order.
[
{ _id: 1, squaredAmount: 250000 },
{ _id: 2, squaredAmount: 90000 },
{ _id: 3, squaredAmount: 1440000 },
{ _id: 4, squaredAmount: 2250000 },
{ _id: 5, squaredAmount: 810000 },
{ _id: 6, squaredAmount: 160000 }
]
$sqrt (Square Root)
- The $sqrt operator returns the square root of a number.
- Example: Calculate the square root of the amount.
db.orders.aggregate([
{ $project: { sqrtAmount: { $sqrt: "$amount" } } }
])
- Explanation: This query calculates the square root of the amount.
[
{ _id: 1, sqrtAmount: 22.360679774997898 },
{ _id: 2, sqrtAmount: 17.320508075688775 },
{ _id: 3, sqrtAmount: 34.64101615137755 },
{ _id: 4, sqrtAmount: 38.72983346207417 },
{ _id: 5, sqrtAmount: 30 },
{ _id: 6, sqrtAmount: 20 }
]
$trunc (Truncate Decimal Places)
- The $trunc operator truncates a number to the specified decimal places or removes the decimal part.
- Example: Truncate the amount to zero decimal places.
db.orders.aggregate([
{ $project: { truncatedAmount: { $trunc: "$amount" } } }
])
- Explanation: This query truncates the amount to remove the decimal part, essentially rounding it down to the nearest whole number.
[
{ _id: 1, truncatedAmount: 500 },
{ _id: 2, truncatedAmount: 300 },
{ _id: 3, truncatedAmount: 1200 },
{ _id: 4, truncatedAmount: 1500 },
{ _id: 5, truncatedAmount: 900 },
{ _id: 6, truncatedAmount: 400 }
]
$sin, $cos, $tan (Trigonometric Functions)
- These operators return the sine, cosine, and tangent of a number, respectively.
- Example: Calculate the sine of the amount.
db.orders.aggregate([
{ $project: { sinAmount: { $sin: "$amount" } } }
])
// output
[
{ _id: 1, sinAmount: -0.46777180532247614 },
{ _id: 2, sinAmount: -0.9997558399011495 },
{ _id: 3, sinAmount: -0.08827860647172615 },
{ _id: 4, sinAmount: -0.9939019569066535 },
{ _id: 5, sinAmount: 0.9978032744219705 },
{ _id: 6, sinAmount: -0.8509193596391765 }
]
- Example: Calculate the cosine of the amount.
db.orders.aggregate([
{ $project: { cosAmount: { $cos: "$amount" } } }
])
// output
[
{ _id: 1, cosAmount: -0.883849273431478 },
{ _id: 2, cosAmount: -0.022096619278683942 },
{ _id: 3, cosAmount: 0.9960958225188027 },
{ _id: 4, cosAmount: -0.11026740251372914 },
{ _id: 5, cosAmount: 0.06624670220315812 },
{ _id: 6, cosAmount: -0.525296338642536 }
]
- Example: Calculate the tangent of the amount.
db.orders.aggregate([
{ $project: { tanAmount: { $tan: "$amount" } } }
])
// output
[
{ _id: 1, tanAmount: 0.52924386474448 },
{ _id: 2, tanAmount: 45.244742070819356 },
{ _id: 3, tanAmount: -0.08862461268886584 },
{ _id: 4, tanAmount: 9.013560982203286 },
{ _id: 5, tanAmount: 15.061931254510103 },
{ _id: 6, tanAmount: 1.6198844291169272 }
]
- Explanation: These queries calculate the sine, cosine, or tangent of the amount field, respectively.
$degreesToRadians (Convert Degrees to Radians)
- The $degreesToRadians operator converts an angle in degrees to radians.
- Example: Convert the amount (as an angle) from degrees to radians.
db.orders.aggregate([
{ $project: { radiansAmount: { $degreesToRadians: "$amount" } } }
])
// output
[
{ _id: 1, radiansAmount: 8.726646259971648 },
{ _id: 2, radiansAmount: 5.235987755982989 },
{ _id: 3, radiansAmount: 20.943951023931955 },
{ _id: 4, radiansAmount: 26.17993877991494 },
{ _id: 5, radiansAmount: 15.707963267948966 },
{ _id: 6, radiansAmount: 6.981317007977318 }
]
- Explanation: This query converts the amount from degrees to radians.
$radiansToDegrees (Convert Radians to Degrees)
- The $radiansToDegrees operator converts an angle in radians to degrees.
- Example: Convert the amount (as an angle) from radians to degrees.
db.orders.aggregate([
{ $project: { degreesAmount: { $radiansToDegrees: "$amount" } } }
])
// output
[
{ _id: 1, degreesAmount: 28647.88975654116 },
{ _id: 2, degreesAmount: 17188.733853924696 },
{ _id: 3, degreesAmount: 68754.93541569878 },
{ _id: 4, degreesAmount: 85943.66926962348 },
{ _id: 5, degreesAmount: 51566.20156177409 },
{ _id: 6, degreesAmount: 22918.31180523293 }
]
- Explanation: This query converts the amount from radians to degrees.
$round, $ceil, $floor (Rounding Numbers)
- These operators perform rounding operations:
- $round: Rounds to the nearest integer or to a specific decimal place.
- $ceil: Rounds a number upward to its nearest integer.
- $floor: Rounds a number downward to its nearest integer.
- Example:
// round example
db.orders.aggregate([{
$project: {
roundedAmount: { $round: ["$amount", 2] }
}
}])
// output
[
{ _id: 1, roundedAmount: 500 },
{ _id: 2, roundedAmount: 300 },
{ _id: 3, roundedAmount: 1200 },
{ _id: 4, roundedAmount: 1500 },
{ _id: 5, roundedAmount: 900 },
{ _id: 6, roundedAmount: 400 }
]
// ceil example
db.orders.aggregate([{
$project: { roundedAmount: { $ceil: "$amount" } }
}])
// output
[
{ _id: 1, roundedAmount: 500 },
{ _id: 2, roundedAmount: 300 },
{ _id: 3, roundedAmount: 1200 },
{ _id: 4, roundedAmount: 1500 },
{ _id: 5, roundedAmount: 900 },
{ _id: 6, roundedAmount: 400 }
]
// floor example
db.orders.aggregate([{
$project: { roundedAmount: { $floor: "$amount" } }
}])
// output
[
{ _id: 1, roundedAmount: 500 },
{ _id: 2, roundedAmount: 300 },
{ _id: 3, roundedAmount: 1200 },
{ _id: 4, roundedAmount: 1500 },
{ _id: 5, roundedAmount: 900 },
{ _id: 6, roundedAmount: 400 }
]