0

I've been tinkering a bit with criteria builder queries to figure out how to check if an array of strings contains a/or many values, but the tricky thing is that this array is inside a jsonb column called data. I don't know how to achieve that using CriteriaBuilder queries. I'll paste my psql script below:

SELECT * FROM myScheme.myTable t where t.data->>'categorys' @> '17';

I have done something like this with criteria, but it isn't working

if (Objects.nonNull(filter.getCategorias())) {
                predicates.add(builder.and(builder.function("jsonb_extract_path_text",
                                String.class,
                                root.get("data"),
                                builder.literal("categorys"))
                        .in(filter.getCategorias())));

        }

I tried researching how to implement the contains operator @> but didn't find anything.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Eduardojls
  • 11
  • 5

1 Answers1

0

The jsonb "contains" operator @> expects the type jsonb to the left. So use the -> operator to extract the 'categorys' object as jsonb instead of ->> which returns text:

SELECT * FROM myScheme.myTable t WHERE t.data -> 'categorys' @> '"17"';

But that's just fixing the error. You should really use:

SELECT * FROM myScheme.myTable t WHERE t.data @> '{"categorys":["17"]}';

fiddle

This can also tap into an index on the jsonb column. See:

Double quotes around "17" if it's indeed an array of strings. Remove the quotes for numeric data.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for sharing your knowledge, I'll take it to improve at queries. I really appreciate it. By the way, do you know how to do exactly the same query with Criteria Builder (Java) ? – Eduardojls Oct 14 '22 at 12:51