ARRAY_AGG(column)

Returns array of the non-MISSING values in the group, including NULL values.

• column: Any column expression.

ARRAY_APPEND(column, value)

Returns new array with value appended.

• column: Any column expression.
• value: The value to be appended to the array.

ARRAY_CONCAT(column1, column2)

Returns new array with the concatenation of the input arrays.

• column1: Any column expression.
• column2: Any column expression.

ARRAY_DISTINCT(column)

Returns new array with distinct elements of input array.

• column: Any column expression.

ARRAY_IFNULL(column)

Returns the first non-NULL value in the array, or NULL.

• column: Any column expression.

ARRAY_PREPEND(column, value)

Returns new array with value pre-pended.

• column: Any column expression.
• value: The value to be pre-pended to the array.

ARRAY_PUT(column, value)

Returns new array with value appended, if value is not already present, otherwise returns the unmodified input array.

• column: Any column expression.
• value: The value to append to the array.

ARRAY_REMOVE(column, value)

Returns new array with all occurrences of value removed.

• column: Any column expression.
• value: The value to be removed from the array.

ARRAY_REPLACE(column, value1, value2 [, integer_n])

Returns new array with all occurrences of value removed.

• column: Any column expression.
• value1: The value to be replaced by value2.
• value2: The value to replace value1.
• integer_n: The maximum number of replacements to be performed.

ARRAY_REVERSE(column)

Returns new array with all elements in reverse order.

• column: Any column expression.

ARRAY_SORT(column)

Returns new array with elements sorted in N1QL collation order.

• column: Any column expression.

DECODE_JSON(column)

Unmarshals the JSON-encoded string into a N1QL value. The empty string is MISSING.

• column: Any column expression.

ENCODE_JSON(column)

Marshals the N1QL value into a JSON-encoded string. MISSING becomes the empty string.

• column: Any column expression.

ENCODED_SIZE(column)

Number of bytes in an uncompressed JSON encoding of the value. The exact size is implementation-dependent. Always returns an integer, and never MISSING or NULL. Returns 0 for MISSING.

• column: Any column expression.

POLY_LENGTH(column)

Returns length of the value after evaluating the expression. The exact meaning of length depends on the type of the value: MISSING: MISSING; NULL: NULL; String: The length of the string.; Array: The number of elements in the array.; Object: The number of name/value pairs in the object; Any other value: NULL.

• column: Any column expression.

OBJECT_LENGTH(column)

Returns number of name-value pairs in the object.

• column: Any column expression.

OBJECT_NAMES(column)

Returns array containing the attribute names of the object, in N1QL collation order.

• column: Any column expression.

OBJECT_PAIRS(column)

Returns array containing the attribute name and value pairs of the object, in N1QL collation order of the names.

• column: Any column expression.

OBJECT_VALUES(column)

Returns array containing the attribute values of the object, in N1QL collation order of the corresponding names.

• column: Any column expression.

ARRAY_AVG(column)

Returns arithmetic mean (average) of all the non-NULL number values in the array, or NULL if there are no such values.

• column: Any column expression.

ARRAY_CONTAINS(column, value)

Returns true if the array contains value.

• column: Any column expression.
• value: The value contained within the array.

ARRAY_COUNT(column)

Returns count of all the non-NULL values in the array, or zero if there are no such values.

• column: Any column expression.

ARRAY_LENGTH(column)

Returns the number of elements in the array.

• column: Any column expression.

ARRAY_MAX(column)

Returns the largest non-NULL, non-MISSING array element, in N1QL collation order.

• column: Any column expression.

ARRAY_MIN(column)

Returns smallest non-NULL, non-MISSING array element, in N1QL collation order.

• column: Any column expression.

ARRAY_POSITION(column, value)

Returns the first position of value within the array, or -1. Array position is zero-based, i.e. the first position is 0.

• column: Any column expression.
• value: The value contained within the array.

ARRAY_SUM(column)

Sum of all the non-NULL number values in the array, or zero if there are no such values.

• column: Any column expression.

GREATEST(column1, column2 [,column3 [,column4]])

Largest non-NULL, non-MISSING value if the values are of the same type; otherwise NULL.

• column1: Any column expression.
• column2: Any column expression.
• column3: Any column expression.
• column4: Any column expression.

LEAST(column1, column2 [,column3 [,column4]])

Returns smallest non-NULL, non-MISSING value if the values are of the same type, otherwise returns NULL.

• column1: Any column expression.
• column2: Any column expression.
• column3: Any column expression.
• column4: Any column expression.

IFMISSING(column1, column2 [,column3 [,column4]])

Returns the first non-MISSING value.

• column1: Any column expression.
• column2: Any column expression.
• column3: Any column expression.
• column4: Any column expression.

IFMISSINGORNULL(column1, column2 [,column3 [,column4]])

Returns first non-NULL, non-MISSING value.

• column1: Any column expression.
• column2: Any column expression.
• column3: Any column expression.
• column4: Any column expression.

IFNULL(column1, column2 [,column3 [,column4]])

Returns first non-NULL value. Note that this function might return MISSING if there is no non-NULL value.

• column1: Any column expression.
• column2: Any column expression.
• column3: Any column expression.
• column4: Any column expression.

MISSINGIF(column1, column2)

Returns MISSING if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

• column1: Any column expression.
• column2: Any column expression.

NULLIF(column1, column2)

Returns NULL if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

• column1: Any column expression.
• column2: Any column expression.

IFINF(column1, column2 [,column3 [,column4]])

Returns first non-MISSING, non-Inf number. Returns MISSING or NULL if a non-number input is encountered first.

• column1: Any column expression.
• column2: Any column expression.
• column3: Any column expression.
• column4: Any column expression.

IFNAN(column1, column2 [,column3 [,column4]])

Returns first non-MISSING, non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.

• column1: Any column expression.
• column2: Any column expression.
• column3: Any column expression.
• column4: Any column expression.

IFNANORINF(column1, column2 [,column3 [,column4]])

Returns first non-MISSING, non-Inf, or non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.

• column1: Any column expression.
• column2: Any column expression.
• column3: Any column expression.
• column4: Any column expression.

NANIF(column1, column2 [,column3 [,column4]])

Returns NaN if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

• column1: Any column expression.
• column2: Any column expression.
• column3: Any column expression.
• column4: Any column expression.

NEGINFIF(column1, column2 [,column3 [,column4]])

Returns NegInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

• column1: Any column expression.
• column2: Any column expression.
• column3: Any column expression.
• column4: Any column expression.

POSINFIF(column1, column2 [,column3 [,column4]])

Returns PosInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

• column1: Any column expression.
• column2: Any column expression.
• column3: Any column expression.
• column4: Any column expression.

CLOCK_MILLIS()

Returns system clock at function evaluation time, as UNIX milliseconds. Varies during a query.

CLOCK_STR([string_fmt])

Returns system clock at function evaluation time, as a string in a supported format. Varies during a query.

• string_fmt: The datetime format to return the system clock in.

DATE_ADD_MILLIS(column, integer_n, string_part)

Performs date arithmetic, and returns result of computation. n and part are used to define an interval or duration, which is then added (or subtracted) to the UNIX time stamp, returning the result.

• column: Any column expression.
• integer_n: The number of string_part's to add to the column value.
• string_part: The part to add integer_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

DATE_ADD_STR(column, integer_n, string_part)

Performs date arithmetic. n and part are used to define an interval or duration, which is then added (or subtracted) to the date string in a supported format, returning the result.

• column: Any column expression.
• integer_n: The number of string_part's to add to the column value.
• string_part: The part to add integer_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

DATE_DIFF_MILLIS(column1, column2, string_part)

Performs date arithmetic. Returns the elapsed time between two UNIX time stamps as an integer whose unit is part.

• column1: Any column expression.
• column2: Any column expression.
• string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

DATE_DIFF_STR(column1, column2, string_part)

Performs date arithmetic. Returns the elapsed time between two date strings in a supported format, as an integer whose unit is part.

• column1: Any column expression.
• column2: Any column expression.
• string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

DATE_PART_MILLIS(column1, string_part [, tz])

Returns date part as an integer. The date expression is a number representing UNIX milliseconds, and part is one of the following date part strings.

• column1: Any column expression.
• string_part: The component of the date to extract. Available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day_of_year, day_of_week, iso_week, iso_year, iso_dow, timezone, timezone_hour, and timezone_minute.
• tz: The timezone to convert the local time to. Default to the system timezone if not specified. If an incorrect time zone is provided, the null is returned.

DATE_PART_STR(column1, string_part)

Returns date part as an integer. The date expression is a string in a supported format, and part is one of the supported date part strings.

• column1: Any column expression.
• string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day_of_year, day_of_week, iso_week, iso_year, iso_dow, timezone, timezone_hour, and timezone_minute.

DATE_TRUNC_MILLIS(column1, string_part)

Returns UNIX time stamp that has been truncated so that the given date part string is the least significant.

• column1: Any column expression.
• string_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

DATE_TRUNC_STR(column1, string_part)

Returns ISO 8601 time stamp that has been truncated so that the given date part string is the least significant.

• column1: Any column expression.
• string_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

MILLIS(column1)

Returns date that has been converted in a supported format to UNIX milliseconds.

• column1: Any column expression.

STR_TO_MILLIS(column1)

Returns date that has been converted in a supported format to UNIX milliseconds.

• column1: Any column expression.

MILLIS_TO_STR(column [, string_fmt])

Returns the string in the supported format to which the UNIX milliseconds has been converted.

• column1: Any column expression.
• string_fmt: The datetime format to return the system clock in.

MILLIS_TO_UTC(column [, string_fmt])

Returns the UTC string to which the UNIX time stamp has been converted in the supported format.

• column1: Any column expression.
• string_fmt: The datetime format to return the system clock in.

MILLIS_TO_TZ(column, string_tzname [, string_fmt])

Converts the UNIX time stamp to a string in the named time zone, and returns the string.

• column1: Any column expression.
• string_tzname: The time zone name.
• string_fmt: The datetime format to return the system clock in.

NOW_MILLIS()

Returns statement time stamp as UNIX milliseconds; does not vary during a query.

NOW_STR([string_fmt])

Returns statement time stamp as a string in a supported format; does not vary during a query.

• string_fmt: The datetime format to return the timestamp in.

STR_TO_UTC(column1)

Converts the ISO 8601 time stamp to UTC.

• column1: Any column expression.

STR_TO_ZONE_NAME(column, string_tzname)

Converts the supported time stamp string to the named time zone.

• column1: Any column expression.
• string_tzname: The time zone name.

BASE64(expression)

Returns base64 encoding of expression.

• expression: Any column or literal expression.

ABS(expression)

Returns absolute value of the number.

• expression: Any column or literal expression.

ACOS(expression)

Returns arccosine in radians.

• expression: Any column or literal expression.

ASIN(expression)

Returns arcsine in radians.

• expression: Any column or literal expression.

ATAN(expression)

Returns arctangent in radians.

• expression: Any column or literal expression.

ATAN2(expression1, expression2)

Returns arctangent of expression2/expression1.

• expression1: Any column or literal expression.
• expression2: Any column or literal expression.

CEIL(expression)

Returns smallest integer not less than the number.

• expression: Any column or literal expression.

COS(expression)

Returns cosine.

• expression: Any column or literal expression.

DEGREES(expression)

Returns radians to degrees.

• expression: Any column or literal expression.

E()

Base of natural logarithms.

EXP(expression)

Returns e^expression.

• expression: Any column or literal expression.

LN(expression)

Returns log base e.

• expression: Any column or literal expression.

LOG(expression)

Returns log base 10.

• expression: Any column or literal expression.

FLOOR(expression)

Largest integer not greater than the number.

• expression: Any column or literal expression.

PI()

Returns PI.

POWER(expression1, expression2)

Returns expression1^expression2.

• expression1: Any column or literal expression.
• expression2: Any column or literal expression.

RADIANS(expression)

Returns degrees to radians.

• expression: Any column or literal expression.

RANDOM([expression])

Returns pseudo-random number with optional seed.

• expression: Any column or literal expression.

ROUND(expression [, integer_digits])

Rounds the value to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.

• expression: Any column or literal expression.
• integer_digits: The number of digits to round to.

SIGN(expression)

Valid values: -1, 0, or 1 for negative, zero, or positive numbers respectively.

• expression: Any column or literal expression.

SIN(expression)

Returns sine.

• expression: Any column or literal expression.

SQRT(expression)

Returns square root.

• expression: Any column or literal expression.

TAN(expression)

Returns tangent.

• expression: Any column or literal expression.

TRUNC(expression [, integer_digits])

Truncates the number to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.

• expression: Any column or literal expression.
• integer_digits: The number of digits to truncate.

CONTAINS(column, string_substring)

True if the string contains the substring.

• column: Any column or literal expression.
• string_substring: The substring to search for.

INITCAP(column)

Converts the string so that the first letter of each word is uppercase and every other letter is lowercase.

• column: Any column or literal expression.

LENGTH(column)

Returns length of the string value.

• column: Any column or literal expression.

LOWER(column)

Returns lowercase of the string value.

• column: Any column or literal expression.

LTRIM(column [, string_chars])

Returns string with all leading chars removed. White space by default.

• column: Any column or literal expression.
• string_chars: The leading characters to remove.

POSITION(column, string_substring)

Returns the first position of the substring within the string, or -1. The position is zero-based, i.e., the first position is 0.

• column: Any column or literal expression.
• string_substring: The substring to search for.

REPEAT(column, integer_n)

Returns string formed by repeating expression n times.

• column: Any column or literal expression.
• integer_n: The number of times to repeat column.

REPLACE(column, string_substring, string_replace [, integer_n])

Returns string with all occurrences of substr replaced with repl. If n is given, at most n replacements are performed.

• column: The column expression.
• string_substring: The regular expression to match.
• string_replace: The value to replace the matched pattern.
• integer_n: The maximum number of replacements to make.

RTRIM(column [, string_chars])

Returns string with all trailing chars removed. White space by default.

• column: Any column or literal expression.
• string_chars: The trailing characters to remove.

SPLIT(column [, string_sep])

Splits the string into an array of substrings separated by string_sep. If string_sep is not given, any combination of white space characters is used.

• column: Any column or literal expression.
• string_sep: The separator to split column on.

SUBSTR(column, integer_position [, integer_length])

Returns substring from the integer position of the given length, or to the end of the string. The position is zero-based, i.e. the first position is 0. If position is negative, it is counted from the end of the string; -1 is the last position in the string.

• column: Any column or literal expression.
• integer_position: The starting position.
• integer_length: The total length of the substring to retrieve.

TRIM(column [, string_chars])

Returns string with all leading and trailing chars removed. White space by default.

• column: Any column or literal expression.
• string_chars: The leading and trailing characters to remove.

UPPER(column)

Returns uppercase of the string value.

• column: Any column or literal expression.

TOARRAY(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; Arrays are themselves; All other values are wrapped in an array.

• column: Any column expression.

TOATOM(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; Arrays of length 1 are the result of TOATOM() on their single element; Objects of length 1 are the result of TOATOM() on their single value; Booleans, numbers, and strings are themselves; All other values are NULL.

• column: Any column expression.

TOBOOLEAN(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; False is false; Numbers +0, -0, and NaN are false; Empty strings, arrays, and objects are false; All other values are true.

• column: Any column expression.

TONUMBER(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; False is 0; True is 1; Numbers are themselves; Strings that parse as numbers are those numbers; All other values are NULL.

• column: Any column expression.

TOOBJECT(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; Objects are themselves; All other values are the empty object.

• column: Any column expression.

TOSTRING(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; False is "false"; True is "true"; Numbers are their string representation; Strings are themselves; All other values are NULL.

• column: Any column expression.