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 }