JPA2/Hibernate Criteria or CriteriaQuery for Postgres JSON type

Question

I have a PostgreSQL database with JSON fields. I would like to construct a query which restricts results by JSON expressions. I can formulate this query in psql without problem:

select * from mytable where relation_id=100 AND CAST(jsonField->'key' AS float) >= 10.0;

This query combines a normal column and a JSON column.

I have no idea how to start this in Hibernate using Criteria or Criteria query. I could, in theory, use HSQL language, but I am almost certain that will fail when it comes to the JSON column.

Does anyone have an idea how to tackle this?

Answer 1

The only way to do that is to write a custom SQLFunction for the CAST(jsonField->'key' AS float) expression and then use that in JPQL.

public String render(Type firstArgumentType, List args, SessionFactoryImplementor factory) throws QueryException {
    return "CAST(" + args.get(0).toString() + "->'" + args.get(1).toString() + "' AS float)";
}

Register in the dialect with registerFunction("json_float", new JsonFloatFunction()) and use it in the query like

SELECT o FROM MyTable o WHERE o.relation.id = 100 AND JSON_FLOAT(o.jsonField, 'key') >= 10.0