ARRAY sorts are part of the ISO/IEC 9075 SQL commonplace. The usual specifies :
- Assemble arrays
- Nest knowledge into arrays (e.g. by way of aggregation or subqueries)
- Unnest knowledge from arrays into tables
However it is vitally unopinionated in relation to perform help. The ISO/IEC 9075-2:2023(E) 6.47 <array worth expression> specifies concatenation of arrays, whereas the 6.48 <array worth perform> part lists a not extraordinarily helpful TRIM_ARRAY perform, solely (utilizing which you’ll be able to take away the final N components of an array, one thing I’ve but to come across a use-case for)
The implementations fare higher. A lot of them have a ton of helpful features, and since just lately , there are a few extra fashionable SQL dialects on the market who’ve began experimenting with lambda expressions in SQL, when working with ARRAY sorts. These dialects embrace, principally:
- ClickHouse
- Databricks
- DuckDB
- Snowflake
- Trino
Take the ARRAY_FILTER perform, for instance. With jOOQ you would possibly write one thing like this, the place you apply a filter that retains solely even numbers in an array:
arrayFilter(array(1, 2, 2, 3), e -> e.mod(2).eq(0))
The corresponding jOOQ API is solely:
public static <T> Subject<T[]> arrayFilter(
Subject<T[]> array,
Function1<? tremendous Subject<T>, ? extends Situation> predicate
) { ... }
So, jOOQ can merely map Java (or Kotlin, Scala) lambda expressions to a SQL lambda expression, with none magic. You simply assemble an expression of the appropriate kind, as at all times with jOOQ.
The results of such an expression would possibly appear like this:
+--------------+
| array_filter |
+--------------+
| [ 2, 2 ] |
+--------------+
In DuckDB, for instance, the above is translated to:
array_filter(
ARRAY[1, 2, 2, 3],
e -> (e % 2) = 0
)
If the dialect doesn’t help the lambda model syntax, the perform can simply be emulated utilizing a subquery that unnests the array, applies a WHERE clause equivalent to the lambda, and collects the outcomes again 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 simply the identical approach when the array isn’t only a static array literal, however an array expression, e.g. TABLE.ARRAY_FIELD.
Associated features embrace:
