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?

Lukas Eder · Accepted Answer · 2020-10-14 18:50:05Z

1

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

answered Oct 14, 2020 at 18:50

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

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

Collectives™ on Stack Overflow

Howto expose a native SQL function as a predicate

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related