When SQL Meets Lambda Expressions

0
250

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:

Sponsored
Sponsored
Sponsored
Search
Virtualbook
CDN FREE
Categories
Read More
Art
# 太阳能热解设施将废弃塑料转化为燃料
太阳能, 热解, 废弃塑料, 可再生能源, 环保技术, 燃料转化, 绿色科技, 可持续发展, 塑料污染...
By Juan Han 2025-08-23 07:05:19 1 43
Other
Streamline Your Education with Online Class Assistance
  As nursing education keeps to adapt in complexity and class, courses like NURS FPX 8008...
By Jon Smit 2025-06-27 08:37:45 1 233
Art
2025: Wie das Standalone VR-Headset die Klassen transformiert
VR-Technologie, Bildung, Standalone VR-Headset, digitale Klassenzimmer, Zukunft der Bildung,...
By Thea Laura 2025-08-07 13:05:33 1 38
Art
8 Tendências Icônicas de Moda dos Últimos 50 Anos: Uma Reflexão sobre a Mudança
tendências de moda, moda icônica, silhuetas de gênero neutro, moda dos anos 70, moda dos anos 80,...
By Fernando Manuel 2025-08-02 14:49:14 1 45
Art
UN Plastik Antlaşması Görüşmeleri Yine Başarısızlıkla Sonuçlandı
## Giriş Son dönemde yapılan Birleşmiş Milletler (BM) plastik antlaşması görüşmeleri, bir kez...
By Ece Kübra 2025-08-16 21:05:26 1 48
Sponsored
Virtuala FansOnly https://virtuala.site