Manu Manu - 1 month ago 13
SQL Question

Strange translation of jOOQ query for array contains function

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

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, 
    Field<? extends C> right
) {
    return DSL.condition("{0} @> {1}", left, right);
}

... which you can then use as such:

contains(c.MYOPTIONS, DSL.val(new String[] { "option1" }))