I've registered a Postgres "jsonb_extract_path_text" function that can extract a specific Json key from JSONB column. Now trying to apply count aggregate function on it, it produces QuerySyntaxException, seems that the function schema does not support braces
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
>From from = cq.from(Domain.class);
cq.select(cb.count(cb.function("jsonb_extract_path_text", String.class, from.get("extra"), cb.literal("test1"))));
TypedQuery query = em.createQuery(cq);
List list = query.getResultList();
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found '(' near line 1, column 22 [select count(function('jsonb_extract_path_text', generatedAlias0.extra, 'test1')) from com.example.demo.Domain as generatedAlias0]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:297) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:155) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:604) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:716) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
... 43 common frames omitted
I've tested the same implementation with EclipseLink and it did not produce any errors, so I don't believe that this goes against JPA specifications. |