Functions + Operators

This page details Materialize’s supported SQL functions and operators.

Functions

Generic functions can typically take arguments of any type.

Function	Computes
`CAST (cast_expr) -> T`	Value as type `T` (docs)
`coalesce(x: T...) -> T?`	First non-NULL arg, or NULL if all are NULL
`nullif(x: T, y: T) -> T?`	NULL if `x == y`, else `x`

Aggregate functions take one or more of the same element type as arguments.

Function	Computes
`count(x: T) -> int`	Number of non-NULL inputs
`max(x: T) -> T`	Maximum value among `T`
`min(x: T) -> T`	Minimum value among `T`
`sum(x: T) -> T`	Sum of `T`’s values
`stddev(x: T) -> T`	Historical alias for `stddev_samp` (imprecise)
`stddev_pop(x: T) -> T`	Population standard deviation of `T`’s values (imprecise)
`stddev_samp(x: T) -> T`	Sample standard deviation of `T`’s values (imprecise)
`variance(x: T) -> T`	Historical alias for `variance_samp` (imprecise)
`variance_pop(x: T) -> T`	Population variance of `T`’s values (imprecise)
`variance_samp(x: T) -> T`	Sample variance of `T`’s values (imprecise)
`jsonb_agg(expression) -> jsonb`	Aggregate values (including nulls) as a jsonb array. (docs)

Number functions take number-like arguments, e.g. int, float, numeric.

Function	Computes
`abs(x: N) -> N`	The absolute value of `x`
`ceil(x: N) -> N`	The largest integer >= `x`
`floor(x: N) -> N`	The largest integer <= `x`
`mod(x: N, y: N) -> N`	`x % y`
`round(x: N) -> N`	`x` rounded to the nearest whole number; halves are rounded up
`round(x: numeric, y: int) -> numeric`	`x` rounded to `y` decimal places, while retaining the same `numeric` scale; halves are rounded up

Function	Computes
`ascii(s: str) -> int`	The ASCII value of `s`’s left-most character
`length(s: str) -> int`	Number of graphemes in `s` (docs)
`length(s: str, encoding_name: str) -> int`	Number of graphemes in `s` using `encoding_name` (docs)
`replace(s: str, f: str, r: str) -> str`	`s` with all instances of `f` replaced with `r`
`substring(s: str, start_pos: int) -> str`	Substring of `s` starting at `start_pos` (docs)
`substring(s: str, start_pos: int, l: int) -> str`	Substring starting at `start_pos` of length `l` (docs)
`regexp_extract(regex: str, haystack: str) -> Col<string>`	Values of the capture groups of `regex` as matched in `haystack`

Scalar functions take a list of scalar expressions

Function	Computes
`expression bool_op ALL(s: Scalars) -> bool`	`true` if applying bool_op to `expression` and every value of `s` evaluates to `true`
`expression bool_op ANY(s: Scalars) -> bool`	`true` if applying bool_op to `expression` and any value of `s` evaluates to `true`
`expression IN(s: Scalars) -> bool`	`true` for each value in `expression` if it matches at least one element of `s`
`expression NOT IN(s: Scalars) -> bool`	`true` for each value in `expression` if it does not match any elements of `s`
`expression bool_op SOME(s: Scalars) -> bool`	`true` if applying bool_op to `expression` and any value of `s` evaluates to `true`

Subquery functions take a query, e.g. SELECT

Function	Computes
`expression bool_op ALL(s: Query) -> bool`	`s` must return exactly one column; `true` if applying bool_op to `expression` and every value of `s` evaluates to `true`
`expression bool_op ANY(s: Query) -> bool`	`s` must return exactly one column; `true` if applying bool_op to `expression` and any value of `s` evaluates to `true`
`EXISTS(s: Query) -> bool`	`true` if `s` returns at least one row
`expression IN(s: Query) -> bool`	`s` must return exactly one column; `true` for each value in `expression` if it matches at least one element of `s`
`NOT EXISTS(s: Query) -> bool`	`true` if `s` returns zero rows
`expression NOT IN(s: Query) -> bool`	`s` must return exactly one column; `true` for each value in `expression` if it does not match any elements of `s`
`expression bool_op SOME(s: Query) -> bool`	`s` must return exactly one column; `true` if applying bool_op to `expression` and any value of `s` evaluates to `true`

Time functions take or produce a time-like type, e.g. date, timestamp, timestamptz.

Function	Computes
`current_timestamp() -> timestamptz`	The `timestamptz` representing when the query was executed. NOTE: Users cannot define views with queries containing `current_timestamp()`.
`date_trunc(time_component: str, val: timestamp) -> timestamp`	Largest `time_component` <= `val` (docs)
`EXTRACT(extract_expr) -> float`	Specified time component from value (docs)
`mz_logical_timestamp() -> numeric`	The logical time at which a query executes. NOTE: Users cannot define views with queries containing `mz_logical_timestamp()`.
`now() -> timestamptz`	The `timestamptz` representing when the query was executed. NOTE: Users cannot define views with queries containing `now()`.
`to_timestamp(val: double precision) -> timestamptz`	Converts Unix epoch (seconds since 00:00:00 UTC on January 1, 1970) to timestamp

Function	Computes
`jsonb_array_elements(j: jsonb) -> Col<jsonb>`	`j`’s elements if `j` is an array. (docs)
`jsonb_array_elements_text(j: jsonb) -> Col<string>`	`j`’s elements if `j` is an array. (docs)
`jsonb_array_length(j: jsonb) -> int`	Number of elements in `j`’s outermost array. (docs)
`jsonb_build_array(x: ...) -> jsonb`	The elements of `x` in a `jsonb` array. Elements can be of heterogenous types. (docs)
`jsonb_build_object(x: ...) -> jsonb`	The elements of x as a `jsonb` object. The argument list alternates between keys and values. (docs)
`jsonb_each(j: jsonb) -> Col<(key: string, value: jsonb)>`	`j`’s outermost elements if `j` is an object. (docs)
`jsonb_each_text(j: jsonb) -> Col<(key: string, value: string)>`	`j`’s outermost elements if `j` is an object. (docs)
`jsonb_object_keys(j: jsonb) -> Col<string>`	`j`’s outermost keys if `j` is an object. (docs)
`jsonb_pretty(j: jsonb) -> string`	Pretty printed (i.e. indented) `j`. (docs)
`jsonb_typeof(j: jsonb) -> string`	Type of `j`’s outermost value. One of `object`, `array`, `string`, `number`, `boolean`, and `null`. (docs)
`jsonb_strip_nulls(j: jsonb) -> jsonb`	`j` with all object fields with a value of `null` removed. Other `null` values remain. (docs)
`to_jsonb(v: T) -> jsonb`	`v` as `jsonb` (docs)

Operator	Computes
`val::type`	Cast of `val` as `type` (docs)

Operator	Computes
`AND`	Boolean “and”
`OR`	Boolean “or”
`=`	Equality
`<>`	Inequality
`!=`	Inequality
`<`	Less than
`>`	Greater than
`<=`	Less than or equal to
`>=`	Greater than or equal to
`a BETWEEN x AND y`	`a >= x AND a <= y`
`a NOT BETWEEN x AND y`	`a < x OR a > y`
`a IS NULL`	`a = NULL`
`a IS NOT NULL`	`a != NULL`
`a LIKE match_expr`	`a` matches `match_expr`, using SQL LIKE matching

Operator	Computes
`\|\|`	Concatenation

Operator	RHS Type	Description
`->`	`string`, `int`	Access field by name or index position, and return `jsonb` (docs)
`->>`	`string`, `int`	Access field by name or index position, and return `string` (docs)
`\|\|`	`jsonb`	Concatenate LHS and RHS (docs)
`-`	`string`	Delete all values with key of RHS (docs)
`@>`	`jsonb`	Does element contain RHS? (docs)
`<@`	`jsonb`	Does RHS contain element? (docs)
`?`	`string`	Is RHS a top-level key? (docs)