10.2 Logic, missing values and conversions

if

if(condition, then, else) → then when the condition is true, else else. The condition must be a boolean. Only the chosen branch is evaluated, so the other may read a missing field.

if(age >= 18, "adult", "minor")
if(qty == 0, null, price / qty)
if(true, 1, nope)          → 1          -- `nope` is never read

case

case(c1, v1, c2, v2, …, [default]) → the value of the first true condition, else default, else null. Lazy, like if.

case(qty > 5, "bulk", qty > 1, "few", "one")
case(false, 1)             → null
case(false, 1, 2)          → 2

coalesce

coalesce(a, b, …) → the first argument that is present and not null; null when none is. Missing arguments do not fail: coalesce is made for optional fields.

coalesce(nick, name)
coalesce(nope, n, 3)       → 3          -- nope missing, n null
coalesce(sales?, 0) - coalesce(costs?, 0)

exists

exists(x) → true when x is present (even null), false when missing. Never fails.

exists(nickname)
where exists(date) and date >= "2026-01-01"

optional

optional(x) → x, or null when missing. x? is the same.

optional(nick)             -- same as nick?

nullif

nullif(a, b) → null when a == b, else a. Turns a sentinel into null.

nullif(status, "")
coalesce(nullif(label, ""), code)

between

between(x, low, high) → low <= x and x <= high. x between low and high is the operator form.

between(5, 1, 10)          → true
where price between 10 and 100

bucket

bucket(x, [b0, b1, …, bn] [, default]) → the lower bound b(i) of the range [b(i), b(i+1)) holding x, else default (or null). The last bound closes the last range: with [0, 18, 65], 65 and above fall to the default. Bounds must be strictly ascending.

bucket(25, [0, 18, 65])            → 18
bucket(70, [0, 18, 65], "old")     → "old"
derive band = bucket(age, [0, 18, 30, 50, 120])

See also the bucket stage, which also counts.

to_int

to_int(x) → an integer from a number (truncated toward zero), a numeric string or a boolean (true → 1). null stays null.

to_int("42")       → 42
to_int(3.9)        → 3
to_int(-3.9)       → -3
to_int(null)       → null
to_int("x")        → error: `to_int` cannot convert String("x")

to_float

to_float(x) → a float from a number, a numeric string or a boolean; null stays null.

to_float("2.5")    → 2.5

to_string

to_string(x) → text. Numbers, booleans, arrays and objects are written as JSON; null stays null.

to_string(qty)     → "3"
to_string([1,2])   → "[1,2]"
group | by to_string(client_code) as client | … | end

to_bool

to_bool(x) → a boolean from a boolean, a number (non-zero is true), or the texts true/false, 1/0, yes/no. Anything else is an error.

to_bool("yes")     → true
to_bool(0)         → false
to_bool("maybe")   → error: `to_bool` cannot convert "maybe"

type

type(x) → the kind of a value: "null", "bool", "number", "string", "array" or "object".

type(1.5)          → "number"
type(profile)      → "object"
where type(code?) == "string"

Operators. and, or, not (and &&, ||, !) need booleans and short-circuit; in / not in test membership in an array; between … and … tests a range. See chapter 5.