Chalk SQL Expressions

An expression computes a value. Expressions appear in the SELECT list, in WHERE, GROUP BY, HAVING, and ORDER BY, and as function arguments. This page documents the expression syntax that is not a named function. Named functions are documented in the Chalk SQL function reference. The star expression, COLUMNS(...), and subqueries are documented on the SELECT page.

Every example on this page builds its input from literal values, so it runs as written against any Chalk environment.

​

Literals

A literal is a value written directly in the query.

​

Lists, structs, and maps

A list literal is a comma-separated list of values in square brackets. A struct literal is a set of 'key': value pairs in curly braces. A map literal is the keyword MAP followed by key: value pairs in curly braces.

SELECT
    [10, 20, 30] AS amounts,
    {'city': 'Oakland', 'zip': '94612'} AS address,
    MAP {'small': 1, 'large': 3} AS sizes;

Struct keys are field names and are always strings. Map keys can be any type, and every key in one map has the same type.

​

Dates, timestamps, and intervals

A typed literal is a type name followed by a string. INTERVAL takes either a number and a unit or a quoted string.

SELECT
    DATE '2026-10-14' AS day,
    TIMESTAMP '2026-10-14 08:00:00' AS local_time,
    TIMESTAMPTZ '2026-10-14 08:00:00+00' AS utc_time,
    TIMESTAMP '2026-10-14 08:00:00' + INTERVAL 1 DAY AS next_day,
    TIMESTAMP '2026-10-14 08:00:00' + INTERVAL '2 hours' AS two_hours_later;

See Timestamp types for the difference between TIMESTAMP and TIMESTAMPTZ.

​

Struct field access

Read a struct field with a dot followed by the field name. Quote a field name that contains spaces or other special characters with double quotes. Brackets with a string literal read the same field.

SELECT
    address.city,                -- 'Oakland', in a column named "city"
    address."zip code",          -- '94612', in a column named "zip code"
    address['city'] AS city_2    -- 'Oakland'
FROM (SELECT {'city': 'Oakland', 'zip code': '94612'} AS address);

Dots chain through nested structs, and a table alias can come first:

SELECT t.customer.address.city    -- 'Oakland'
FROM (SELECT {'address': {'city': 'Oakland'}} AS customer) AS t;

Chalk SQL first reads a dotted name as written, so t.customer is the column customer of the table t. When no table or column matches the full name, Chalk SQL takes the longest leading part of the name that is a column and reads the rest as struct fields. Field access works anywhere an expression is allowed, including WHERE and correlated subqueries.

​

Map lookup

Read a map value with the key in square brackets. A key that is not in the map returns NULL.

SELECT
    sizes['large'] AS large,      -- 3
    sizes['medium'] AS medium     -- NULL
FROM (SELECT MAP {'small': 1, 'large': 3} AS sizes);

The key must have the map’s key type. Looking up an integer key in a map with string keys is a type error.

​

List indexing and slicing

List indexes start at 1. A negative index counts from the end of the list, so -1 is the last element. An index past either end of the list returns NULL. Index 0 is an error.

list[start:end] returns the elements from start to end, including both ends. Leave out start to slice from the first element, or end to slice to the last.

SELECT
    amounts[1] AS first,              -- 10
    amounts[-1] AS last,              -- 30
    amounts[5] AS missing,            -- NULL
    amounts[2:3] AS middle,           -- [20, 30]
    amounts[:2] AS first_two,         -- [10, 20]
    amounts[-2:] AS last_two          -- [20, 30]
FROM (SELECT [10, 20, 30] AS amounts);

Brackets chain, so [[1, 2], [3, 4]][2][1] returns 3.

​

CASE

A searched CASE returns the result of the first WHEN condition that is true. A simple CASE compares one value against each WHEN value. When no branch matches and there is no ELSE, the result is NULL.

SELECT
    amount,
    CASE
        WHEN amount >= 100 THEN 'large'
        WHEN amount >= 10 THEN 'medium'
        ELSE 'small'
    END AS size,
    CASE status
        WHEN 1 THEN 'open'
        WHEN 2 THEN 'closed'
    END AS status_name
FROM (VALUES (250, 1), (40, 2), (5, 3)) AS t(amount, status);

The third row has status 3, which matches no branch, so its status_name is NULL.

​

Casts

CAST(value AS type) and value::type convert a value to another type. When a conversion fails, CAST returns an error and TRY_CAST(value AS type) returns NULL.

SELECT
    CAST('42' AS BIGINT) AS a,        -- 42
    '42'::BIGINT AS b,                -- 42
    TRY_CAST('abc' AS BIGINT) AS c;   -- NULL

​

Timestamp types

Chalk SQL has two kinds of timestamp type:

Type	Meaning
TIMESTAMP	A date and time with no time zone, in microseconds
TIMESTAMP_S, TIMESTAMP_MS, TIMESTAMP_NS	The same, in seconds, milliseconds, or nanoseconds
TIMESTAMPTZ, also written TIMESTAMP WITH TIME ZONE	An instant in time, stored in UTC

Conversions between the two kinds use UTC:

• Casting a TIMESTAMP to TIMESTAMPTZ reads its date and time as UTC.
• Casting a TIMESTAMPTZ to TIMESTAMP returns its date and time in UTC.
• Casting a string that includes a UTC offset to TIMESTAMP returns the date and time in UTC.

SELECT
    CAST(TIMESTAMP '2026-10-14 08:00:00' AS TIMESTAMPTZ) AS a,      -- 2026-10-14 08:00:00+00
    CAST(TIMESTAMPTZ '2026-10-14 08:00:00+02' AS TIMESTAMP) AS b,   -- 2026-10-14 06:00:00
    CAST('2026-10-14 05:00:00+02' AS TIMESTAMP) AS c;               -- 2026-10-14 03:00:00

A function or comparison that combines a TIMESTAMP with a TIMESTAMPTZ reads the TIMESTAMP as UTC. A function called with only TIMESTAMP arguments, such as date_trunc, returns a TIMESTAMP. To convert between UTC and another time zone, use AT TIME ZONE, as in ts AT TIME ZONE 'America/New_York'.

​

Lambdas

A lambda is an inline function passed as an argument to a function that applies it to each element of a list. Chalk SQL accepts three forms:

• x -> x * 10 for one parameter
• (a, b) -> a + b for two parameters
• lambda x: x * 10, the Python-style form

SELECT
    list_transform([1, 2, 3], x -> x * 10) AS tens,                   -- [10, 20, 30]
    list_filter([1, 2, 3, 4], lambda x: x % 2 = 0) AS evens,          -- [2, 4]
    zip_with([1, 2], [10, 20], (a, b) -> a + b) AS sums,              -- [11, 22]
    array_reduce([1, 2, 3], 0, (acc, x) -> acc + x, acc -> acc) AS total;   -- 6

Lambdas are accepted only by the following functions:

Function	Lambda parameters	Result
list_transform, array_transform, transform, list_apply, array_apply	The element	A list of the lambda’s results
list_filter, array_filter	The element	A list of the elements for which the lambda is true
list_sort, array_sort	The element	The list, sorted by the lambda’s result
find_first	The element	The first element for which the lambda is true
find_first_index	The element	The index of that element
map_contains	The map key	Whether any key satisfies the lambda
zip_with	One element from each of two lists	A list of the lambda’s results
array_reduce	Two lambdas, described below	The final value

array_reduce(list, initial_state, (state, x) -> new_state, state -> result) takes two lambdas. The first combines the running state with each element, starting from initial_state. The second turns the final state into the result.

A lambda parameter that holds a struct supports field access, for example list_transform(people, p -> p.name). Outside these function calls, -> is the JSON extraction operator. COLUMNS(...) also accepts a lambda over column names.