# Aggregation Operators

Aggregation operators can be used to create expressions in the aggregation pipeline. The operators work like functions. They typically take either a single argument like this:

{[operator]: [argument]}

Or they take an array of arguments like this:

{[operator]: [[argument1], [argument2] ...]}

Type | Description |

Boolean Operators | Boolean operators evaluate their expression and return a boolean result. |

Comparison Operators | Comparison operators compare two arguments and return a boolean result or a number. |

Arithmetic Operators | Arithmetic operators perform calculations on numbers and dates |

String Operators | String operators perform operations on string arguments. |

Array Operators | Array operators perform operations on array arguments. |

Set Operators | Set operators work on arrays but treats them as sets, ignoring duplicate entries and order and return a set as a result. |

Literal Operators | Literal operators return a value or expression without parsing or evaluating the expression. |

Date Operators | Date operators perform operations on date arguments. |

Conditional Operators | Conditional operators evaluate expressions and return a value depending on the result of the condition. |

Accumulator Operators | Accumulator operators take a single expression as input and evaluate that expression once for each document in the result set. |

Operator | Description | Example |

$and | Evaluates to true if all conditions in the passed in array evaluate to true. | {“$and”: [“c_active”, “c_fav”]} |

$or | Evaluates to true if any condition in the passed in array evaluate to true. | {“$or”: [“c_active”, “c_fav”]} |

$not | Evaluates to true if the property or expression evaluates to false. | {“$not”: “c_active”} |

Operator | Description | Example |

$cmp | Returns 0 if the two passed in array values are equivalent, 1 if the first value is greater than the second, -1 if the second value is greater than the first. | {“$cmp”: [“name.first”, {“$string”: “Jamie”}]} |

$gt | Selects documents where the value of the property is greater than the passed in value. | {“c_bpm”: {“$gt”: 70}} |

$gte | Selects documents where the value of the property is greater than or equal to the passed in value. | {“c_bpm”: {“$gte”: 70}} |

$lt | Selects documents where the value of the property is lesser than the passed in value. | {“c_bpm”: {“$lt”: 70}} |

$lte | Selects documents where the value of the property is lesser than or equal to the passed in value. | {“c_bpm”: {“$lte”: 70}} |

$ne | Returns true if the first property or expression in the passed in not equivalent to the second. | {“$ne”: [“c_bpm”, {“$integer”: 100}]} |

Operator | Description | Example |

$trunc | Truncates a number down to an integer. | { "$trunc": "c_value" } } |

$sqrt | Takes the square root of an aggregated number. | {"$sqrt": {"$add": "c_value"}} |

$ln | Takes the log of a number. | {"$ln": "c_number"} |

$floor | Returns the largest integer less than or equal to the number | {"$floor": "c_number"} |

$exp | Takes the e^n of a number | {"$exp": "c_number"} |

$ceil | Returns the smallest integer greater than or equal to the specified number. | {"$ceil": "c_number"} |

$abs | Takes the absolute value of a number. | {"$abs": "c_number"} |

$add | Adds two or more array properties or expressions. In addition to numbers, a single Date value may be included in the array. | {“$add”: [“created”, “c_msOffset”]} |

$multiply | Multiplies two or more number properties or expressions in the passed in array. | {“$multiply”: [“c_bpm”, “c_scalar”, {“$number”: 0.99}]} |

$subtract | Subtracts the second element from the first in the passed in array. In addition to number properties, numbers and dates maybe be subtracted from a date value. | {“$subtract”: [“created”, “updated”]} |

$divide | Divides the second element by the first in the passed in array. | {“$divide”: [“c_bpm”, {“$number”: 0.9}]} |

$mod | Divides the second element by the first in the passed in array, and returns the remainder. | {“$divide”: [“c_bpm”, {“$number”: 20}]} |

$pow | Raises the first number to the power of the second number. | {“$pow”: [“c_heartrate”, {“$number”: 2}]} |

$log | Takes the log of the first number in the second number's base. | {“$log”: [100, 10] } // result: 2 |

Operator | Description | Example |

$concat | Concatenates two or more string properties or expressions in the passed in array. | {“$concat”: [“name.first”, {“$string”: “ ”}, “name.last”]} |

$substr | Returns a portion of a string. Takes an array of three elements: source string, integer start index and integer length. The integer values can be literals, properties, or expressions. | {“$substr”: [“c_desc”, 0, 20]} |

$toLower | Returns a lowercase version of the string argument. | {“$toLower”: “c_title”} |

$toUpper | Returns an uppercase version of the string argument. | {“$toUpper”: “c_title”} |

$strcasecmp | Performs a case-insensitive string comparison. Returns 0 if the two passed in array values are equivalent, 1 if the first value is greater than the second, -1 if the second value is greater than the first. | {“$strcasecmp”: [“name.first”, {“$string”: “J”}]} |

Operator | Description | Example |

$size | Returns the size of the array argument. The argument must resolve to an array | {“$size”: “body”} |

$slice | Returns the subset of an array. Param 1: The master array Param 2 (optional): the position to start evaluating the array from Param 3: the number of elements from param 2 to return | { $slice: [ [ 1, 5, 7, 9 ], 1, 2 ] } // returns [5, 7] |

$isArray | Returns true if the operand is an array | {"$isArray": [1,2,3]} // returns true |

$concatArrays | Returns an array which contains all the arrays concatenated together. | {"$concatArrays": [ [ "medable", "is"], [ "great" ] ] } //returns ["medable", "is", "great"] |

$arrayElemAt | Returns the element in the array at a specified index. | { $arrayElemAt: [ [ "purple", "orange", "yellow" ], 1 ] } //returns "orange" |

Operator | Description | Example |

$setEquals | Evaluates to true if the passed in array properties or expressions contain the same distinct elements | {“$setEquals”: [ {“$array”: [“cpg”, “mvc”, “kvm”]}, “c_requested” ] }} |

$setIntersection | Returns the intersection between 2 or more input arrays. | {“$setIntersection”:[“c_available”, “c_reserved”]} |

$setUnion | Combines 2 or more input arrays. | {“$setUnion”:[{“$array”: [“000000000000000000000007”]}, “roles”]}}} |

$setDifference | Returns the difference between 2 or more input arrays. | {“$setDifference”:[{“$array”: [“000000000000000000000007”]}, “roles”]}}} |

$setIsSubset | Returns true if the first array is a subset of the second. | {“$setIsSubset”: [“c_some”, “c_all”]} |

$anyElementTrue | Returns true if any property or expression in the passed in array evaluates to true. | {“$anyElementTrue”: [“c_bool”, “c_fav”]} |

$allElementsTrue | Returns true if all properties or expressions in the passed in array evaluate to true. | {“$allElementsTrue”: [“c_bool”, “c_fav”]} |

Operator | Description | Example |

$literal | Allows the insertion of any literal value. This is used mainly when you'd like to use a value that would otherwise be evaluated as an expression. | pricesEqual: { $eq: [ "$cashValue", { $literal: "$10" } ] } |

$string | Allows the insertion of a string literal. The value must be a string or an expression that evaluates to a string. | {“$concat”: [“c_pants”, {“$string”: “c_pants”}] } -> “clown c_pants” |

$number | Allows the insertion of a number value as a literal where a property name would otherwise be expected. The value must be a number or an expression that evaluates to a number. | {“$add”: [“c_scalar”, {“$number”: 1.23}]} |

$integer | Allows the insertion of an integer value as a literal where a property name would otherwise be expected. The value must be an integer or an expression that evaluates to an integer. | {“$add”: [“c_ints”, {“$integer”: 10}]} |

$boolean | Allows the insertion of a boolean value as a literal where a property name would otherwise be expected. The value must be a boolean or an expression that evaluates to a boolean. | {“alwaysFirstName”: {“$first”: {“$cond”: [{“$boolean”: true}, “name.first”, “name.last”]}}} |

$date | Allows the insertion of a date value as a literal where a property name would otherwise be expected. The value must be a date or an expression that evaluates to a date. | {“offsetDate”: {“$add”: [{“$date”: “2015-06-07T14:48:00.000Z”}, “c_offset”]}} |

$objectId | Allows the insertion of an objectId value as a literal where a property name would otherwise be expected. The value must be an objectId or an expression that evaluates to an objectId. | {“$cmp”: [{“$objectId”: “561614700000000000000000”}, “_id”]} |

$array | Allows the insertion of an array value as a literal where a property name would otherwise be expected. The value must be an array or an expression that evaluates to an array. | {“$setUnion”:[{“$array”: [“000000000000000000000007”]}, “roles”]}}} |

$object | Allows the insertion of an object value as a literal where a property name would otherwise be expected. The value must be an object or an expression that evaluates to an object. | {“$eq”: [ {“$object”: { "street": "Easy St" }}, “c_address”]} |

Operator | Description | Example |

$dayOfWeek | Returns the day of the week for the passed in date property or expression as a number from 1 (Sunday) to 7 (Saturday). | {"$dayOfWeek": "created"} |

$dayOfMonth | Returns the day of the month for the passed in date property or expression as a number from 1 to 31 | {"$dayOfMonth": "created"} |

$dayOfYear | Returns the day of the year for the passed in date property or expression as a number from 1 to 366 | {"$dayOfYear": "created"} |

$year | Returns the year for the passed in date property or expression. | {"$year": "created"} |

$month | Returns the month for the passed in date property or expression. | {"$month": "created"} |

$week | Returns the week of the year for the passed in date property or expression as a number between 0 (the partial week that precedes the first Sunday of the year) and 53 (in a leap year). | {"$week": "created"} |

$hour | Returns the hour for the passed in date property or expression as a number between 0 and 23. | {"$hour": "created"} |

$minute | Returns the minute for the passed in date property or expression as a number between 0 and 59. | {"$minute": "created"} |

$second | Returns the second for the passed in date property or expression as a number between 0 and 60 (for a leap second). | {"$second": "created"} |

$millisecond | Returns the millisecond for the passed in date property or expression as a number between 0 and 999. | {"$millisecond": "created"} |

$dateToString | Converts a date object to a string in a specified format, and expects an array of [, date_expression] | { "$dateToString": ["%Y-%m-%d", "created"] } |

Date Format Specifiers

Specifiers | Description | Possible Values |

%d | Day of Month (2 digits, zero padded) | 01-31 |

%G | Year in ISO 8601 format | 0000-9999 |

%H | Hour (2 digits, zero padded, 24-hour clock) | 00-23 |

%j | Day of year (3 digits, zero padded) | 001-366 |

%L | Millisecond (3 digits, zero padded) | 000-999 |

%m | Month (2 digits, zero padded) | 01-12 |

%M | Minute (2 digits, zero padded) | 00-59 |

%S | Second (2 digits, zero padded) | 00-60 |

%u | Day of week number in ISO 8601 format (1-Monday, 7-Sunday) | 1-7 |

%U | Week of year (2 digits, zero padded) | 00-53 |

%V | Week of Year in ISO 8601 format | 1-53 |

%w | Day of week (1-Sunday, 7-Saturday) | 1-7 |

%Y | Year (4 digits, zero padded) | 0000-9999 |

%% | Percent Character as a Literal | % |

Operator | Description | Example |

$cond | Takes an array of 3 expressions. If the first expression evaluates to true, the second expression value is returned. Otherwise, the value of the third expression is returned. | {“$cond”: [“c_active”, {“$string”: “active”}, {“$string”: “inactive”}]} |

$ifNull | Takes an array of 2 expressions. Returns the value of the first expression unless it evaluates to null, in which case the value of the second expression is returned. | {“$ifNull”: [“c_offset”, {“$integer”: 42}]} |

Operator | Description | Example |

$count | Performs a count of a grouped documents or, if the property is an array, a count of the number of elements. | {“numInGroup”: {“$count”: “_id”}} |

$sum | Calculates the sum of a number property or expression in the group. | {“sumOfDays”: {“$sum”: {“$dayOfMonth”: “created”}}} |

$avg | Calculates the average of a number property or expression in the group. | {“averageHeartRate”: {“$avg”: “c_bpm”}} |

$first | Returns the first value from the group. The result depends on the sort field(s). | {“oldest”: {“$first”: “created”}} |

$last | Returns the last value from the group. The result depends on the sort field(s). | {“latest”: {“$last”: “created”}} |

$min | Returns the smallest value from the group. | {“smallest”: {“$min”: “c_size”}} |

$max | Returns the largest value from the group. | {“largestArray”: {“$max: {”$size": “c_arr”}}} |

$pushAll | Returns the array of pushed values, with NULL included in the array where there are missing values | {"combinedArray": {"$pushAll": "c_value"}} |

$push | Returns the array of pushed values | {"combinedArray": {"$push": "c_value"}} |

$addToSet | Returns array of unique pushed values | {"uniqueArray": {"$addToSet": "c_value"}} |

$stdDevPop | Returns the population standard deviation of input values | {"stdDeviation": { "$stdDevPop": "c_heartrate"}} |

$stdDevSamp | Returns the sample standard deviation of input values | {"stdDeviation": { "$stdDevSamp": "c_heartrate"}} |

Last modified 2yr ago