MongoDB $sqrt Operator
What is the $sqrt operator in MongoDB?
MongoDB provides a variety of arithmetic expression operators. The $sqrt operator is one of those arithmetic expression operators. The $sqrt operator is used to calculate the square root of a positive number, and it returns the output as a double. This operator is also used in the aggregation pipeline stages.
Syntax of the $sqrt operator:
The < number > can be any valid number as long as it resolves for a non-negative number.
Important point:
- If the number refers to a missing field or null, the $sqrt operator returns the null.
- If the number is NaN, the $sqrt operator returns NaN.
S.No | Example | Output |
---|---|---|
1. | { $sqrt: 4 } | 2 |
2. | { $sqrt: 15 } | 3.872983346207417 |
3. | { $sqrt: -2 } | Error |
4. | { $sqrt: null} | Null |
Examples:
Suppose we have a collection of items with the following documents.
>db.items.find().pretty() { { "_id" : 1, "item_name" : "bat", "quantity" : 4 } { "_id" : 2, "item_name" : "ball", "quantity" : null } { "_id" : 3, "item_name" : "box", "details" : { "length" : 20, "width" : 25 } } { "_id" : 4, "item_name" : "ball", "quantity" : null } { "_id" : 5, "item_name" : "bat", "quantity" : 20 } { "_id" : 6, "item_name" : "toy", "quantity" : -10 } { "_id" : 7, "item_name" : "bat", "quantity" : 75 } { "_id" : 8, "item_name" : "bat", "quantity" : 45 } }
Example 1: Using $sqrt operator to finding the square root of any field
In this example, we are using the $sqrt operator to find the square root of the “quantity” field.
Output:
{ "_id" : 1, "item_name" : "bat", "quantity" : 4, "result" : 2 } { "_id" : 5, "item_name" : "bat", "quantity" : 20, "result" : 4.472135954999579 } { "_id" : 7, "item_name" : "bat", "quantity" : 75, "result" : 8.660254037844386 } { "_id" : 8, "item_name" : "bat", "quantity" : 45, "result" : 6.708203932499369 }
Example 2: Negative values of the field
The $sqrt operator only supports positive numbers that must be greater than or equal to 0. If the value of the argument is negative, it gives an error. Let’s apply the $sqrt operator against the toy documents.
Output:
uncaught exception: Error: command failed: { "ok": 0, "errmsg": "$sqrt's argument must be greater than or equal to 0", "code": 28714, "codeName": "Location28714" } : aggregate failed: [email protected]/mongo/shell/utils.js : 25 : 13 [email protected]/mongo/shell/assert.js : 18 : 14 [email protected]/mongo/shell/assert.js : 618 : 17 [email protected]/mongo/shell/assert.js : 708 : 16 [email protected]/mongo/shell/db.js : 266 :5 [email protected]/mongo/shell/collection.js : 1046 : 12 @(shell) : 1 : 1
Example 3: Null values of the field
If the field value is null, the $sqrt operator returns the null. Let’s apply the $sqrt operator against the ball documents.
Output:
{ "_id" : 2, "item_name" : "ball", "quantity" : null, "result" : null } { "_id" : 4, "item_name" : "ball", "quantity" : null, "result" : null }
Example 4: Non-existent Fields
If the argument refers to a missing field, the $sqrt operator returns the null. In this example, we are using the $sqrt operator to find the square root of the “price” field.
Output:
{ "_id" : 5, "item_name" : "bat", "quantity" : 20, "result" : null }