Using UNNEST with jOOQ

Question

I'm using PostgreSQL 9.4, Spring Boot 1.3.2 and jOOQ 3.7. I'd like to "jOOQify" the following query

SELECT id FROM users WHERE username IN (SELECT * FROM UNNEST(?))

so that I can run it with Spring's JdbcTemplate by passing a list or array (in this particular case, an List<String>). I've tried

DSLContext.select(
    USERS.ID
)
.from(
    USERS
)
.where(
    USERS.USERNAME.in(...)
)

but I can't figure out what to place in .... I tried something like

DSLContext.select(field("*")).from(unnest(myList))

but unfortunately the compiler doesn't like that. Is there any simple way I can achieve this?

Answer 1

The compiler doesn't like your attempt, because USERS.USERNAME is a Field<String>, and thus the in() method expects a Select<? extends Record1<String>>.

You can fix it as such:

select(field("*", String.class)).from(unnest(myList))

Or, perhaps a bit better:

select(field(name("v"), String.class)).from(unnest(myList).as("t", "v"))