Square root in MongoDB aggregate pipe

Question

Is there a way to get the square root of a field in a MongoDB aggregate aggregate? I am thinking of something like this:

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

I know that $ sqrt does not exist or any power operators, but is there a way to do this within the aggregate pipeline? I know that this can be done using a user-defined function in map-reduce, but is this possible in the aggregate pipeline?

Answer 1

As @AnandJayabalan noted, the $sqrt statement will be released with MongoDB 3.2 and has the syntax:

 { $sqrt: <number> } 

In your example, it will be

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

At the time of this writing, John Page's related blog post about calculating square roots as part of aggregation uses arithmetic primitives to calculate Newton’s square roots.

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 obtaining a reasonable assumption about the number A , which, when squared, will be close to the equation N

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

The equation used in this method is the equation of Newton's square root:

Where

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

Newton's method allows you to repeat the assessment several times to get closer to the exact number, if necessary. Taking the example of John Page N = 29 , and you guess that A = 5 , you can enter the values ​​in the equation and steps of the algorithm

a. start with assumption A = 5

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

c. add this to the assumption (5.9 + 5 = 10.9)

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

e. set this as the new assumption A = 5.45 and repeat from b.

5.45 5.38555 5.38516

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

Now, using the aggregation structure (from the John Page blog post) and applying this to your example, the aggregation pipeline will look like this:

 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 its own sqrt method for the aggregation structure .

If the argument resolves to 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.