Converting a SQL query to an ARRAY in PostgreSQL with jOOQ

Question

Let's say I have a query of the type:

SELECT * FROM users WHERE access_codes @>
    ARRAY[(SELECT access_code FROM provisional_access)];

How would I write this in jooq? The best I have done so far is:

ctx.selectFrom(Tables.USERS).where(Tables.USERS.ACCESS_CODES).contains(
    ?? ctx.select(Tables.PROVISIONAL_ACCESS.ACCESS_CODE).from(Tables.PROVISIONAL_ACCESS) ??
);

Lukas Eder · Accepted Answer · 2016-04-18 15:32:51Z

1

This currently isn't supported by jOOQ out of the box, but you can very easily implement a utility function using "plain SQL":

public static <T> Condition arrayContains(
    Field<T[]> left, Select<? extends Record1<T>> right
) {
    return DSL.condition("{0} @> ARRAY[({1})]", left, right);
}

answered Apr 18, 2016 at 15:32

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

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

Collectives™ on Stack Overflow

Converting a SQL query to an ARRAY in PostgreSQL with jOOQ

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