Field::contains should implement PostgreSQL @> also for JSONB types

[saibotma]

Use case

@> can be GIN-Indexed and thus useful to quickly lookup if a key-value pair exists.

Possible solution

No response

Possible workarounds

No response

jOOQ Version

jOOQ Community 3.17.5

Database product and version

Postgres 15 and older

Java Version

No response

OS Version

No response

JDBC driver name and version (include name if unofficial driver)

No response

[lukaseder]

Thanks for your report. We already support it via Field.contains(), though only for arrays. Did you have something else in mind?

[saibotma]

I also noticed that Field.contains() exists. Should've checked more carefully before creating an issue 😅
I want to use it with JSON/JSONB as shown here.

Using it on Field<JSONB> compiles at least. I did not execute the query yet, though.

[lukaseder]

Makes sense, thanks. Yes it compiles but generates a string contains implementation, i.e. arg1 like '%' || arg2 || '%'. It certainly makes sense to retrofit this for JSON and JSONB

[saibotma]

Thanks for the information. That means the current workaround is to use jOOQ's plain SQL support?

[lukaseder]

Yes

[lukaseder]

Will implement native support only for now. Emulations could be done separately: #14264, though they're probably not so simple.

[lukaseder]

Fixed for jOOQ 3.18.0

[saibotma]

Wow, that was fast!