ARRAY sorts are part of the ISO/IEC 9075 SQL customary. The usual specifies tips on how to:
- Assemble arrays
- Nest knowledge into arrays (e.g. by the use of aggregation or subqueries)
- Unnest knowledge from arrays into tables
However it is vitally unopinionated on the subject of perform assist. The ISO/IEC 9075-2:2023(E) 6.47 TRIM_ARRAY perform, completely (utilizing which you’ll 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 not too long ago , there are a few extra trendy 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 may 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 Area arrayFilter(
Area array,
Function1 tremendous Area, ? 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 correct kind, as all the time with jOOQ.
The results of such an expression may 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 model syntax, the perform can simply be emulated utilizing a subquery that unnests the array, applies a WHERE clause similar 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 manner when the array isn’t only a static array literal, however an array expression, e.g. TABLE.ARRAY_FIELD.
Associated capabilities embrace:
