ANY(subquery) does sadly not work on H2 in-memory database because of long-living bug 1934, as noticed and checked in https://stackoverflow.com/questions/72095966/in-h2-database-how-to-use-anyarray

Trying criteriaBuilder.isMember(...) also not work for me.

In all case I believe that a custom function should be supported in Hibernate. The new validations in Hibernate 6 seems not to cope with every edge cases. With Hibernate 5 and less validation, I could use the function properly. I attach 2 tests that currently lead to an exception, first one is my primary use case, see:

JPAHHH16312UnitTestCase.java