Skip to content
DuckDBDuckDBDuckDB
English

Lambda Functions

DuckDB lambda functions (a.k.a. ‘lambdas’ or ‘lambda expressions’) are expressions that define an inline anonymous function. Lambdas are passed as argument to certain list functions, which evaluate the passed lambda expression against its elements. The value resulting from the lambda’s evaluation is then processed further by the list function to create its return value.

Lambdas may also be used in the COLUMNS clause. In that context, the lambda function acts as a filter: if it evaluates to true, the column is retained, and discarded otherwise. COLUMNS-clause lambdas have a mandatory parameter that receives the column name. Optionally, a second parameter may be declared that receives the (1-based) column index.

The syntax for a lambda expression is:

lambda ⟨parameters⟩ : ⟨expression⟩
  • ⟨parameters⟩{:.language-sql .highlight} is a comma-separated list of parameter names. Parameters may be given any name, and their name may be referenced in the ⟨expression⟩{:.language-sql .highlight}.
  • The number of allowed and required parameters depends on the list function to which it is passed.
  • There is always a mandatory parameter that is used by the list function to pass the current element of the list. This is typically the first parameter.
  • There is always an optional parameter used by the list function to pass the (1-based) index of the current element. This is always the last parameter.
  • The expression must be a scalar expression. Additionally, it cannot contain a subquery, table function, and the like.
  • The expression may contain macro invocations. However, only scalar macros are allowed, and only if they do not rely on a subquery.

For example, the following are all valid lambda functions:

  • lambda param : param > 1{:.language-sql .highlight}
  • lambda s : contains(concat(s, 'DB'), 'duck'){:.language-sql .highlight}
  • lambda acc, x : acc + x{:.language-sql .highlight}

Lambda functions enable the use of more complex and flexible expressions in queries.

Scalar Functions That Accept Lambda Functions

Function Description
apply(list, lambda(x)) Alias for list_transform.
array_apply(list, lambda(x)) Alias for list_transform.
array_filter(list, lambda(x)) Alias for list_filter.
array_reduce(list, lambda(x,y)[, initial_value]) Alias for list_reduce.
array_transform(list, lambda(x)) Alias for list_transform.
filter(list, lambda(x)) Alias for list_filter.
list_apply(list, lambda(x)) Alias for list_transform.
list_filter(list, lambda(x)) Constructs a list from those elements of the input list for which the lambda function returns true. DuckDB must be able to cast the lambda function’s return type to BOOL. The return type of list_filter is the same as the input list’s. See list_filter examples.
list_reduce(list, lambda(x,y)[, initial_value]) Reduces all elements of the input list into a single scalar value by executing the lambda function on a running result and the next list element. The lambda function has an optional initial_value argument. See list_reduce examples.
list_transform(list, lambda(x)) 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. See list_transform examples.
reduce(list, lambda(x,y)[, initial_value]) Alias for list_reduce.

list_filter(list, lambda(x))

| Description | Constructs a list from those elements of the input list for which the lambda function returns true. DuckDB must be able to cast the lambda function’s return type to BOOL. The return type of list_filter is the same as the input list’s. See list_filter examples. | | Example | list_filter([3, 4, 5], lambda x : x > 4) | | Result | [5] | | Aliases | array_filter, filter |

list_reduce(list, lambda(x,y)[, initial_value])

| Description | Reduces all elements of the input list into a single scalar value by executing the lambda function on a running result and the next list element. The lambda function has an optional initial_value argument. See list_reduce examples. | | Example | list_reduce([1, 2, 3], lambda x, y : x + y) | | Result | 6 | | Aliases | array_reduce, reduce |

list_transform(list, lambda(x))

| Description | 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. See list_transform examples. | | Example | list_transform([1, 2, 3], lambda x : x + 1) | | Result | [2, 3, 4] | | Aliases | apply, array_apply, array_transform, list_apply |

Nesting Lambda Functions

All scalar functions can be arbitrarily nested. For example, nested lambda functions to get all squares of even list elements:

SELECT list_transform(
        list_filter([0, 1, 2, 3, 4, 5], lambda x: x % 2 = 0),
        lambda y: y * y
    );
[0, 4, 16]

Nested lambda function to add each element of the first list to the sum of the second list:

SELECT list_transform(
        [1, 2, 3],
        lambda x :
            list_reduce([4, 5, 6], lambda a, b: a + b) + x
    );
[16, 17, 18]

Scoping

Lambda functions conform to scoping rules in the following order:

  • inner lambda parameters
  • outer lambda parameters
  • column names
  • macro parameters
CREATE TABLE tbl (x INTEGER);
INSERT INTO tbl VALUES (10);
SELECT list_apply(
            [1, 2],
            lambda x: list_apply([4], lambda x: x + tbl.x)[1] + x
    )
FROM tbl;
[15, 16]

Indexes as Parameters

All lambda functions accept an optional extra parameter that represents the index of the current element. This is always the last parameter of the lambda function (e.g., i in (x, i)), and is 1-based (i.e., the first element has index 1).

Get all elements that are larger than their index:

SELECT list_filter([1, 3, 1, 5], lambda x, i: x > i);
[3, 5]

Examples

list_transform Examples

Incrementing each list element by one:

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

Transforming strings:

SELECT list_transform(['Duck', 'Goose', 'Sparrow'], lambda s: concat(s, 'DB'));
[DuckDB, GooseDB, SparrowDB]

Combining lambda functions with other functions:

SELECT list_transform([5, NULL, 6], lambda x: coalesce(x, 0) + 1);
[6, 1, 7]

list_filter Examples

Filter out negative values:

SELECT list_filter([5, -6, NULL, 7], lambda x: x > 0);
[5, 7]

Divisible by 2 and 5:

SELECT list_filter(
        list_filter([2, 4, 3, 1, 20, 10, 3, 30], lambda x: x % 2 = 0),
        lambda y: y % 5 = 0
    );
[20, 10, 30]

In combination with range(...) to construct lists:

SELECT list_filter([1, 2, 3, 4], lambda x: x > #1) FROM range(4);
[1, 2, 3, 4]
[2, 3, 4]
[3, 4]
[4]

list_reduce Examples

Sum of all list elements:

SELECT list_reduce([1, 2, 3, 4], lambda acc, x: acc + x);
10

Only add up list elements if they are greater than 2:

SELECT list_reduce(
        list_filter([1, 2, 3, 4], lambda x: x > 2),
        lambda acc, x: acc + x
    );
7

Concat all list elements:

SELECT list_reduce(['DuckDB', 'is', 'awesome'], lambda acc, x: concat(acc, ' ', x));
DuckDB is awesome

Concatenate elements with the index without an initial value:

SELECT list_reduce(
        ['a', 'b', 'c', 'd'],
        lambda x, y, i: x || ' - ' || CAST(i AS VARCHAR) || ' - ' || y
    );
a - 2 - b - 3 - c - 4 - d

Concatenate elements with the index with an initial value:

SELECT list_reduce(
        ['a', 'b', 'c', 'd'],
        lambda x, y, i: x || ' - ' || CAST(i AS VARCHAR) || ' - ' || y, 'INITIAL'
    );
INITIAL - 1 - a - 2 - b - 3 - c - 4 - d

Limitations

  • Subqueries in lambda expressions are currently not supported. For example:

    SELECT list_apply([1, 2, 3], lambda x: (SELECT 42) + x);
    Terminal window
    Binder Error:
    subqueries in lambda expressions are not supported
  1. The expression can contain macro calls, but only if the macro is a scalar macro. In addition, the macro must not contain a subquery or rely on another macro that contains a subquery.
  2. lambda functions must be defined inline as argument to the list function. You cannot return a lambda function from a macro (but you can invoke a macro in the lambda’s expression).

Deprecated Syntax

Deprecated DuckDB v1.3 deprecated the old lambda single arrow syntax (x -> x + 1) in favor of the Python-style syntax (lambda x : x + 1).

DuckDB v1.3 also introduces a new setting to configure the lambda syntax.

SET lambda_syntax = 'DEFAULT';
SET lambda_syntax = 'ENABLE_SINGLE_ARROW';
SET lambda_syntax = 'DISABLE_SINGLE_ARROW';

Currently, DEFAULT enables both syntax styles, i.e., the old single arrow syntax and the Python-style syntax.

DuckDB v1.5 is the last release supporting the single arrow syntax without explicitly enabling it.

DuckDB v2.0 will disable the single arrow syntax by default.

DuckDB v2.1 will remove the lambda_syntax flag and fully deprecates the single arrow syntax, so the old behavior will no longer be possible.

This is an unofficial website and is not affiliated with DuckDB. Official site:duckdb.org.duckdb.ubitools.com · Translated and built with Astro and daisyUI