Aggregation Arithmetic Expression Operators in MongoDB with examples

Subscribe to my newsletter and never miss my upcoming articles

In this post, I will discuss some examples on $group and various accumulators (operators used along with $group) like $push, $avg, $sum, $min, $max and $addToSet.

In previous post, I had made a reference to expressions, right? Now, what can constitute an expression? One of the important things is the 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 a date.

It takes an 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.png

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

Solution

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

add-example-code-and-output-in-MongoDB-Aggregate.png

$multiply

$multiply multiplies the numbers passes as an argument

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

Example

Suppose you have a 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 the net amount in the system. For that, we need to multiply the price and quantity of each record and then add the amount of all the documents.

multiply-arithmetic-example.png

$pow

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

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

Here is a simple example. Consider a cardboard factory producing cardboard boxes of the shape of a cube. Given the following data about the 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.png

$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 the volume will also be in decimals. We may need to round off value for practical reasons. Let’s get rounded off volume for those cardboard boxes.

round-example-in-MongoDB-Aggregate-Pipeline.png

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.

Here is a 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.png

$floor

$floor calculates the 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
}

Let's suppose we want to find the floor of property x in each of the documents and list it. Here’s code floor-in-MongoDB-aggregate-example.png

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 x side

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

Here is the code divide-example-in-aggregate.png

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 the $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.png

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

You can Tweet me at @MohitSehgl. Happy Coding 🙂

Originally published at Appsyoda.com

No Comments Yet