Fork me on GitHub

Functions

Aggregate Functions

  • COUNT(array) - Returns the number of elements in array

  • MIN(array) - Returns the lowest value in array

  • MAX(array) - Returns the highest value in array

  • FIRST(array) - Returns the first element in array

  • LAST(array) - Returns the last element in array

  • AVG(array) - Returns the average value of the numerical values in array (ignores non-numerical values)

  • SUM(array) - Returns the sum of the numerical values in array (ignores non-numerical values)

  • ANY(array) - Returns true if array has any elements

DataType Functions

  • MINVALUE() - Returns the singleton instance of MinValue

  • MAXVALUE() - Returns the singleton instance of MaxValue

  • OBJECTID() - Returns a new instance of ObjectId

  • GUID() - Returns a new instance of Guid

  • NOW() - Returns the current timestamp in local time

  • NOW_UTC() - Returns the current timestamp in UTC

  • TODAY() - Returns the current date at 00h00min00s

  • INT32(value) - Returns value converted to Int32, or null if not possible

  • INT64(value) - Returns value converted to Int64, or null if not possible

  • DOUBLE(value, culture) - Returns value converted to Double according to the specified culture, or null if not possible

  • DECIMAL(value, culture) - Returns value converted to Decimal according to the specified culture, or null if not possible

  • STRING(value) - Returns the string representation of value

  • BINARY(value) - Returns value converted to BsonBinary, or null if not possible

  • OBJECTID(value) - Returns value converted to ObjectId, or null if not possible

  • GUID(value) - Returns value converted to Guid, or null if not possible

  • BOOLEAN(value) - Returns value converted to Boolean, or null if not possible

  • DATETIME(value, culture) - Returns value converted to DateTime in local time according to the specified culture, or null if not possible

  • DATETIME_UTC(value, culture) - Returns value converted to DateTime in UTC according to the specified culture, or null if not possible

  • DATETIME(year, month, day) - Returns a new DateTime at 00h00min00s in local time based on the provided year, month and day

  • DATETIME_UTC(year, month, day) - Returns a new DateTime at 00h00min00s in UTC based on the provided year, month and day

  • IS_MINVALUE(value) - Returns true if value is MinValue, false otherwise

  • IS_MAXVALUE(value) - Returns true if value is MaxValue, false otherwise

  • IS_NULL(value) - Returns true if value is null, false otherwise

  • IS_INT32(value) - Returns true if value is Int32, false otherwise

  • IS_INT64(value) - Returns true if value is Int64, false otherwise

  • IS_DOUBLE(value) - Returns true if value is Double, false otherwise

  • IS_DECIMAL(value) - Returns true if value is Decimal, false otherwise

  • IS_NUMBER(value) - Returns true if value is of a numerical type, false otherwise

  • IS_STRING(value) - Returns true if value is String, false otherwise

  • IS_DOCUMENT(value) - Returns true if value is BsonDocument, false otherwise

  • IS_arrayAY(value) - Returns true if value is Bsonarrayay, false otherwise

  • IS_BINARY(value) - Returns true if value is BsonBinary, false otherwise

  • IS_OBJECTID(value) - Returns true if value is ObjectId, false otherwise

  • IS_GUID(value) - Returns true if value is Guid, false otherwise

  • IS_BOOLEAN(value) - Returns true if value is Boolean, false otherwise

  • IS_DATETIME(value) - Returns true if value is DateTime, false otherwise

Date Functions

  • YEAR(value) - Returns the year of value, or null if it is not a DateTime

  • MONTH(value) - Returns the month of value, or null if it is not a DateTime

  • DAY(value) - Returns the day of value, or null if it is not a DateTime

  • HOUR(value) - Returns the hour of value, or null if it is not a DateTime

  • MINUTE(value) - Returns the minutes of value, or null if it is not a DateTime

  • SECOND(value) - Returns the seconds of value, or null if it is not a DateTime

  • DATEADD(dateInterval, amount, value)

    • dateInterval is one of the following: y|year, M|month, d|day, h|hour, m|minute, s|second
    • amount is the amount of units defined by dateInterval to be added to value
  • DATEDIFF(dateInterval, start, end)

    • dateInterval is one of the following: y|year, M|month, d|day, h|hour, m|minute, s|second
    • start and end are dates
    • The function returns the difference between the dates in units defines by dateInterval
  • TO_LOCAL(date) - Returns date converted to local time, or null if is not a DateTime

  • TO_UTC(date) - Returns date converted to UTC, or null if is not a DateTime

Math Functions

  • ABS(value) - Returns the absolute value of value, or null if it is not a numerical value

  • ROUND(value, digits) - Returns value rounded to digits of precision, or null if it is not a numerical value

  • POW(x, y) - Returns x to the power of y (always as a Double), or null if either of them is not a numerical value

String Functions

  • LOWER(value) - Returns value in lower case, or null if it is not a String

  • UPPER(value) - Returns value in upper case, or null if it is not a String

  • LTRIM(value) - Returns a new string with leading whitespaces removed, or null if it is not a String

  • RTRIM(value) - Returns a new string with trailing whitespaces removed, or null if it is not a String

  • TRIM(value) - Returns a new string with leading and trailing whitespaces removed, or null if it is not a String

  • INDEXOF(value, match) - Returns the zero-based index of the first occurrence of match in value

  • INDEXOF(value, match, startIndex)- Returns the zero-based index of the first occurrence of match in value. The search starts in startIndex

  • SUBSTRING(value, startIndex) - Returns the substring of value from startIndex to the end

  • SUBSTRING(value, startIndex, length) - Returns the substring of value starting from startIndex and with length specified by length

  • SUBSTRING(value, oldValue, newValue) - Returns a new string with occurrences of oldValue replaced by newValue

  • LPAD(value, totalWidth, paddingChar) - Returns a new string left-padded to totalWidth length with paddingChar

  • RPAD(value, totalWidth, paddingChar) - Returns a new string right-padded to totalWidth length with paddingChar

  • SPLIT(value, separator) - Returns an arrayay containing the substrings of value split by separator

  • FORMAT(value, format) - Returns the string representation of value with the provided format

  • JOIN(array) - Takes an array of string and returns those strings joined by ,

  • JOIN(array, separator) - Takes an array of string and returns those strings joined by separator

Misc Functions

  • JSON(value) - Takes a string representation of a JSON and returns a parsed BsonValue

  • EXTEND(source, extend) - Merges two documents into one, copying their attributes.

  • CONCAT(array1, array2) - Returns a new array containt the concatenation of array1 and array2

  • KEYS(document) - Returns an array containing every key in document

  • OID_CREATIONTIME(objectId) - Returns the creation time of objectId

  • IIF(predicate, ifTrue, ifFalse) - Returns ifTrue if predicate evaluates to true, false otherwise

  • COALESCE(left, right) - Returns left if it is not null, right otherwise

  • LENGTH(value) - Returns the lenght of value (if value is String, Binary, Array or Document)

  • TOP(values, num) - Returns the first num elements from values

  • UNION(array1, array2) - Returns the set union between array1 and array2

  • EXCEPT(array1, array2) - Returns the set difference between array1 and array2

  • DISTINCT(array) - Returns the distinct elements from array

  • RANDOM() - Returns a random Int32

  • RANDOM(min, max) - Returns a random Int32 between min and max