Using unnest as a field rather than a table in jOOQ

Question

This is the query I am trying to run in PostgreSQL:

SELECT * FROM message WHERE id IN (
    SELECT unnest(message_ids) "mid"
        FROM session_messages WHERE session_id = '?' ORDER BY "mid" ASC
);

However, I am not able do something:

create.selectFrom(Tables.MESSAGE).where(Tables.MESSAGE.ID.in(
    create.select(DSL.unnest(..))

Because DSL.unnest is a Table<?>, which makes sense since it is trying to take a List-like object (mostly a literal) and convert it to table.

I have a feeling that I need to find a way to wrap the function around my field name, but I have no clue as to how to proceed.

NOTE. The field message_ids is of type bigint[].

EDIT

So, this is how I am doing it now, and it works exactly as expected, but I am not sure if this is the best way to do it:

Field<Long> unnestMessageIdField = DSL.field(
                "unnest(" +  SESSION_MESSAGES.MESSAGE_IDS.getName() + ")",
                Long.class)
        .as("mid");

Field<Long> messageIdField = DSL.field("mid", Long.class);

MESSAGE.ID.in(
        ctx.select(messageIdField).from(
            ctx.select(unnestMessageIdField)
               .from(Tables.CHAT_SESSION_MESSAGES)
                    .where(Tables.CHAT_SESSION_MESSAGES.SESSION_ID.eq(sessionId))
            )
            .where(condition)
)

EDIT2

After going through the code on https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/DSL.java I guess the right way to do this would be:

DSL.function("unnest", SQLDataTypes.BIGINT.getArrayType(), SESSION_MESSAGES.MESSAGE_IDS)

EDIT3

Since as always lukas is here for my jOOQ woes, I am going to capitalize on this :)

Trying to generalize this function, in a signature of sort

public <T> Field<T> unnest(Field<T[]> arrayField) {
    return DSL.function("unnest", <??>, arrayField);
}

I don't know how I can fetch the datatype. There seems to be a way to get DataType<T[]> from DataType<T> using DataType::getArrayDataType(), but the reverse is not possible. There is this class I found ArrayDataType, but it seems to be package-private, so I cannot use it (and even if I could, it does not expose the field elementType).

Answer 1

Old PostgreSQL versions had this funky idea that it is OK to produce a table from within the SELECT clause, and expand it into the "outer" table, as if it were declared in the FROM clause. That is a very obscure PostgreSQL legacy, and this example is a good chance to get rid of it, and use LATERAL instead. Your query is equivalent to this one:

SELECT * 
FROM message 
WHERE id IN (
    SELECT "mid"
    FROM session_messages 
    CROSS JOIN LATERAL unnest(message_ids) AS t("mid")
    WHERE session_id = '?' 
);

This can be translated to jOOQ much more easily as:

DSL.using(configuration)
   .select()
   .from(MESSAGE)
   .where(MESSAGE.ID).in(
        select(field(name("mid"), MESSAGE.ID.getDataType()))
       .from(SESSION_MESSAGES)
       .crossJoin(lateral(unnest(SESSION_MESSAGES.MESSAGE_IDS)).as("t", "mid"))
       .where(SESSION_MESSAGES.SESSION_ID.eq("'?'"))
   )

Answer 2

The Edit3 in the question is quite close to a decent solution for this problem. We can create a custom generic unnest method for jOOQ which accepts Field and use it in jOOQ query normally.

Helper method:

public static <T> Field<T> unnest(Field<T[]> field) {
    var type = (Class<T>) field.getType().getComponentType();
    return DSL.function("unnest", type, field);
}

Usage:

public void query(SessionId sessionId) {

    var field = unnest(SESSION_MESSAGES.MESSAGE_IDS, UUID.class);

    dsl.select().from(MESSAGE).where(
            MESSAGE.ID.in(
                dsl.select(field).from(SESSION_MESSAGES)
                .where(SESSION_MESSAGES.SESSION_ID.eq(sessionId.id))
                .orderBy(field)
            )
    );

}