Square Root in MongoDB Aggregate Pipeline

Question

Is there a way to get the square root of a field in the MongoDB aggregate pipeline? I'm think of something like this:

db.collection.aggregate(
  { $group: _id: null, sum: { $sum: "$values" }}, 
  { $project: { answer: { $sqrt: "$sum" }}})

I know $sqrt doesn't exists, or any power operators, but is there a way to accomplish this within the aggregate pipeline? I know it could be done with a user defined function in map-reduce, but is it possible in aggregate pipeline?

Answer 1

As @AnandJayabalan pointed out, the $sqrt operator will be released with MongoDB version 3.2 and has the syntax:

{ $sqrt: <number> }

In your example, this would be

db.collection.aggregate([
  { $group: { _id: null, total: { $sum: "$values" }}}, 
  { $project: { answer: { $sqrt: "$total" }}}])

At the time of writing, for a workaround, the linked blog post by John Page about calculating square roots within the aggregation framework uses the arithmetic primitives to calculate square roots by Newton's method.

Just to explain how this algorithm works with an example, suppose you want to find the square root of a positive number N. Newton's method involves making an educated guess of a number A that, when squared, will be close to equaling N.

For example, if N = 121, you might guess A = 10, since A² = 100, which is a close guess, but you can do better than that.

The equation to use in this method is Newton's square root equation:

where

• N is a positive number of which you want to find the square root
• √ is the square root sign
• ≈ means "approximately equal to..."
• A is your educated guess

Newton's method allows you to repeat the estimation a number of times to approach an exact number, if necessary. Taking John Page's example N = 29 and you guess at A = 5, you can enter the values into the equation and the algorithm steps

a. start with the guess A = 5

b. divide N by the guess (29/5 = 5.9)

c. add that to the guess (5.9 + 5 = 10.9)

d. then divide that result by 2 (10.9/2 = 5.45)

e. set that as the new guess A = 5.45, and repeat, starting at b.

5.45 5.38555 5.38516

After 2 iterations the answer is 3.1623, which is approaching the exact value of the square root.

Now, using the aggregation framework (from John Page's blog post) and applying that to your example, the aggregation pipeline would be:

var groupPipeline = { $group: _id: null, total: { $sum: "$values" } },
    firstGuess = { 
        $project : { 
            n : "$total", r : { $literal : 5 } /* similar to step a) in the algorithm, the $literal operator sets r to the value 5 */
        } 
    },
    refineStep = { 
        $project : { 
            n: 1, r : { 
                $divide : [  /* step d) of the algorithm */
                    { 
                        $add : [ /* step c) of the algorithm */
                            { $divide : [ "$n", "$r"] },  /* step b) of the algorithm */
                            "$r" 
                        ]
                    },
                    2 
                ]
            }
        }
    };

/* Run the aggregation pipeline */
> db.collection.aggregate(groupPipeline, firstGuess, refineStep, refineStep, refineStep)

> { "_id" : ObjectId("538062103439ddd3764ff791"), "n" : 29, "r" : 5.385164807134505 }

Answer 2

Mongo 3.2 will have a native sqrt method for the aggregation framework.

If the argument resolves to a value of null or refers to a field that is missing, $sqrt returns null. If the argument resolves to NaN, $sqrt returns NaN.

$sqrt errors on negative numbers.

Answer 3

This article is very old, however we needed to write our own implementation of a cubic root in order to replace $pow(x,1/3). The reason is that we are using AWS DocumentDB to not have to host and manage our own MongoDB databases. However AWS Document DB does not support the operators $sqrt or $pow in neither of their versions. I also was not able to figure out why they don't support it.

We implemented two versions, using Netwon's methods and Halley's method (because the latter converges faster). Technically they are also based on the answer of @chridram using the aggregation framework. You can copy the exact commands in order to reproduce it:

Start up a docker container and execute the mongosh:

docker run -d -p 27017:27017 --name test-mongo mongo:latest
docker exec -it test-mongo mongosh

Netwon's method:

use test
db.roots.insert({n:29})
firstguess = { $project : { n : 1, r : { $literal : 1}}}
refine = { $project : { n: 1, r : { $multiply: [{$divide: [1, 3]},{$add:[{$divide: ["$n", {$multiply: ["$r","$r"]}]},{$multiply:[2, "$r"]}]}]
}}}
db.roots.aggregate([firstguess,refine,refine,refine,refine,refine,refine,refine,refine])

Halley's method:

use test
db.roots.insert({n:29})
firstguess = { $project : { n : 1, r : { $literal : 1}}}
refine = { $project : { n: 1, r :
{
    $multiply: [
        "$r",
        {
            $divide: [
                {
                    $add: [
                        {$multiply: ["$r", "$r", "$r"]},
                        {$multiply: [2, "$n"]}
                    ]
                },
                {
                    $add: [
                        {$multiply: [2, "$r", "$r", "$r"]},
                        "$n"
                    ]
                }
            ]
        }
    ]
}}}
db.roots.aggregate([firstguess,refine,refine,refine,refine,refine,refine,refine,refine])

See https://en.wikipedia.org/wiki/Cube_root for more details on the actual formulas.

A further improvement could be to use $addFields instead of $project. It is easier to read. See also https://www.mongodb.com/docs/manual/reference/operator/aggregation/addFields/