Using PostgreSQL's = ANY(array) syntax with Hibernate 6.2.9

We’ve successfully used the syntax ‘WHERE attribute = function(‘any’, :some_array)’ syntax in Hibernate 5 (while passing collections as custom array types from this library GitHub - vladmihalcea/hypersistence-utils: The Hypersistence Utils library (previously known as Hibernate Types) gives you Spring and Hibernate utilities that can help you get the most out of your data access layer., e.g. StringArrayType).

Seems, that Hibernate 6.2.9 (coming with the current Quarkus), contains additional validation and doesn’t allow to issue such a query at all:

Caused by: org.hibernate.query.sqm.InterpretationException: Error interpreting query [DELETE FROM XXX WHERE name = ?1 AND id = function('any', ?2)]; this may indicate a semantic (user query) problem or a bug in the parser [DELETE FROM XXX WHERE name = ?1 AND id = function('any', ?2)]
	at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(
Caused by: java.lang.IllegalArgumentException: Can't compare test expression of type [BasicSqmPathSource(id : String)] with element of type [basicType@21(java.lang.Boolean,16)]
	at org.hibernate.query.sqm.internal.SqmCriteriaNodeBuilder.assertComparable(

(in the entity, the “id” is a String).

Same happens when the Criteria API is used instead of JPQL.

What would be the best practice here?

We’re using =ANY (instead of IN) and passing the matching collection of elements as a single array parameter for two reasons:

  1. to avoid the limit of query parameters (max 32767 bind-params in the PG JDBC driver, AFAIK)
  2. performance vs (very long) boolean expressions that PostgreSQL generates for ‘plain’ INs

BTW. does Hibernate 6 JPQL / criteria API have a way to express this kind native query:

SELECT * FROM my_table
WHERE (attr_1, attr_2) IN (SELECT * FROM unnest(:attr_1_array, :attr_2_array))

– it was SELECTing * FROM a function (syntax required by PostgreSQL) I wasn’t able to express. Again, the arguments are arrays for the same reasons as above.

If the attribute by which you want to filter by is the primary key or a natural id, then you can just use the Hibernate multiLoad API and it will use that SQL syntax behind the scenes automatically for PostgreSQL.

Out of the box support for array functions/predicates just landed in Hibernate ORM 6.4, so if you can/want to upgrade to that, you’ll be able to make use of where array_contains(arrayAttribute, :arrayParam).

In the meantime, you can create a custom SqmFunction for this purpose and register it via a FunctionContributor. You can render any SQL you want in such a custom function implementation and use it from HQL like a regular function e.g. where array_any(arrayAttribute, :arrayParam).

Thank you. I was able to successfully register the following function:

    public void contributeFunctions(FunctionContributions functionContributions) {
        BasicType<Boolean> resultType = functionContributions.getTypeConfiguration().getBasicTypeRegistry().resolve(StandardBasicTypes.BOOLEAN);
        functionContributions.getFunctionRegistry().registerPattern("array_any", "?1 = ANY(?2)", resultType);

and use it from JPQL with an array argument:

        delete(MyEntity_.UNAME + " = ?1 AND array_any(" + MyEntity_.ID + ", ?2)",
                new TypedParameterValue<>(StringArrayType.INSTANCE, ids.toArray(String[]::new)));

Unfortunately, it doesn’t work with CriteriaQuery:

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaDelete<MyEntity> delete = cb.createCriteriaDelete(MyEntity.class);
        Root<MyEntity> myEntity = delete.from(MyEntity.class);
        Predicate unamePredicate = cb.equal(myEntity.get(MyEntity_.uname), username);
        Expression<Boolean> arrayAny = cb.function("array_any", Boolean.class, cb.literal(MyEntity_.ID), cb.literal(new TypedParameterValue<>(StringArrayType.INSTANCE, ids.toArray(String[]::new))));
        delete.where(cb.and(unamePredicate, arrayAny));

resulting in:

java.lang.NullPointerException: Cannot invoke "org.hibernate.metamodel.model.domain.internal.BasicSqmPathSource.getSqmPathType()" because the return value of "org.hibernate.query.sqm.tree.expression.SqmLiteral.getNodeType()" is null

	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitLiteral(
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitLiteral(
	at org.hibernate.query.sqm.tree.expression.SqmLiteral.accept(
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitWithInferredType(
	at org.hibernate.query.sqm.function.SelfRenderingSqmFunction.resolveSqlAstArguments(
	at org.hibernate.query.sqm.function.SelfRenderingSqmFunction.convertToSqlAst(
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitFunction(
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitFunction(
	at org.hibernate.query.sqm.tree.expression.SqmFunction.accept(
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitBooleanExpressionPredicate(
	at org.hibernate.query.sqm.tree.predicate.SqmBooleanExpressionPredicate.accept(
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitJunctionPredicate(
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitJunctionPredicate(
	at org.hibernate.query.sqm.tree.predicate.SqmJunctionPredicate.accept(
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitWhereClause(
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitDeleteStatement(
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitDeleteStatement(
	at org.hibernate.query.sqm.tree.delete.SqmDeleteStatement.accept(
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.translate(
	at org.hibernate.query.sqm.internal.SimpleDeleteQueryPlan.createDeleteTranslator(
	at org.hibernate.query.sqm.internal.SimpleDeleteQueryPlan.executeUpdate(
	at org.hibernate.query.sqm.internal.QuerySqmImpl.doExecuteUpdate(
	at org.hibernate.query.sqm.internal.QuerySqmImpl.executeUpdate(

It fails while trying to inspect the array literal:

Its nodeType and javaType are null.

Looks like the usage of custom array types is unnecessary now and Hibernate 6 will actually pass java arrays as SQL arrays without further steps. Are there any caveats here?

This simpler predicate just works:

Expression<Boolean> arrayAny = cb.function("array_any", Boolean.class, myEntity.get(, cb.literal(ids.toArray(String[]::new)));

(also: the compared column reference is not a literal)

Leaving the previous quesion in the thread, so that others with the same problem can find it.

No caveats, it should just work ideally.

one more question: in a function contributor, what would be the correct invariant / return type for a function, that turns array(s) into a set of rows? (PostgreSQL’s select * from unnest(…) with one or more arrays).
The types of columns are not fixed in this case.

As of now, I’ve tried with StandardBasicTypes.OBJECT_TYPE (which definitely doesn’t describe reality), and though it works with the use case column IN (my_custom_function(?1), it doesnt’ work when trying to compare multiple columns, e.g. (column1, column2) in (my_custom_function(?1, ?2), resulting in:

java.lang.ClassCastException: class org.hibernate.type.JavaObjectType cannot be cast to class org.hibernate.metamodel.mapping.EmbeddableMappingType (org.hibernate.type.JavaObjectType and org.hibernate.metamodel.mapping

You can’t model this. You rather have to model a function that returns a boolean and you render a predicate into SQL.

Hi all,
we are using ORM 6.4.1 now. But how can use array_contains function?
Can you give me an example?

Thanks in advance

Look into the documentation: Hibernate ORM User Guide