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

DuckDB 1.3.0 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 1.4.0 will be the last release supporting the single arrow syntax without explicitly enabling it.

DuckDB 1.5.0 disables the single arrow syntax on default.

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

Lambda functions enable the use of more complex and flexible expressions in queries. DuckDB supports several scalar functions that operate on LISTs and accept lambda functions as parameters in the form lambda parameter1, parameter2, ... : expression. If the lambda function has only one parameter, then the parentheses can be omitted. The parameters can have any names. For example, the following are all valid lambda functions:

• lambda param : param > 1
• lambda s : contains(concat(s, 'DB'), 'duck')
• lambda acc, x : acc + x

Scalar Functions That Accept Lambda Functions

list_filter(list, lambda(x))

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

list_transform(list, lambda(x))

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 confirm 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);

Binder Error:
subqueries in lambda expressions are not supported