Edit this page

Lambda Functions

Lambda functions enable the use of more complex and flexible expressions in queries. SereneDB 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'), 'DB')
lambda acc, x : acc + x

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`. SereneDB 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))`

Constructs a list from those elements of the input list for which the lambda function returns true. SereneDB 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. Aliases: array_filter, filter.

Query

SELECT list_filter([3, 4, 5], lambda x : x > 4) AS list_filter;

Result

 list_filter------------- {5}

`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. Aliases: array_reduce, reduce.

Query

SELECT list_reduce([1, 2, 3], lambda x, y : x + y) AS list_reduce;

Result

 list_reduce-------------           6

`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. Aliases: apply, array_apply, array_transform, list_apply.

Query

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

Result

 list_transform---------------- {2,3,4}

Nesting Lambda Functions

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

Query

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

Result

 list_transform---------------- {0,4,16}

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

Query

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

Result

 list_transform---------------- {16,17,18}

Scoping

Lambda functions conform to scoping rules in the following order:

inner lambda parameters
outer lambda parameters
column names
macro parameters

Query

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;

Result

 list_apply------------ {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:

Query

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

Result

 list_filter------------- {3,5}

Examples

`list_transform` Examples

Incrementing each list element by one:

Query

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

Result

 list_transform---------------- {2,3,NULL,4}

Transforming strings:

Query

SELECT list_transform(['Serene', 'Calm', 'Tranquil'], lambda s: concat(s, 'DB'));

Result

 list_transform------------------------------ {SereneDB,CalmDB,TranquilDB}

Combining lambda functions with other functions:

Query

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

Result

 list_transform---------------- {6,1,7}

`list_filter` Examples

Filter out negative values:

Query

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

Result

 list_filter------------- {5,7}

Divisible by 2 and 5:

Query

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

Result

 list_filter------------- {20,10,30}

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

Query

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

Result

 list_filter------------- {1,2,3,4} {2,3,4} {3,4} {4}

`list_reduce` Examples

Sum of all list elements:

Query

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

Result

 list_reduce-------------          10

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

Query

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

Result

 list_reduce-------------           7

Concat all list elements:

Query

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

Result

 list_reduce--------------------- SereneDB is awesome

Concatenate elements with the index without an initial value:

Query

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

Result

 list_reduce--------------------------- a - 2 - b - 3 - c - 4 - d

Concatenate elements with the index with an initial value:

Query

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

Result

 list_reduce----------------------------------------- INITIAL - 1 - a - 2 - b - 3 - c - 4 - d

Limitations

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

Query

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

Result

error db error: ERROR: subqueries in lambda expressions are not supported

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​

Scoping​

Indexes as Parameters​

Examples​

list_transform Examples​

list_filter Examples​

list_reduce Examples​

Limitations​

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

Scoping

Indexes as Parameters

Examples

`list_transform` Examples

`list_filter` Examples

`list_reduce` Examples

Limitations