|The jOOQ User Manual : SQL building : Column expressions : Aggregate functions : COLLECT||previous : next|
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
COLLECT() aggregate function is Oracle's vendor specific version of the standard SQL ARRAY_AGG function. It produces a structurally typed array, which is implemented behind the scenes as a nominally typed, system-generated array. It supports being used with an ORDER BY clause.
The following example is using an auxiliary data type and casting the
COLLECT() result to that type.
CREATE TYPE NUMBERS AS TABLE OF NUMBER(10); SELECT CAST(collect(ID ORDER BY ID) AS NUMBERS); FROM BOOK;
create.select( collect(BOOK.ID, NumbersRecord.class).orderBy(BOOK.ID)) .from(BOOK)
+--------------+ | collect | +--------------+ | [1, 2, 3, 4] | +--------------+