When SQL Meets Lambda Expressions

A few modern SQL dialects have started introducing lambda expressions
ARRAY
types are a part of the ISO/IEC 9075 SQL standard. The standard specifies how to:
- Construct arrays
- Nest data into arrays (e.g. by means of aggregation or subqueries)
- Unnest data from arrays into tables
But it is very unopinionated when it comes to function support. The ISO/IEC 9075-2:2023(E) 6.47 <array value expression>
specifies concatenation of arrays, whereas the 6.48 <array value function>
section lists a not extremely useful TRIM_ARRAY
function, exclusively (using which you can remove the last N
elements of an array, something I have yet to encounter a use-case for)
The implementations fare better. Many of them have a ton of useful functions, and since recently , there are a couple of more modern SQL dialects out there who have started experimenting with lambda expressions in SQL, when working with ARRAY
types. These dialects include, mostly:
- ClickHouse
- Databricks
- DuckDB
- Snowflake
- Trino
Take the ARRAY_FILTER
function, for example. With jOOQ you might write something like this, where you apply a filter that keeps only even numbers in an array:
arrayFilter(array(1, 2, 2, 3), e -> e.mod(2).eq(0))
The corresponding jOOQ API is simply:
public static <T> Field<T[]> arrayFilter( Field<T[]> array, Function1<? super Field<T>, ? extends Condition> predicate ) { ... }
So, jOOQ can simply map Java (or Kotlin, Scala) lambda expressions to a SQL lambda expression, without any magic. You just construct an expression of the right type, as always with jOOQ.
The result of such an expression might look like this:
+--------------+ | array_filter | +--------------+ | [ 2, 2 ] | +--------------+
In DuckDB, for example, the above is translated to:
array_filter( ARRAY[1, 2, 2, 3], e -> (e % 2) = 0 )
If the dialect doesn’t support the lambda style syntax, the function can easily be emulated using a subquery that unnests the array, applies a WHERE
clause corresponding to the lambda, and collects the results back into an array, e.g. in PostgreSQL:
( SELECT coalesce( array_agg(e), CAST(ARRAY[] AS int[]) ) FROM UNNEST(ARRAY[1, 2, 2, 3]) t (e) WHERE mod(e, 2) = 0 )
This works just the same way when the array isn’t just a static array literal, but an array expression, e.g. TABLE.ARRAY_FIELD
.
Related functions include:
- Live Stream
- Causes
- Crafts
- Dance
- Drinks
- Film
- Fitness
- Food
- Games
- Gardening
- Health
- Home
- Literature
- Music
- Networking
- Other
- Party
- Religion
- Shopping
- Sports
- Theater
- Wellness
- Art
- Life
- Coding