How use Hibernate Criteria with searching in Postgresql JSON and use with Lateral

Question

On the client side we have endpoint for searching with filter.

On backend I have method for searching in Postgresql DB. One of the option - search into jsonb field.

Before this day I searched over values and keys. And my code was:

if (appFilter.getSearchValue() != null) {
    criteria.add(Restrictions.sqlRestriction("UPPER(values::text) LIKE '%" + appFilter.getSearchValue().toUpperCase() + "%'"));
}

SQL analog (UPPER - is not mandatory):

SELECT *
FROM application
WHERE UPPER(values :: TEXT) LIKE '%some text%'

Now I need search only into values. SQL query for this purpouse:

SELECT *
FROM application, LATERAL json_each_text(application.values :: JSON)
WHERE value :: TEXT LIKE '%some text%';

How I can add this into restrictions? Maybe there ara any another variants for searching into values? Hibernate 5.0.12

Answer 1

Restrictions.sqlRestriction will be insert in generated sql after WHERE clause with AND so you need add something like this

criteria.add(Restrictions.sqlRestriction("this_.id IN (SELECT application.id FROM application , LATERAL json_each_text(values :: JSON) WHERE UPPER(value :: TEXT) LIKE '%" + appFilter.getSearchValue().toUpperCase() +"%')"));