如何在Jooq中查询元组上的非重复数组聚合？

问：

如何在JooQ中正确表达此SQL查询？我确实知道arrayAggDistinct（）DSL方法，但是我无法表达行类型。

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;

编辑我发现，这创建了正确的查询

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())

但 rowField 方法被标记为已弃用，并且建议的 rowN 替代项不是在 arrayAggDistinct 中使用的正确类型。