Operators

SQL comparison operator expressions.

and

and(...clauses)

Returns an expression for the logical AND of the provided clauses. The clauses array will be flattened and any null entries will be ignored.

or

or(...clauses)

Returns an expression for the logical OR of the provided clauses. The clauses array will be flattened and any null entries will be ignored.

not

not(expression)

Returns an expression for the logical NOT of the provided expression.

eq

eq(a, b)

Returns an expression testing if expression a is equal to expression b. In SQL semantics, two NULL values are not considered equal. Use isNotDistinct to compare values with NULL equality.

neq

neq(a, b)

Returns an expression testing if expression a is not equal to expression b. In SQL semantics, two NULL values are not considered equal. Use isDistinct to compare values with NULL equality.

lt

lt(a, b)

Returns an expression testing if expression a is less than expression b.

gt

gt(a, b)

Returns an expression testing if expression a is greater than expression b.

lte

lte(a, b)

Returns an expression testing if expression a is less than or equal to expression b.

gte

gte(a, b)

Returns an expression testing if expression a is greater than or equal to expression b.

isNull

isNull(expression)

Returns an expression testing if the input expression is a NULL value.

isNotNull

isNotNull(expression)

Returns an expression testing if the input expression is not a NULL value.

isDistinct

isDistinct(a, b)

Returns an expression testing if expression a is distinct from expression b. Unlike normal SQL equality checks, here NULL values are not considered distinct.

isNotDistinct

Returns an expression testing if expression a is not distinct from expression b. Unlike normal SQL equality checks, here NULL values are not considered distinct.

isBetween

isBetween(expression, [lo, hi])

Returns an expression testing if the input expression lies between the values lo and hi, provided as a two-element array. Equivalent to lo <= expression AND expression <= hi.

isNotBetween

isNotBetween(expression, [lo, hi])

Returns an expression testing if the input expression does not lie between the values lo and hi, provided as a two-element array. Equivalent to NOT(lo <= expression AND expression <= hi).

isIn

isIn(expression, values)

Returns an expression testing if the input expression matches any of the entries in the values array. Maps to expression IN (...values).

compareAny

compareAny(expression, op, subquery)

Returns an expression testing if the comparison op holds between the input expression and any row of the single-column subquery. The op argument is one of "=", "<>", "!=", "<", ">", "<=", or ">=". Maps to expression op ANY (subquery).

compareAll

compareAll(expression, op, subquery)

Returns an expression testing if the comparison op holds between the input expression and every row of the single-column subquery. The op argument is one of "=", "<>", "!=", "<", ">", "<=", or ">=". Maps to expression op ALL (subquery).

listContains

listContains(expression, value)

Returns an expression testing if the input value exists in the expression list. Maps to list_contains(expression, value).

listHasAny

listHasAny(expression, values)

Returns an expression testing if any of the input values exist in the expression list. Maps to list_has_any(expression, values).

listHasAll

listHasAll(expression, values)

Returns an expression testing if all the input values exist in the expression list. Maps to list_has_all(expression, values).

subscript

subscript(expression, index)

Returns an expression that indexes into the expression list, string, struct, or map. Maps to expression[index]. String-valued index arguments are interpreted as literals, for example to access struct fields; use column to index by a column value.

slice

slice(expression, start, end, step)

Returns an expression that slices the expression list or string. Maps to expression[start:end:step]. The start and end indices are inclusive and 1-based. If start or end is null or undefined, the slice extends to the beginning or end of the input. The step argument is optional.

unnest

unnest(expression)

Returns an expression that unnests the expression list or struct. Maps to UNNEST(expression).