15.19. Lambda Expressions and Functions
Lambda Expression
Lambda expressions are written with ->
:
x -> x + 1
(x, y) -> x + y
x -> regexp_like(x, 'a+')
x -> x[1] / x[2]
x -> IF(x > 0, x, -x)
x -> COALESCE(x, 0)
x -> CAST(x AS JSON)
Most SQL expressions can be used in a lambda body, with a few exceptions:
- Subqueries are not supported.
- The
TRY
function is not supported yet. (try_cast()
is supported.) - Capture is not supported yet:
- Columns or relations cannot be referenced.
- Only lambda variables from the inner-most lambda expression can be referenced.
Lambda Functions
-
filter
(array<T>, function<T, boolean>) → ARRAY<T> Constructs an array from those elements of
array
for whichfunction
returns true:SELECT filter(ARRAY [], x -> true); -- [] SELECT filter(ARRAY [5, -6, NULL, 7], x -> x > 0); -- [5, 7] SELECT filter(ARRAY [5, NULL, 7, NULL], x -> x IS NOT NULL); -- [5, 7]
-
map_filter
(map<K, V>, function<K, V, boolean>) → MAP<K,V> Constructs a map from those entries of
map
for whichfunction
returns true:SELECT map_filter(MAP(ARRAY[], ARRAY[]), (k, v) -> true); -- {} SELECT map_filter(MAP(ARRAY[10, 20, 30], ARRAY['a', NULL, 'c']), (k, v) -> v IS NOT NULL); -- {10 -> a, 30 -> c} SELECT map_filter(MAP(ARRAY['k1', 'k2', 'k3'], ARRAY[20, 3, 15]), (k, v) -> v > 10); -- {k1 -> 20, k3 -> 15}
-
reduce
(array<T>, initialState S, inputFunction<S, T, S>, outputFunction<S, R>) → R Returns a single value reduced from
array
.inputFunction
will be invoked for each element inarray
in order. In addition to taking the element,inputFunction
takes the current state, initiallyinitialState
, and returns the new state.outputFunction
will be invoked to turn the final state into the result value. It may be identity function (i -> i
). For example:SELECT reduce(ARRAY [], 0, (s, x) -> s + x, s -> s); -- 0 SELECT reduce(ARRAY [5, 20, 50], 0, (s, x) -> s + x, s -> s); -- 75 SELECT reduce(ARRAY [5, 20, NULL, 50], 0, (s, x) -> s + x, s -> s); -- NULL SELECT reduce(ARRAY [5, 20, NULL, 50], 0, (s, x) -> s + COALESCE(x, 0), s -> s); -- 75 SELECT reduce(ARRAY [5, 20, NULL, 50], 0, (s, x) -> IF(x IS NULL, s, s + x), s -> s); -- 75 SELECT reduce(ARRAY [2147483647, 1], CAST (0 AS BIGINT), (s, x) -> s + x, s -> s); -- 2147483648 SELECT reduce(ARRAY [5, 6, 10, 20], -- calculates arithmetic average: 10.25 CAST(ROW(0.0, 0) AS ROW(sum DOUBLE, count INTEGER)), (s, x) -> CAST(ROW(x + s.sum, s.count + 1) AS ROW(sum DOUBLE, count INTEGER)), s -> IF(s.count = 0, NULL, s.sum / s.count));
-
transform
(array<T>, function<T, U>) → ARRAY<U> Returns an array that applies
function
to each element ofarray
:SELECT transform(ARRAY [], x -> x + 1); -- [] SELECT transform(ARRAY [5, 6], x -> x + 1); -- [6, 7] SELECT transform(ARRAY [5, NULL, 6], x -> COALESCE(x, 0) + 1); -- [6, 1, 7] SELECT transform(ARRAY ['x', 'abc', 'z'], x -> x || '0'); -- ['x0', 'abc0', 'z0'] SELECT transform(ARRAY [ARRAY [1, NULL, 2], ARRAY[3, NULL]], a -> filter(a, x -> x IS NOT NULL)); -- [[1, 2], [3]]
-
transform_keys
(map<K1, V>, function<K1, V, K2>) → MAP<K2,V> Returns a map that applies
function
to each entry ofmap
and transforms the keys:SELECT transform_keys(MAP(ARRAY[], ARRAY[]), (k, v) -> k + 1); -- {} SELECT transform_keys(MAP(ARRAY [1, 2, 3], ARRAY ['a', 'b', 'c']), (k, v) -> k + 1); -- {2 -> a, 3 -> b, 4 -> c} SELECT transform_keys(MAP(ARRAY ['a', 'b', 'c'], ARRAY [1, 2, 3]), (k, v) -> v * v); -- {1 -> 1, 4 -> 2, 9 -> 3} SELECT transform_keys(MAP(ARRAY ['a', 'b'], ARRAY [1, 2]), (k, v) -> k || CAST(v as VARCHAR)); -- {a1 -> 1, b2 -> 2} SELECT transform_keys(MAP(ARRAY [1, 2], ARRAY [1.0, 1.4]), -- {one -> 1.0, two -> 1.4} (k, v) -> MAP(ARRAY[1, 2], ARRAY['one', 'two'])[k]);
-
transform_values
(map<K, V1>, function<K, V1, V2>) → MAP<K, V2> Returns a map that applies
function
to each entry ofmap
and transforms the values:SELECT transform_values(MAP(ARRAY[], ARRAY[]), (k, v) -> v + 1); -- {} SELECT transform_values(MAP(ARRAY [1, 2, 3], ARRAY [10, 20, 30]), (k, v) -> v + 1); -- {1 -> 11, 2 -> 22, 3 -> 33} SELECT transform_values(MAP(ARRAY [1, 2, 3], ARRAY ['a', 'b', 'c']), (k, v) -> k * k); -- {1 -> 1, 2 -> 4, 3 -> 9} SELECT transform_values(MAP(ARRAY ['a', 'b'], ARRAY [1, 2]), (k, v) -> k || CAST(v as VARCHAR)); -- {a -> a1, b -> b2} SELECT transform_values(MAP(ARRAY [1, 2], ARRAY [1.0, 1.4]), -- {1 -> one_1.0, 2 -> two_1.4} (k, v) -> MAP(ARRAY[1, 2], ARRAY['one', 'two'])[k] || '_' || CAST(v AS VARCHAR));
-
zip_with
(array<T>, array<U>, function<T, U, R>) → array<R> Merges the two given arrays, element-wise, into a single array using
function
. Both arrays must have the same length:SELECT zip_with(ARRAY[1, 3, 5], ARRAY['a', 'b', 'c'], (x, y) -> (y, x)); -- [ROW('a', 1), ROW('b', 3), ROW('c', 5)] SELECT zip_with(ARRAY[1, 2], ARRAY[3, 4], (x, y) -> x + y); -- [4, 6] SELECT zip_with(ARRAY['a', 'b', 'c'], ARRAY['d', 'e', 'f'], (x, y) -> concat(x, y)); -- ['ad', 'be', 'cf']