Lists & arrays Scalar

list_transform()

Returns a list that is the result of applying the lambda function to each element of the input list. The return type is defined by the return type of the lambda function.

Arguments & returns

`list_transform(list, lambda(x))`

Scalar

Overload link	`list`	`lambda(x)`	Returns
#01	`ANY[]`	`LAMBDA`	`ANY[]`

Example

SELECT list_transform([1, 2, 3], lambda x : x + 1);

Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

In a query

A little function. A whole new list.

Apply the same idea to every value. A lambda lets you describe the transformation right where you use it.

SELECT list_transform(
  [12, 24, 36],
  lambda price: price * 1.2
) AS prices_with_tax;

Haybarn WASM 1.5.5-rc3 · In your browser

Guide recipe · Run to view results.

In other engines

Used in larger rewrites 2

These examples use list_transform as one part of a larger SQL translation.

BigQuery`array_to_string()`

SELECT ARRAY_TO_STRING(['cake', 'pie', NULL], '--', 'MISSING') AS text

In DuckDB

SELECT ARRAY_TO_STRING(LIST_TRANSFORM(['cake', 'pie', NULL], x -> COALESCE(x, 'MISSING')), '--') AS text

SQLGlot example ↗

Snowflake`array_to_string()`

SELECT ARRAY_TO_STRING(x, '')