Postgresql custom expression with QueryDSL

I have a trouble with writing custom boolean template for where clause using QueryDSL. Simply I need to use arraycontains function to know that table.languages contains values at array of strings from filter.

Why Hibernate can't execute this? The raw sql is fine.

Code (variables are simplified, on screenshot it will be different):

    final var test = new JPAQuery<>(this.entityManager)
               .select(table)
               .from(table)
               .where(whereFromFilter(filter))
               .fetch()
    //...some code
    private static Predicate[] whereFromFilter(final Filter filter) {
      final var result = new ArrayList<Predicate>();
      //...some code
      result.add(
                Expressions.booleanTemplate(
                    "arraycontains(replace(replace(({0})::text,'[','{'),']','}')::text[],string_to_array({1},',')) = true",
                    table.languages,
                    "'" + String.join(",", filter.languages) + "'"
                )
      );
      //...some code
      return result.toArray(Predicate[]::new);
    }

table.languages in DB it's a json object that simply array of strings (so in template I'm converting it to array of strings):

I also try this code in raw sql console and it works fine:

SELECT arraycontains(replace(replace((table.languages)::text,'[','{'),']','}')::text[],string_to_array('CONFIG,PYTHON', ',')) is true
FROM table

Another filter (not with custom boolean template) works fine:

result.add(table.issueCountHigh.goe(filter.issueCountHighMin));

In debug mode I see that code from custom filter looks similar to code from another filter (that 100% works):

So, what HqlBaseParser can't parse? SQL functions?

stacktrace:

{
"statusCode": 500,
"statusDescription": "Internal Server Error",
"stacktrace": "java.lang.NullPointerException\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.identPrimary(HqlBaseParser.java:4770)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:1090)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.atom(HqlBaseParser.java:3866)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:3703)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.multiplyExpression(HqlBaseParser.java:3570)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.additiveExpression(HqlBaseParser.java:3197)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.concatenation(HqlBaseParser.java:682)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.relationalExpression(HqlBaseParser.java:2959)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.equalityExpression(HqlBaseParser.java:2815)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2774)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:2685)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:2645)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.expression(HqlBaseParser.java:2323)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.exprList(HqlBaseParser.java:4404)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.identPrimary(HqlBaseParser.java:4763)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:1090)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.atom(HqlBaseParser.java:3866)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:3703)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.multiplyExpression(HqlBaseParser.java:3570)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.additiveExpression(HqlBaseParser.java:3197)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.concatenation(HqlBaseParser.java:682)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.relationalExpression(HqlBaseParser.java:2959)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.equalityExpression(HqlBaseParser.java:2815)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2774)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:2685)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:2645)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.expression(HqlBaseParser.java:2323)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.exprList(HqlBaseParser.java:4404)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.identPrimary(HqlBaseParser.java:4763)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:1090)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.atom(HqlBaseParser.java:3866)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:3703)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.multiplyExpression(HqlBaseParser.java:3570)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.additiveExpression(HqlBaseParser.java:3197)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.concatenation(HqlBaseParser.java:682)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.relationalExpression(HqlBaseParser.java:2959)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.equalityExpression(HqlBaseParser.java:2815)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2774)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:2685)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:2645)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.expression(HqlBaseParser.java:2323)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.logicalExpression(HqlBaseParser.java:2071)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.whereClause(HqlBaseParser.java:540)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:831)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:336)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:200)\n\tat org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:294)\n\tat org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189)\n\tat org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)\n\tat org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)\n\tat org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)\n\tat org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162)\n\tat org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:604)\n\tat org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:716)\n\tat org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:113)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)\n\tat org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:362)\n\tat com.sun.proxy.$Proxy140.createQuery(Unknown Source)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)\n\tat org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:315)\n\tat com.sun.proxy.$Proxy140.createQuery(Unknown Source)\n\tat com.querydsl.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:101)\n\tat com.querydsl.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:94)\n\tat com.querydsl.jpa.impl.AbstractJPAQuery.fetch(AbstractJPAQuery.java:201)\n\tat

Solution

Solved problem by:

create function in DB using liqubase script:

        <createProcedure>
            CREATE OR REPLACE FUNCTION jsonb_array_contains(array_jsonb jsonb, string_comma character varying )
            RETURNS bool
            LANGUAGE plpgsql
            AS $$
            BEGIN
                RETURN arraycontains(
                        replace(
                            replace(
                                (array_jsonb)::text,
                                '[',
                                '{'
                            ),
                            ']',
                            '}'
                        )::text[],
                        string_to_array(string_comma, ',')
                    );
            END;
            $$;
        </createProcedure>

call this function in boolen template:

             result.add(
                Expressions.booleanTemplate(
                    "FUNCTION('jsonb_array_contains',({0}),'"
                        + String.join(",", filter.languages)
                        + "') = true",
                    table.languages
                )
            );