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, 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)
)
-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DB2, DERBY, DUCKDB, EXASOL, FIREBIRD, HANA, INFORMIX, MARIADB, MEMSQL, MYSQL,
-- ORACLE, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, TRINO, VERTICA
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!