Aggregation Framework - Expression Reference

Overview

Expressions are used to calculate values from fields and other values.

Expressions are expressed in a prefix form as JSON field-value pairs. The "fieldname" is really the expression name. Multiple operands are then provided in an array: $add:[5, 6, 7]. Most of the operators below are binary, and expect a pair of operands to be presented as an array. Some, such as $add, $and, and $or, will accept more operands. A few are unary, and for these the operands may optionally be provided without array syntax, e.g., $not:[false] and $not:false are equivalent.

Composition

Expressions may be composed hierarchically. An expression may appear wherever a value may. Building on the addition above, we could have $add:[5,{ $divide:[12, 2] }, 7].

Field References

Within an expression, field names must be prefixed by a dollar sign ('$'); this must also be quoted. The dollar sign is required in order to be able to tell the difference between a field reference and a string literal. For example, two fields can be concatenated with a dot in between them as follows: $add:['$fieldOne', '.', '$fieldTwo'].

Table of Expressions

Comparison Operators

All comparison operators take either an array containing either a pair of strings or a pair of numbers and return a boolean (except $cmp which returns an int).

$cmp returns zero if the two values are equivalent, a negative number if the first is less than the second, and a positive number if the first is greater than the second.

$eq returns true if the two values are equivalent and false otherwise.

$gt returns true if the first value is greater than the second and false otherwise.

$gte returns true if the first value is greater than or equal to the second and false otherwise.

$lt returns true if the first value is less than the second and false otherwise.

$lte returns true if the first value is less than or equal to the second and false otherwise.

$ne returns true if the two values are not equivalent and false otherwise.

Boolean Operators

All boolean operators take booleans as their arguments and return booleans. Non-boolean values passed as input are converted to booleans as per BSON standards. So numeric values that are not zero treated as true, as a strings, dates, objects, arrays, etc. However, Null, undefined, and zero are treated as false.

$and takes an array and returns true if all of the values passed are true and false otherwise. Note: $and uses short-circuit logic, meaning it will stop evaluation when it encounters the first false expression.

$not returns the opposite of the boolean value it is passed (true if handed false and false if handed true).

$or takes an array and returns true if any of the values passed are true and false otherwise. Note: $or uses short-circuit logic, meaning it will stop evaluation when it encounters the first true expression.

Arithmetic Operators

$add takes an array of numbers and adds them together, returning their sum.

• If a string is present in the array, all the values will be appended to one another in the order they are passed and returned as a string.

• If a date is present in the array (and no strings are), all numeric values are treated as a number of days and added to the date, the resulting date is returned.

$divide takes an array containing a pair of numbers and returns the value of the first number divided by the second number.

$mod takes an array containing a pair of numbers and computes and returns the remainder of the first number divided by the second number.

$multiply takes an array of numbers and multiples them together, the resulting product is returned.

$subtract takes an array containing a pair of numbers and subtracts the second from the first, returning their difference.

• If a date is passed as the first entry in the array, the number will be treated as a number of days and removed from the date, the resulting date is returned.

String Operators

$add see Arithmetic Add

$strcasecmp takes in two strings and returns a Javascript long that is positive if the first string is "greater than" the second, is negative if the first string is "less than" the second, and 0 if they are the same string. Note: unlike $cmp the strings are capitalized before being compared, so $strcmp is case insensitive.

$substr takes a string and two numbers, the first number represents the number of characters to skip in the original string and the second is the number of characters to take from the original string. The resulting string is returned.

$toLower takes in a single string and returns the same string with all uppercase letters replace with their lowercase equivalents.

$toUpper takes in a single string and returns the same string with all lowercase letters replace with their uppercase equivalents.

Date Operators

All date operators, except $add, $subtract, and $isoDate, take a Date as their single argument and return a Javascript long.

$add see Arithmetic Add

$dayOfMonth returns the day of the month as a number between 1 and 31.

$dayOfWeek returns the day of the week as a number between 1 and 7.

$dayOfYear returns the day of the year as a number between 1 and 366.

$hour returns the hour between 0 and 23.

$isoDate converts a broken-down date into a date. Use as

$isoDate:{year: <year>, month: <month>, dayOfMonth: <dayOfMonth>,
          hour: <hour>, minute: <minute>, second: <second>}

All values are optional, with the following defaults:

These values can appear in any order, and may themselves be expressions.

$minute* returns the minute between 0 and 59.

month returns the month as a number between 1 and 12.

second returns the second between 0 and 59.

subtract see Arithmetic Subtract

week returns the week of the year as a number between 0 and 53. Weeks start on Sundays and the days before the first Sunday of the year are in week 0.

year returns the four digit year.

Other

$ifNull takes an array of two expressions. If the first expression evaluates to a non-false value, it is returned. Otherwise, the second expression's value is returned.

$cond takes an array of three expressions, the first of which should evaluate to a boolean value. If the first expression is true, $cond evaluates and returns the second expression. If the first expression is false, $cond evaluates and returns the third expression.