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:

• ARRAY_MAP
• ARRAY_ALL_MATCH
• ARRAY_ANY_MATCH
• ARRAY_NONE_MATCH

I made jOOQ View all posts by lukaseder