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

**URL:** https://discourse.hibernate.org/t/using-postgresqls-any-array-syntax-with-hibernate-6-2-9/8460
**Category:** Hibernate ORM
**Created:** [October 26, 2023, 10:49am UTC](https://discourse.hibernate.org/t/using-postgresqls-any-array-syntax-with-hibernate-6-2-9/8460 "2023-10-26T10:49:05Z")
**Posts on this page:** 9
**Page:** 1

<div class="post-metadata">

### Author: ![lw-mcno](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/lw-mcno/32/2738_2.png) [@lw-mcno](https://discourse.hibernate.org/u/lw-mcno)
#### Post date: [October 26, 2023, 10:49am UTC](https://discourse.hibernate.org/t/using-postgresqls-any-array-syntax-with-hibernate-6-2-9/8460/1 "2023-10-26T10:49:05Z")

</div>

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.](https://github.com/vladmihalcea/hypersistence-utils), 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:

```auto
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(StandardHqlTranslator.java:97)
	...
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(SqmCriteriaNodeBuilder.java:2102)
	...

```

(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:

```sql
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.

---

<div class="post-metadata">

### Author: ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)
#### Post date: [October 27, 2023, 8:22am UTC](https://discourse.hibernate.org/t/using-postgresqls-any-array-syntax-with-hibernate-6-2-9/8460/2 "2023-10-27T08:22:09Z")

</div>

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)`.

---

<div class="post-metadata">

### Author: ![lw-mcno](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/lw-mcno/32/2738_2.png) [@lw-mcno](https://discourse.hibernate.org/u/lw-mcno)
#### Post date: [October 27, 2023, 9:58am UTC](https://discourse.hibernate.org/t/using-postgresqls-any-array-syntax-with-hibernate-6-2-9/8460/3 "2023-10-27T09:58:39Z")

</div>

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

```java
    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:

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

```

Unfortunately, it doesn’t work with CriteriaQuery:

```java
        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));
        em.createQuery(delete).executeUpdate();

```

resulting in:

```auto
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(BaseSqmToSqlAstConverter.java:5445)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitLiteral(BaseSqmToSqlAstConverter.java:435)
	at org.hibernate.query.sqm.tree.expression.SqmLiteral.accept(SqmLiteral.java:65)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitWithInferredType(BaseSqmToSqlAstConverter.java:6800)
	at org.hibernate.query.sqm.function.SelfRenderingSqmFunction.resolveSqlAstArguments(SelfRenderingSqmFunction.java:132)
	at org.hibernate.query.sqm.function.SelfRenderingSqmFunction.convertToSqlAst(SelfRenderingSqmFunction.java:144)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitFunction(BaseSqmToSqlAstConverter.java:6018)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitFunction(BaseSqmToSqlAstConverter.java:435)
	at org.hibernate.query.sqm.tree.expression.SqmFunction.accept(SqmFunction.java:66)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitBooleanExpressionPredicate(BaseSqmToSqlAstConverter.java:7730)
	at org.hibernate.query.sqm.tree.predicate.SqmBooleanExpressionPredicate.accept(SqmBooleanExpressionPredicate.java:71)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitJunctionPredicate(BaseSqmToSqlAstConverter.java:6967)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitJunctionPredicate(BaseSqmToSqlAstConverter.java:435)
	at org.hibernate.query.sqm.tree.predicate.SqmJunctionPredicate.accept(SqmJunctionPredicate.java:74)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitWhereClause(BaseSqmToSqlAstConverter.java:2478)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitDeleteStatement(BaseSqmToSqlAstConverter.java:1121)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitDeleteStatement(BaseSqmToSqlAstConverter.java:435)
	at org.hibernate.query.sqm.tree.delete.SqmDeleteStatement.accept(SqmDeleteStatement.java:94)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.translate(BaseSqmToSqlAstConverter.java:771)
	at org.hibernate.query.sqm.internal.SimpleDeleteQueryPlan.createDeleteTranslator(SimpleDeleteQueryPlan.java:86)
	at org.hibernate.query.sqm.internal.SimpleDeleteQueryPlan.executeUpdate(SimpleDeleteQueryPlan.java:105)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.doExecuteUpdate(QuerySqmImpl.java:735)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.executeUpdate(QuerySqmImpl.java:705)

```

It fails while trying to inspect the array literal:

 ![image](https://canada1.discourse-cdn.com/flex035/uploads/hibernate/original/2X/9/947257e04acf72ad3786ee826854e14c55c14f1a.png)

Its nodeType and javaType are null.

---

<div class="post-metadata">

### Author: ![lw-mcno](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/lw-mcno/32/2738_2.png) [@lw-mcno](https://discourse.hibernate.org/u/lw-mcno)
#### Post date: [October 27, 2023, 11:26am UTC](https://discourse.hibernate.org/t/using-postgresqls-any-array-syntax-with-hibernate-6-2-9/8460/4 "2023-10-27T11:26:16Z")

</div>

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:

```java
Expression<Boolean> arrayAny = cb.function("array_any", Boolean.class, myEntity.get(MyEntity_.id), 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.

---

<div class="post-metadata">

### Author: ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)
#### Post date: [October 27, 2023, 2:38pm UTC](https://discourse.hibernate.org/t/using-postgresqls-any-array-syntax-with-hibernate-6-2-9/8460/5 "2023-10-27T14:38:41Z")

</div>

No caveats, it should just work ideally.

---

<div class="post-metadata">

### Author: ![lw-mcno](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/lw-mcno/32/2738_2.png) [@lw-mcno](https://discourse.hibernate.org/u/lw-mcno)
#### Post date: [October 27, 2023, 11:58pm UTC](https://discourse.hibernate.org/t/using-postgresqls-any-array-syntax-with-hibernate-6-2-9/8460/6 "2023-10-27T23:58:30Z")

</div>

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:

```auto
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

```

---

<div class="post-metadata">

### Author: ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)
#### Post date: [October 30, 2023, 10:23am UTC](https://discourse.hibernate.org/t/using-postgresqls-any-array-syntax-with-hibernate-6-2-9/8460/7 "2023-10-30T10:23:16Z")

</div>

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

---

<div class="post-metadata">

### Author: ![Pat](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/pat/32/2565_2.png) [@Pat](https://discourse.hibernate.org/u/Pat)
#### Post date: [February 7, 2024, 5:00pm UTC](https://discourse.hibernate.org/t/using-postgresqls-any-array-syntax-with-hibernate-6-2-9/8460/8 "2024-02-07T17:00:42Z")

</div>

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  
Regards  
Pat

---

<div class="post-metadata">

### Author: ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)
#### Post date: [February 7, 2024, 6:05pm UTC](https://discourse.hibernate.org/t/using-postgresqls-any-array-syntax-with-hibernate-6-2-9/8460/9 "2024-02-07T18:05:41Z")

</div>

Look into the documentation: [Hibernate ORM User Guide](https://docs.jboss.org/hibernate/orm/6.4/userguide/html_single/Hibernate_User_Guide.html#hql-array-contains-functions)