Howto expose a native SQL function as a predicate

Question

I have a table in my database which stores a list of string values as a jsonb field.

create table rabbits_json (
  rabbit_id bigserial primary key, 
  name text, 
  info jsonb not null
);

insert into rabbits_json (name, info) values
  ('Henry','["lettuce","carrots"]'),
  ('Herald','["carrots","zucchini"]'),
  ('Helen','["lettuce","cheese"]');

I want to filter my rows checking if info contains a given value. In SQL, I would use ? operator:

select * from rabbits_json where info ? 'carrots';

If my googling skills are fine today, I believe that this is not implemented yet in JOOQ: https://github.com/jOOQ/jOOQ/issues/9997

How can I use a native predicate in my query to write an equivalent query in JOOQ?

score 1 · Accepted Answer · answered Oct 14 '20 at 18:50

For anything that's not supported natively in jOOQ, you should use plain SQL templating, e.g.

Condition condition = DSL.condition("{0} ? {1}", RABBITS_JSON.INFO, DSL.val("carrots"));

Unfortunately, in this specific case, you will run into this issue here. With JDBC PreparedStatement, you still cannot use ? for other usages than bind variables. As a workaround, you can:

Use Settings.statementType == STATIC_STATEMENT to prevent using a PreparedStatement in this case
Use the jsonb_exists_any function (not indexable) instead of ?, see https://stackoverflow.com/a/38370973/521799

Howto expose a native SQL function as a predicate

1 Answers1