How to query distinct array aggregate over tuple in Jooq?

Question

how do I express this SQL query in JooQ correctly? I do know about the arrayAggDistinct() DSL method, but I have trouble to express the row type.

SELECT
    u.user_id,
    ARRAY_AGG(DISTINCT (g.group_id, g.group_name)) AS grouped_groups,
    ARRAY_AGG(DISTINCT (a.authority_id, a.authority)) AS grouped_authorities
FROM users u
         LEFT JOIN group_members gm ON u.user_id = gm.user_id
         LEFT JOIN groups g ON gm.group_id = g.group_id
         LEFT JOIN group_authorities ga ON g.group_id = ga.group_id
         LEFT JOIN authorities a ON ga.authority_id = a.authority_id
GROUP BY u.user_id;

EDIT I found, that this creates the correct query

val groupRow: Row2<Long?, String?> = row(GROUPS.GROUP_ID, GROUPS.GROUP_NAME)
val groupsField: Field<Record2<Long?, String?>> = PostgresDSL.rowField(groupRow)
val authorityRow: Row2<Long?, String?> = row(AUTHORITIES.AUTHORITY_ID, AUTHORITIES.AUTHORITY)
val authoritiesField: Field<Record2<Long?, String?>> = PostgresDSL.rowField(authorityRow)
val jooqQuery = PostgresDSL.select(
    *USERS.fields(),
    arrayAggDistinct(groupsField).`as`("groups"),
    arrayAggDistinct(authoritiesField).`as`("authorities"),
).from(USERS)
    .leftJoin(GROUP_MEMBERS).on(USERS.USER_ID.eq(GROUP_MEMBERS.USER_ID))
    .leftJoin(GROUPS).on(GROUP_MEMBERS.GROUP_ID.eq(GROUPS.GROUP_ID))
    .leftJoin(GROUP_AUTHORITIES).on(GROUPS.GROUP_ID.eq(GROUP_AUTHORITIES.GROUP_ID))
    .leftJoin(AUTHORITIES).on(GROUP_AUTHORITIES.AUTHORITY_ID.eq(AUTHORITIES.AUTHORITY_ID))
.where(USERS.MAIL.eq(mail))
.groupBy(*USERS.fields())

but the rowField method is marked deprecated and the suggested rowN alternative is not the right type to be used in arrayAggDistinct.

Answer 1

There are numerous issues requesting that Row[N] types, which extend SelectField, but not Field, be accepted more generally throughout the API, see e.g.:

• #9123 Add support for min(Row[N]) and max(Row[N])
• #10701 Add support for ARRAY_AGG()
• #11998 Review API to accept SelectField instead of Field more often

The main reason why these aren't widely supported by jOOQ is that it would be very difficult to convey to users that these things tend to work in PostgreSQL but hardly anywhere else, so e.g. Oracle, MySQL, SQL Server users would be constantly disappointed by problems arising from what the jOOQ API seemingly promises but doesn't deliver on.

Until there's a way to emulate these things thoroughly, your workaround using PostgresDSL (or your own plain SQL templates) will have to do.