Functions

ResourceQL supports the following functions.

**Table 1** Mathematical operation functions
Function	Description
abs(x)	Returns the absolute value of x.
ceil/ceiling(x)	Returns x rounded up to the nearest integer.
floor(x)	Returns x rounded down to the nearest integer.
pow/power(x, p) → double	Returns x raised to the power of p.
round(x)	Returns x rounded to the nearest integer.
round(x, d)	Returns x rounded to d decimal places.
sign(x)	Returns the sign of x. 1 if the argument is greater than 0 -1 if the argument is less than 0

**Table 2** String functions
Function	Description
concat(str1, str2, ..., strn) → string	Returns the concatenation of str1, str2, ..., strN.
chr(n) → string	Returns the Unicode code point n as a single character string.
codepoint(str) → int	Returns the Unicode code point of the only character of str.
length(str) → int	Returns the length of str in characters.
lower/upper(str) → string	Converts str to lowercase or uppercase.
replace(str, sub) → string	Removes all substrings from strings.
replace(str, sub, replace) → string	Replaces all instances of sub with replace in str.
reverse(str) → string	Returns str with the characters in reverse order.
split(str, delimiter) → array	Splits str on delimiter and returns an array.
strpos(str, sub) → int	Returns the starting position of the first instance of sub in str. Positions start with 1. If not found, 0 is returned.
strpos(str, sub, n) -> int	Returns the position of the N-th instance of sub in str. Positions start with 1. If not found, 0 is returned.
strrpos(str, sub) → int	Returns the starting position of the last instance of sub in str. Positions start with 1. If not found, 0 is returned.
strrpos(str, sub, n) -> int	Returns the position of the N-th instance of sub in str starting from the end of the string. Positions start with 1. If not found, 0 is returned.
substr(str, start) → string	Returns the rest of str from the starting position start.
substr(str, start, length) → string	Returns a substring with a length from the start index.
trim/lstrim/rstrim(str)	Removes leading and trailing whitespace from a string.

**Table 3** Array functions
Function	Description
all_match(array(T), function(T, boolean)) → boolean	Returns whether all elements of an array match the given predicate.
any_match(array(T), function(T, boolean)) → boolean	Returns whether any elements of an array match the given predicate.
array_average(a) → double	Returns the average of all non-null elements of a.
array_distinct(a) → array	Removes duplicate values from array a.
array_duplicates(a) → array	Returns a set of elements that occur more than once in array a.
array_frequency(a) → map	Returns a map: keys are the unique elements in array, values are how many times the key appears.
array_has_duplicates(a) → boolean	Returns a boolean: whether a has any elements that occur more than once.
array_intersect(a, b) → array	Returns an array of the elements in the intersection of a and b, without duplicates.
array_join(x, delimiter) → string	Concatenates the elements of the given array using the delimiter.
array_join(x, delimiter[, null_replacement]) → string	Concatenates the elements of the given array using the delimiter and an optional string to replace nulls.
array_max/array_min(a)	Returns the maximum or minimum value of input array a.
array_position(a, element) → int	Returns the position of the first occurrence of the element in array a (or 0 if not found).
array_position(a, element, instance) → int	Returns the position of the first occurrence of the element in array a. If no matching element instance is found, 0 is returned. If instance > 0, returns the position of the instance-th occurrence of the element in array a. If instance < 0, return the position of the instance-to-last occurrence of the element in array a.
array_remove(a, element) → array	Removes all elements that equal element from array a.
array_sort(a) → array	Sorts and returns array a.
array_sort(array(T), function(<T, T>, int)) → array	Sorts and returns the array based on the given comparator function. The comparator will take two nullable arguments representing two nullable elements of the array. It returns -1, 0, or 1 as the first nullable element is less than, equal to, or greater than the second nullable element.
array_sum(a)	Returns the sum of all non-null elements of a.
array_overlap(a, b) → boolean	Tests if arrays a and b have any non-null elements in common.
array_union(a, b) → array	Returns an array of the elements in the union of a and b, without duplicates.
array_except(x, y) → array	Returns an array of elements in x but not in y.
cardinality(a) → int	Returns the cardinality (size) of array a.
concat(a1, a2, ...) → array	Concatenates the arrays a1, a2, .... This function provides the same functionality as the SQL-standard concatenation operator (\|\|).
contains(a, element) → boolean	Returns true if the array a contains the element.
element_at(a, index)	Returns element of a at given index. If index < 0, element_at accesses elements from the last to the first.
filter(array(T), function(T, boolean)) → array(T)	Constructs an array from those elements of array for which function returns true.
none_match(array(T), function(T, boolean)) → boolean	Returns whether no elements of an array match the given predicate.
reverse(a) → array	Returns an array which has the reversed order of array a.
sequence(start, stop, step)	Generates a sequence of timestamps from start to stop, incrementing by step. It is similar to the range() function in Python, which returns a sequence of numbers, starting from 0 by default, and increments by 1 (by default), and stops before a specified number.
shuffle(a) → array	Generates a random permutation of given array a.
slice(a, start, length) → array	Subsets array a starting from index start (or starting from the end if start is negative) with a length of length.
transform(array(T), function(T, S)) → array(S)	Returns an array that is the result of applying function to each element of array.

**Table 4** Aggregate functions
Function	Description
arbitrary(x)	Returns an arbitrary non-null value of x, if one exists.
array_agg(x) → array	Returns an array created from the input x elements.
avg(x)→ double	Returns the average (arithmetic mean) of all input values.
bool_and/bool_or(x) → boolean	bool_and returns TRUE if every input value is TRUE, otherwise FALSE. bool_or returns TRUE if any input value is TRUE, otherwise FALSE.
coalesce(value1, value2, ...)	Returns the first non-null value in an argument list. Short-circuit evaluation will be used.
count(*)/count(x) → int	*count() returns the number of input rows. count(x)** returns the number of non-null input values.
greatest(value1, value2, ..., valueN)	Returns the largest of the provided values.
histogram(x) → map	Returns a map containing the count of the number of times each input value occurs.
least(value1, value2, ..., valueN)	Returns the smallest of the provided values.
max/min(x, n=1)	Returns n largest or smallest values of all input values of x.
max_by/min_by(x, y, n=1)	Returns n values of x associated with the n largest of all input values of y in descending order of y, or return n values of x associated with the n smallest of all input values of y in ascending order of y.
geometric_mean(x) → double	Returns the geometric mean of all input values.
set_agg(x) → array	Returns an array created from the distinct input x elements.
set_union(x) → array	Returns an array of all the distinct values contained in each array of the input.
sum(x)	Returns the sum of all input values.
multimap_agg(key, value)	Returns multiple mappings created from input key-value pairs.
map_agg(key, value)	Returns the mapping created from the input key-value pair.

**Table 5** Time functions
Function	Description
now() → date	Returns the current time.
date_diff(unit, timestamp1, timestamp2) → int	Returns timestamp2-timestamp1 expressed in terms of unit. The option of unit can be millisecond, second, minute, hour, day, week, month, quarter, or year.
date_parse(string, format) → timestamp	Parses a string into a timestamp using format.