Criteria builder for postgres jsonB ? operator

Question

I have a table synonyms_table with a jsonb column synonym which is a json array. Entries in this column will look something like this : ["good","nice","awesome"]

I have a query select * from synonyms_table where synonym::jsonb ? 'good';

I want to create this using CriteriaBuilder. How can I do that? Any help will be deeply appreciated.

Specification specification = (root, query, criteriaBuilder) -> {
    return criteriaBuilder.equal(criteriaBuilder.function("jsonb_extract_path_text", String.class, root.<String>get("synonym"), criteriaBuilder.literal("synonym")), 'good');
};

But this is not working Getting following error:

Caused by: org.postgresql.util.PSQLException: ERROR: function jsonb_extract_path_text(character varying, character varying) does not exist

ALex · Accepted Answer · 2024-01-12 11:56:49Z

1

I'm just assuming but i think the problem is that your column is a plain string array and not an array or objects or a {..} json.

I've checked the jsonb_array_elements function but the only example i could find was those of arrays having complex objects in them.

You either look for more example of this jsonb_array_elements and see if anything help.

Or

If you transform the column in a simple varchar[]

you could do something like this https://stackoverflow.com/a/24695695/3018285

edited Jan 12, 2024 at 11:56

answered Jan 11, 2024 at 10:44

ALex

8301 gold badge8 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

Criteria builder for postgres jsonB ? operator

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