PostHole
Compose Login
You are browsing eu.zone1 in read-only mode. Log in to participate.
rss-bridge 2025-03-27T13:04:44+00:00

When SQL Meets Lambda Expressions

ARRAY types are a part of the ISO/IEC 9075 SQL standard. The standard specifies how to: 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 … Continue reading When SQL Meets Lambda Expressions →

When SQL Meets Lambda Expressions

Posted on March 27, 2025 by lukaseder

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:

Like this:

Like Loading...

###
Published by lukaseder

I made jOOQ View all posts by lukaseder

Original source

Reply