> For the complete documentation index, see [llms.txt](https://docs.medable.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.medable.com/cortex-api/aggregating/aggregation-operators.md). # 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] ...]} ``` ## Aggregation Operators Types | 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. | ## Boolean Operators | 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”} | ## Comparison Operators | 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}]} | ## Arithmetic Operators | 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 | ## String Operators | 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”}]} | ## Array Operators | 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" | ## Set Operators | 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”]} | ## Literal Operators | 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”]} | ## Date Operators | 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 | % | ## Conditional Operators | 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}]} | ## Accumulator Operators | 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"}} |