Aggregation Arithmetic Expression Operators in MongoDB with examples

Aggregation Arithmetic Expression Operators in MongoDB with examples

In this post I will discuss about basics of arithmetic expression operators with some examples and illustration to help you understand easily.

In previous post, I had made a reference to expressions, right? Now what can constitute an expression? One of the important thing is operators. Within those operators I am going to talk about arithmetic expression operators.

What are arithmetic expression operators?

Arithmetic expression operators allow us to perform mathematical operations in expressions in Aggregation Pipeline.

Some heavy words there. No worries, I’ll explain with examples

We will take on some important arithmetic expressions

$add

$add performs additions on numbers on numbers or numbers on date.

It takes array as input, each element can be some field in the input, constant, or even expression.

Example

Consider student data in 3 subjects in this format.

sample data for arithmetic operators mongodb
sample data for arithmetic operators mongodb

Now what if we wanted to list all the student marks along with sum.

Solution

We need all the fields and we want additional field with sum of marks scored in 3 subjects. We would use $addFields stage. Here is the solution.

$add example code and output in MongoDB Aggregate
$add example code and output in MongoDB Aggregate

$multiply

$multiply multiplies the numbers passes as an argument

Input is array, and each element can be expression (which should resolve to number), numerical fields in input or constants.

Example

Suppose you have sales database. There is a sales table where there data in the following format

{
	"_id" : ObjectId("5f40e9c4c76c2fb35707735e"),
	"item" : "Macbook Pro",
	"price" : 119000,
	"currency" : "INR",
	"quantity" : 2
}

Now we want net amount in the system. For that we need to multiply price and quantity of each record and then add amount of all the documents.

$multiply arithmetic example
$multiply arithmetic example

$pow

This is straight out of mathematics textbook. $pow stands for “raise to power”.

If e1 and e2 are 2 parameters passed to $pow, then resulting value of the document would be e1e2

Here is a simple example. Consider a cardboard factory producing cardboard boxes of the shape of cube. Given the following data about kinds of cube boxes it produces.

// sample record representing the cube box
{
  "itemCode":23201,
  "sideLengthInInch":23,
  "price":35
}

What if we want to list all the items with volume (in inches3).

Here is the code

$pow example in aggregate pipeline
$pow example in aggregate pipeline

$round

$round simply rounds of the numerical input to a specified decimal place.

Let’s continue the box example. If there is some box whose side is decimals, then volume will also be in decimals. We may need rounded off value for practical reasons. Let’s get rounded off volume for those cardboard boxes.

$round example in MongoDB Aggregate Pipeline
$round example in MongoDB Aggregate Pipeline

Notice the difference between volume and roundedOffVolume.

Also I had divided the pipeline into 2 stages for simplicity.

$sqrt

$sqrt finds out and prints the square root of the numerical expression.

e.g. $sqrt(25)= 5 and $sqrt(64) = 8.

Let’s take code example.

Imagine we have 2-D graph data with each record representing a straight line with 2 points (x,y). What if we want to list the distance between the 2 points for some records. Please refer to this formula.

c = square root of [(xA-xB)^2+(yA-yB)^2]
Formula for distance between 2 points.

Sample document.

{
    "_id" : ObjectId("5f411577c76c2fb3570775f3"),
    "point1" : {
        "x" : 1,
        "y" : 10
    },
    "point2" : {
        "x" : 4,
        "y" : 6
    }
}

Here is how you calculate and list the distance using $sqrt.

$sqrt example in aggregate pipeline MongoDB
$sqrt example in aggregate pipeline MongoDB

$floor

$floor calculates largest integer less than or equal to the value of expression passes as input.

For example, consider this collection with simple sample values like this.

/* 1 */
{
    "_id" : ObjectId("5f412311c76c2fb357077819"),
    "x" : 1.31
}

/* 2 */
{
    "_id" : ObjectId("5f412322c76c2fb357077829"),
    "x" : -99.103
}

/* 3 */
{
    "_id" : ObjectId("5f41232fc76c2fb35707782d"),
    "x" : 0.5
}

/* 4 */
{
    "_id" : ObjectId("5f412343c76c2fb357077835"),
    "x" : 48.89
}

Lets suppose we want to find floor of property x in each of the documents and list it. Here’s code

$floor in MongoDB aggregate example
$floor in MongoDB aggregate example

I hope you get the idea of how to use it, (without any practical example 🙁 )

$divide

As the name already suggests, it divides the 2 numbers (can be expressions themselves).

Let’s jump straight to example.

Imagine a database of shapes that has a collection called shapes. What if we wanted to display the area for each of them.

Here is a sample docs

/* 1 */
{
    "_id" : ObjectId("5f412620c76c2fb3570778df"),
    "type" : "rectangle",
    "length" : 5,
    "breadth" : 4
}

/* 2 */
{
    "_id" : ObjectId("5f412638c76c2fb3570778e6"),
    "type" : "square",
    "side" : 5
}

/* 3 */
{
    "_id" : ObjectId("5f412650c76c2fb3570778ef"),
    "type" : "circle",
    "diameter" : 10
}

Remember, Area of rectangle = length * breadth

Area of Square = side * side

Area of Circle = (3.14 * diameter * diameter) /4

Here is the code

$divide example in aggregate
$divide example in aggregate

Explanation

First, we had 3 kinds of shapes in the DB as you could see from the sample data i.e. rectangle, square, and circle.

I applied $switch operator where we can handle multiple cases(three in our case) easily.

$switch has an array of branches. Each branch has where there is a condition to match the shape. If that matches, then expression inside the “then” gets assigned. I simply applied the formulas in each of the cases inside the “then” section.

Here is how the response looks like.

example response - area of shapes
example response – area of shapes

Conclusion

There are some more arithmetical expression operators like $abs (absolute value), $ceil (ceiling function in maths), $subtract, $exp, $ln (log natural), $log10 (log base 10), $mod (modulo i.e. remainder after division). Let me know if you need help on any of these in comments section

You can also Tweet me at @MohitSehgl. Happy Coding 🙂

Leave a Comment

Aggregation Arithmetic Expression Operators in MongoDB with examples

by Mohit Sehgal time to read: 4 min
0