Postgres ANY query with an array of bind Variables

As noted in the Postgres documentation 9.21.3, Postgres supports an ANY query that takes an array of bind variables in addition to those that make use of subqueries. While Hibernate provides support for the later there does not seem to be anyway to make use of the former. HQL does not allow for the use of an array of bind variables in this context. Additionally, as the function Session.createCriteria() is now deprecated there seems to be no way to make use of the Criterion API to create a custom ANY implementation that would take an array of bind variables. It seems that it may be possible to utilize the JPA Criteria framework (by implementing CriteriaBuilder and CriteriaQuery), however at that point it is preferable to simply make SQL queries.
While it may not be a common use case, it would be very beneficial to have HQL support for ANY queries on an array of bind variables. While such queries could sometimes be reworked to use an IN query and a list of bind variables, doing so has notable disadvantages. For one, the number of bind variables in such IN queries is limited in size. No such limitation exists for an ANY clause. Additionally, postgres behavior on IN queries with a large list bind variable is suboptimal when compared to that of an = ANY query making use of an array.
I was curious if there were any plans to adding support for an ANY clause with an array of bind variables?

Hi,

yes we have plans to support collection valued bindings in JPQL/HQL i.e. alias.someField IN :fieldValues to render to this construct, but that is an optimization that will probably have to wait until Hibernate 6.0.x.
You can implement this yourself though by introducing a custom function and binding the parameter with an explicit type by using setParameter(String, Object, Type). Also see this answer on StackOverflow for an example: postgresql - HQL - Check if an Array contains a value - Stack Overflow