ARRAY value constructor
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The ARRAY value constructor allows for collecting the results of a single-column, non scalar subquery into a single nested collection value with ARRAY data type semantics (ordinals are defined on elements).
For example, let's find:
- All authors.
- The languages in which that author has their books published.
- The book stores at which that author's books are available.
This can be done in a single query:
SELECT
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
ARRAY(
SELECT DISTINCT LANGUAGE.CD
FROM BOOK
JOIN LANGUAGE ON BOOK.LANGUAGE_ID = LANGUAGE.ID
) AS BOOKS,
ARRAY(
SELECT DISTINCT BOOK_TO_BOOK_STORE.BOOK_STORE_NAME
FROM BOOK_TO_BOOK_STORE
JOIN BOOK ON BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
WHERE BOOK.AUTHOR_ID = AUTHOR.ID
) AS BOOK_STORES
FROM AUTHOR
ORDER BY AUTHOR.ID
var result = create.select(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
array(
selectDistinct(BOOK.language().CD)
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
).as("books"),
array(
selectDistinct(BOOK_TO_BOOK_STORE.BOOK_STORE_NAME)
.from(BOOK_TO_BOOK_STORE)
.where(BOOK_TO_BOOK_STORE.tBook().AUTHOR_ID.eq(AUTHOR.ID))
).as("book_stores"))
.from(AUTHOR)
.orderBy(AUTHOR.ID)
.fetch();
The above var result is inferred to:
Result<Record4<String, String, String[], String[]>> result =
The result of the above query may look like this:
+----------+---------+--------+--------------------------------------------------+ |first_name|last_name|books |book_stores | +----------+---------+--------+--------------------------------------------------+ |George |Orwell |[en] |[Ex Libris, Orell Füssli] | |Paulo |Coelho |[de, pt]|[Buchhandlung im Volkshaus, Ex Libris, Orell Fü...| +----------+---------+--------+--------------------------------------------------+
Feedback
Do you have any feedback about this page? We'd love to hear it!