New versions: Dev (3.16) | Latest (3.15)

ARRAY value constructor

Applies to ✅ 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!

The jOOQ Logo