Postgres Enum Arrays in JOOQ

When you are trying to perform array operations in Postgres, you may encounter some of the following array operations:

• @> Contains All of the elements of an array are present within the other
• <@ Is contained by Whether all of the elements in one array are contained within another
• && Overlap when two arrays contain at least one or more common elements

The following will outline how you can use these operations on Array column types, even on enumerated or custom types.

JOOQ Array Overlap Example

Below is an example of how you can check to see if an array contains any of the items of another array. This is typically performed using the overlap operator &&.

In the following code, we have an enumerated array column called color and we can use JOOQ’s arrayOverlap method to retrieve records which may contain the colors blue or green.

import static org.jooq.util.postgres.PostgresDSL.arrayOverlap;
import static org.jooq.impl.DSL.*;

...

Color[] colors = new Color[] { Color.BLUE, Color.GREEN };

dsl.select().from(MY_TABLE)
    .where(arrayOverlap(MY_TABLE.COLOR, colors))

The resulting sql from this overlap call would look as follows:

... where("public"."my_table"."color" && cast('{"BLUE","GREEN"}' as "public"."color"[]))

If you wanted to do this with raw SQL you could query the array like so:

.where("public.my_table.color && {0}", val(colors).cast(Color[].class))

Note: arrayOverlap also works on Aurora Postgres, CockroachDB, and YugabyteDB.

JOOQ Array Contains Example

The Postgres Array Contains command translates to the @> operator which will satisfy the condition if all of each array match. The order of each array does not matter.

import static org.jooq.impl.DSL.*;

...

Color[] colors = new Color[] { Color.BLUE, Color.GREEN };

dsl.select().from(MY_TABLE)
    .where(MY_TABLE.COLOR.contains(cast(array(colors), MY_TABLE.COLOR.getDataType())))
    ...

JOOQ Array is Contained By <@ operator

This operator doesn’t appear to match to any existing methods in Jooq, nonetheless we can implement it with a little bit of SQL crafting like so.

import static org.jooq.impl.DSL.*;

dsl.select().from(MY_TABLE)
.where("my_table.color <@ {0}", val(new String[] {"GREEN", "BLUE"}).cast(Color[].class))