New versions: Dev (3.16) | Latest (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 
  T_AUTHOR.ID, 
  ARRAY(
    SELECT ID 
    FROM T_BOOK
    WHERE T_BOOK.AUTHOR_ID = T_AUTHOR.ID
  )
FROM T_AUTHOR
create.select(
  T_AUTHOR.ID,
  array(
    select(T_BOOK.ID)
    .from(T_BOOK)
    .where(T_BOOK.AUTHOR_ID.eq(T_AUTHOR.ID))))
  .from(T_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, POSTGRES
ARRAY(SELECT 1)

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

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

-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DB2, DERBY, EXASOL, FIREBIRD, HANA, IGNITE, INFORMIX, MARIADB, MEMSQL, MYSQL, ORACLE, 
-- REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, VERTICA
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.16, 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