ARRAY sorts are part of the ISO/IEC 9075 SQL commonplace. The usual specifies find out how to:
- 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 relating to perform assist. 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, completely (utilizing which you’ll be able to take away the final N parts 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 capabilities, and since 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> Discipline<T[]> arrayFilter(
Discipline<T[]> array,
Function1<? tremendous Discipline<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 precise 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 assist the lambda fashion 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 capabilities embrace:
