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). The expression and values may be tuples to test multiple columns at once, as in isIn(tuple([column('a'), column('b')]), [tuple([1, 2]), tuple([3, 4])]), which maps to (a, b) IN ((1, 2), (3, 4)).
isInDistinct
isInDistinct(expression, values)
Returns a null-safe expression testing if the input expression matches any of the entries in the values array. Unlike isIn, a NULL literal in values matches NULL values of expression, via an added expression IS NULL check.
The expression may also be a tuple, in which case each entry in values must be a tuple of the same length. Value tuples without NULL literals are tested with a single tuple IN operator, while tuples with NULL literals are tested with explicit per-column checks. For example, isInDistinct(tuple([column('a'), column('b')]), [tuple([1, 2]), tuple([null, 4])]) maps to ((a, b) IN ((1, 2))) OR (a IS NULL AND b = 4).
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).