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:

1

{[operator]: [argument]}

Copied!

Or they take an array of arguments like this:

1

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

Copied!

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"}}

Last modified 4mo ago