# Chalk SQL Expressions source: https://docs.chalk.ai/docs/chalksql/expressions ## Syntax for literals, struct, map, and list access, CASE, casts, and lambdas. 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.