When SQL Meets Lambda Expressions

0
258

A few modern SQL dialects have started introducing lambda expressions

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:

Sponsor
Sponsor
Sponsor
Căutare
Virtualbook
CDN FREE
Categorii
Citeste mai mult
Alte
Opportunities in the Unmanned Electronic Warfare Market
Market OverviewAccording To The Research Report Published By Polaris Market Research, The Global...
By MAYUR YADAV 2025-09-25 10:45:31 0 27
Gardening
Fred C. Gloeckner Foundation Grants for 2020
Fred C. Gloeckner Foundation Grants for 2020The Fred C. Gloeckner Foundation, Inc. Officers and...
By Martina Lukačić 2025-05-31 19:53:55 0 225
Art
# Клавиатура с лентой: Зачем нам это нужно?
Клавиатура, лента, звук, Меллотрон, 1960-е, музыка, ностальгия, технологии, звукозапись ##...
By إدوارد ليونيد 2025-08-04 16:05:22 1 38
Art
Rad Power Bikes RadRunner Max İncelemesi: Daha Hızlı ve Daha Güvenli!
Rad Power Bikes, RadRunner Max ile bisiklet tutkunlarına heyecan verici bir yenilik sunuyor. Bu...
By Okan Ege 2025-08-23 00:05:17 1 59
Art
Gamescom-Manager "überrascht" von der Kritik lokaler Entwickler am Latam-Event
Gamescom, Latam-Event, Felix Falk, brasilianische Entwickler, Kritik, Missverständnis, Gamescom...
By Amelie Clara 2025-08-19 08:05:23 1 59
Sponsor
Virtuala FansOnly https://virtuala.site