Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15

ARRAY constructor from subquery

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

In order to construct an ad-hoc ARRAY type from a subquery, the ARRAY constructor can be used.

SELECT
  AUTHOR.ID,
  ARRAY(
    SELECT ID
    FROM BOOK
    WHERE BOOK.AUTHOR_ID = AUTHOR.ID
  )
FROM AUTHOR
create.select(
  AUTHOR.ID,
  array(
    select(BOOK.ID)
    .from(BOOK)
    .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))))
  .from(AUTHOR)
  .fetch();

Unlike ARRAY_AGG, this ARRAY constructor does not act as an aggregate function, and thus does not produce aggregate semantics, such as requiring an explicit (or producing an implicit) GROUP BY clause.

The result would look like this:

+----+----------+
| ID |          |
+----+----------+
|  1 | [ 1, 2 ] |
|  2 | [ 3, 4 ] |
+----+----------+

Dialect support

This example using jOOQ:

array(select(val(1)))

Translates to the following dialect specific expressions:

Aurora Postgres, CockroachDB, DuckDB, Postgres, YugabyteDB

ARRAY(
  SELECT 1
)

H2

(
  SELECT array_agg(t.c)
  FROM (
    SELECT 1
  ) t (c)
)

HSQLDB

ARRAY(
  SELECT 1
  FROM (VALUES(1)) AS dual(dual)
)

ASE, Access, Aurora MySQL, BigQuery, ClickHouse, DB2, Derby, Exasol, Firebird, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica

/* UNSUPPORTED */

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo