Accessing sql-array item by in jooq

Question

How can I access item of array in JOOQ like in this sql-query?

SELECT (ARRAY_AGG(id))[1]
FROM entities;

Something like that:

dsl().select(arrayAgg(ENTITIES.ID).get(1)).from(ENTITIES).fetch();

Or just access to first item:

dsl().select(arrayAgg(ENTITIES.ID).head()).from(ENTITIES).fetch();

I know about string solution, but it's not good:

field("(ARRAY_AGG(id))[1]")

Answer 1

As of jOOQ 3.8, this is not possible out of the box. There's a pending feature request for array element access: https://github.com/jOOQ/jOOQ/issues/229

Why Java cannot offer this functionality

One of the problems is API design here. A post-fix method as you suggested cannot return the appropriate type. Consider this API design:

interface Field<T> {
    Field<???> get(int index); // What type to return here?
}

In Java, it is not possible to put any additional constraints on the class's generic type parameter <T> on a per-method level, as in the following hypothetical code using something like a "partial method" (i.e. a method that is available only on some subtypes of Field<T>, namely those where T is an array):

interface Field<T> {
    @If(T instanceof E[])
    Field<E> get(int index);
}

Workaround

But you can write your own workaround. For instance:

@SuppressWarnings("unchecked")
public static <T> Field<T> elementAt(Field<T[]> arrayField, int index) {
    return DSL.field("{0}[{1}]", 
        (Class<T>) arrayField.getType().getComponentType(),
        arrayField, DSL.inline(index)
    );
}

You can now use it as such:

dsl().select(elementAt(arrayAgg(ENTITIES.ID), 1))
     .from(ENTITIES)
     .fetch();