Expressions and operators

Precedence

When operators are mixed, VegaDB evaluates tighter-binding operators first. Parentheses are recommended whenever intent is not visually obvious.

JSON arrows also participate in lambda syntax and bind loosely in some contexts. Parenthesize extracted values before comparing them:

WHERE (payload->>'$.status') = 'paid'

NULL and three-valued logic

Ordinary comparisons with NULL produce NULL. A WHERE, HAVING or QUALIFY filter retains only rows for which its predicate is TRUE.

Use IS [NOT] DISTINCT FROM for NULL-safe equality. Be careful with NOT IN: one NULL in the right-hand set can make the result unknown.

Comparison and membership

amount BETWEEN 10 AND 100
region IN ('ae', 'sa', 'qa')
customer_id = ANY ($1::BIGINT[])
score >= ALL (SELECT minimum_score FROM policies)

BETWEEN includes both bounds. IN (subquery) and EXISTS (subquery) may be correlated with an outer query. Scalar subqueries must return at most one row.

Pattern matching

All forms have negated variants. Escape or bind user input; do not concatenate untrusted pattern text into SQL.

CASE and conditional expressions

CASE status
  WHEN 'paid' THEN net_amount
  WHEN 'refunded' THEN -net_amount
  ELSE 0
END

Searched CASE evaluates predicates in order. coalesce returns the first non-NULL argument, nullif(a, b) returns NULL when its arguments compare equal, and if(condition, then_value, else_value) is a compact conditional.

TRY(expression) converts an error raised while evaluating the protected scalar expression into NULL where the warehouse release exposes that form. Prefer the narrower TRY_CAST for input conversion.

Stars and column expressions

SELECT * EXCLUDE (internal_id, source_payload)
REPLACE (lower(email) AS email)
RENAME (created_at AS imported_at)
FROM staged_customers;

COLUMNS(pattern) selects matching columns for expressions that accept a column set. Star modifiers are resolved after input binding and fail when a requested column is missing or ambiguous.

Lambda expressions

List operations accept lambdas with ->:

SELECT list_filter(amounts, x -> x > 0),
       list_transform(amounts, x -> round(x, 2)),
       list_reduce(amounts, (x, y) -> x + y, 0)
FROM settlements;

Lambda variables are scoped to the lambda and can reference legal outer columns. Cast an empty list or ambiguous NULL so its element type is known.

Subqueries

Subqueries may appear as scalar expressions, membership inputs, EXISTS predicates, derived tables and lateral relations. A lateral relation can reference relations written before it in FROM.

SELECT c.customer_id, recent.order_id
FROM customers AS c
LEFT JOIN LATERAL (
  SELECT order_id
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
  ORDER BY ordered_at DESC
  LIMIT 1
) AS recent ON TRUE;