DuckDB Array Lambda Functions: list_transform, list_filter, and list_reduce Explained

Sometimes nested list data does not need to be unnested first. DuckDB list lambda functions let you transform, filter, and reduce list values directly inside a SELECT expression.

SQL for Files gives you a browser-based way to practice these DuckDB features on guided sample data or your own local files.

list_transform applies a lambda expression to every element and returns a new list with the transformed values.

SELECT customer, list_transform(prices, lambda p : p * 2) AS doubled_prices
FROM orders
ORDER BY customer;

list_filter keeps only elements where the lambda returns true. If no elements qualify, the result is an empty list.

SELECT customer, list_filter(prices, lambda p : p > 10) AS high_prices
FROM orders
ORDER BY customer;

list_reduce uses an accumulator to fold a list into one value, such as an order total. Combined with list_filter, it becomes a compact list-processing pipeline.

SELECT customer,
  COALESCE(
    list_reduce(
      list_filter(prices, lambda p : p > 10),
      lambda a, b : a + b
    ),
    0
  ) AS expensive_total
FROM orders
ORDER BY customer;

1. Use list_transform to apply a calculation to every price.
2. Use list_filter to keep only prices above a threshold.
3. Use list_reduce to calculate totals from list elements.
4. Combine list functions into compact SQL pipelines.