10. Functions at a glance
74 functions, called by name: round(price * qty, 2). Names are not reserved words, arguments are any expressions,
and the argument count is checked when the query is parsed. Arity reads min..max (n.. for no maximum).
| Function | Arity | Category | Returns |
|---|---|---|---|
abs | 1 | Numeric | Absolute value |
sqrt | 1 | Numeric | Square root |
pow | 2 | Numeric | Power |
round | 1..3 | Numeric | Rounded value |
trunc | 1..2 | Numeric | Value rounded toward zero |
floor | 1 | Numeric | Rounded down, integer |
ceil | 1 | Numeric | Rounded up, integer |
sign | 1 | Numeric | -1, 0 or 1 |
ln | 1 | Numeric | Natural logarithm |
log10 | 1 | Numeric | Base-10 logarithm |
log | 2 | Numeric | Logarithm in a base |
exp | 1 | Numeric | Exponential |
min | 1.. | Numeric | Smallest argument or item |
max | 1.. | Numeric | Largest argument or item |
sum | 1.. | Numeric | Sum |
avg | 1.. | Numeric | Mean |
median | 1.. | Numeric | Middle value |
percentile | 2 | Numeric | Value at a fraction |
stddev | 1.. | Numeric | Sample standard deviation |
div | 2..3 | Numeric | Safe division |
if | 3 | Logic | One of two values |
case | 2.. | Logic | Value of the first true condition |
coalesce | 1.. | Missing values | First present, non-null argument |
exists | 1 | Missing values | Whether a value is present |
optional | 1 | Missing values | The value, or null when missing |
nullif | 2 | Missing values | null when equal |
between | 3 | Logic | Inclusive range test |
bucket | 2..3 | Logic | Lower bound of a range |
to_int | 1 | Conversion | Integer |
to_float | 1 | Conversion | Float |
to_string | 1 | Conversion | Text |
to_bool | 1 | Conversion | Boolean |
type | 1 | Conversion | Kind name |
len | 1 | Text | Length |
lower | 1 | Text | Lower case |
upper | 1 | Text | Upper case |
trim | 1 | Text | Spaces removed at both ends |
ltrim | 1 | Text | Spaces removed at the start |
rtrim | 1 | Text | Spaces removed at the end |
concat | 1.. | Text | Joined text or arrays |
contains | 2 | Text | Substring or item test |
starts_with | 2 | Text | Prefix test |
ends_with | 2 | Text | Suffix test |
substr | 2..3 | Text | Substring |
split | 2 | Text | Array of parts |
replace | 3 | Text | Text with every occurrence replaced |
index_of | 2 | Text | Position, or -1 |
pad_left | 2..3 | Text | Text padded on the left |
pad_right | 2..3 | Text | Text padded on the right |
at | 2 | Array | Item at a position |
slice | 2..3 | Array | Sub-array |
reverse | 1 | Array | Reversed array or text |
sort_array | 1..2 | Array | Sorted copy |
unique | 1 | Array | Distinct items |
flatten | 1 | Array | Array flattened one level |
range | 2..3 | Array | Integers |
keys | 1 | Object | Keys |
values | 1 | Object | Values |
merge | 1.. | Object | Shallow merge |
get | 2..3 | Object | Value under a key |
has | 2 | Object | Whether a key is present |
now | 0 | Date | Current time |
parse_date | 1 | Date | Epoch milliseconds |
date_trunc | 2 | Date | Start of a unit |
date_add | 3 | Date | Date moved by n units |
date_diff | 3 | Date | Unit boundaries crossed |
date_part | 2 | Date | A part of a date |
date_format | 1..2 | Date | Formatted text |
fiscal_year | 2 | Fiscal | Fiscal year |
fiscal_month | 2 | Fiscal | Month in the fiscal year |
fiscal_start | 2 | Fiscal | First day of the fiscal year |
fiscal_end | 2 | Fiscal | Last day of the fiscal year |
month_end | 1 | Date | Last day of the month |
days_in | 2 | Date | Days in the unit |
groupmeasures (sum,avg,min,max,first,last,count,list,median,percentile,stddev) andwindowfunctions (lag,lead,cumsum,first,last,row_number) are directives of their stage, not calls.
Common rules
- Missing arguments. A function reading a missing field fails, like any expression, except
exists,coalesceandoptional, made for that. Writefield?to passnullinstead. - Null. There is no general null propagation: numeric, text and date functions refuse
null(`upper` expects a string, found Null). The functions that accept it:coalesce,exists,optional,nullif,if/casebranches,concat(skips it),to_int/to_float/to_string(keep it),type,merge(skips it), and the aggregatessum,avg,median,percentile,stddev(skipnullitems;minandmaxrefuse them). - Kinds. Functions check their argument kinds:
upper(42)is an error. Convert withto_string,to_int,to_float. - Positions. Text positions count characters; negative positions count from the end; arrays are 0-based.
- Determinism. Every function is deterministic except
now().