JOOQ/Oracle listAgg with distinct-CodePudding

I am struggling to build the following query with JOOQ DSL - as I can't bring in the distinct keyword into the generated query from JOOQ. How would I accomplish this? The ListAgg-Object created behind the listAgg-method takes a flag for distinct as value, but since ListAgg is package-private, I can't access it. Is there way to make use of this flag or any other way to accomplish this in general?

Oracle SQL

select distinct DATA.*, LISTAGG(distinct ORGANISATIONS.LABEL, ', ') WITHIN GROUP (ORDER BY DATA.ID),
        from DATA
            join ORGANISATIONS
            on DATA.ORG_ID= ORGANISATIONS.ID
        group by DATA.fields()

JOOQ

create.selectDistinct(DATA.asterisk(), listAgg(ORGANISATIONS.LABEL, ", " ).withinGroupOrderBy(DATA.ID).as("ORG_LABELS"))
                .from(DATA)
                .join(ORGANISATIONS).on(DATA.ORG_ID.eq(ORGANISATIONS.ID))
                .groupBy(DATA.fields())
                .fetch()

Btw, the Table-names have been simplified.

Any input is much appreciated.

CodePudding user response：

The aggregate function is not yet supported, see: https://github.com/jOOQ/jOOQ/issues/7104

You can build your own using plain SQL templating:

static Field<String> listAggDistinct(
    Field<String> field, 
    String separator, 
    OrderField<?> orderBy
) {
    return DSL.field(
        "listagg(distinct {0}, {1}) within group (order by {2})",
        field.getDataType(),
        field,
        DSL.inline(separator),
        orderBy
    );
}