jOOQ to select distinct array aggregate in PostgreSQL

Question

I am trying to get jOOQ to emit this (without much luck):

SELECT array_agg(DISTINCT my_field) FROM ...

I ended up using a plain-SQL field.

Is there a way to do this that I am missing?

If not, would it make sense to add a .distinct() method to Field that can be used with any aggregate function? Or a DSL.distinct(Field) that creates a Field, rather than a SelectStep - not sure if that makes a difference.

This currently seems to be accomplished by creating separate DSL methods for each aggregate: countDistinct, groupConcatDistinct, min/max/sum/avgDistinct, etc.

Thanks

Lukas Eder · Accepted Answer · 2017-09-19 10:08:45Z

1

There will be a DSL.arrayAggDistinct() function available from jOOQ 3.10 onwards: https://github.com/jOOQ/jOOQ/issues/6281

Until then, simply use the plain SQL API as a workaround:

public static <T> Field<T[]> arrayAggDistinct(Field<T> myField) {
    return DSL.field("array_agg(distinct {0})", myField.getArrayDataType(), myField);
}

answered Sep 19, 2017 at 10:08

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

jOOQ to select distinct array aggregate in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related