Strange translation of jOOQ query for array contains function

Question

I have the following type in my PostgreSQL database :

myoptions text[]

I use a jOOQ converter so that I have a Set as corresponding type in my record :

Set<String> myoptions

In my query I have the following Condition :

c.MYOPTIONS.contains(Sets.newHashSet("option1"))

which is translated in SQL like this :

cast("c"."myoptions" as varchar) like ('%' || '[option1]' || '%') escape '!'

Is it the normal behavior ?

I would like to have something like :

c.myoptions @> ARRAY['option1']

or

'option1' = ANY(c.myoptions)

Thanks in advance for your help

Answer 1

jOOQ currently (as of version 3.8) does not recognise your custom data type as still being an array data type in PostgreSQL, which is why the Field.contains() default behaviour kicks in - i.e. the one that treats all values as strings.

I've created feature request #5602 for this. As a workaround, you might need to roll your own using plain SQL:

public static <T, C extends Collection<T>> Condition contains(
    Field<? extends C> left, 
    C right
) {
    return DSL.condition("{0} @> {1}::text[]", left, DSL.val(right, left.getDataType()));
}

... which you can then use as such:

contains(c.MYOPTIONS, Sets.newHashSet("option1"))

Answer 2

Please try following solution. Worked in my case.

select.where(ARRAY_FIELD.contains(DSL.cast(DSL.array(VALUE), ARRAY_FIELD.getDataType())));