JPA/Hibernate - Using Postgre's "ANY" Function in HQL

Context: Spring Boot Starter JPA Version 3.0.2, Hibernate Core 6.2.7.Final

Consider the following SQL tables and corresponding Hibernate entities:

CREATE TABLE person (
  id INT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  customerids VARCHAR(8)[] UNIQUE
);

CREATE TABLE order (
  id INT PRIMARY KEY,
  customerid VARCHAR(8) NOT NULL,
  ...
);

@Entity
@Table(...)
class Person {
  @Id
  @Column
  private int id;

  @Column
  private String name;

  @Column
//  @Type(ListArrayType.class)
  private List<String> customerIDs;
}

@Entity
@Table(...)
class Order {
  @Id
  @Column
  private int id;

  @Column
  private String customerID;
  ...
}

As we can see, each Person can have multiple customerIDs, which are stored in a Postgres array column (I commented out Vlad Mihalcea's Hibernate Type specification, as it appears to no longer be necessary in Hibernate 6.2.7). Of course, each of these customerIDs is completely unique.

Therefore, if we want to get all orders for a particular person, our SQL would look like this:

SELECT * FROM person 
INNER JOIN order ON order.customerid = ANY(person.customerids)
WHERE person.id = :pid

Where pid is the person's ID. This statement of course executes with no issues.

My issue occurs when translating this query into Hibernate Query Language. Here's the equivelant HQL and surrounding Spring Data Repository context (please note that I'm using an unrelated join for simplicity - it's not a necessity):

@Query(FROM Order o
INNER JOIN Person p ON o.customerID = FUNCTION('ANY', p.customerIDs)
WHERE p.id = :pid)
List<Order> getOrders(@Param("pid") String pid);

This immediately causes the following error however when starting Spring:

Caused by: java.lang.NullPointerException: Cannot invoke "org.hibernate.type.descriptor.jdbc.JdbcType.getDefaultSqlTypeCode()" because the return value of "org.hibernate.type.descriptor.java.JavaType.getRecommendedJdbcType(org.hibernate.type.descriptor.jdbc.JdbcTypeIndicators)" is null

Naturally, I decided instead to try using IN(UNNEST()) instead of = ANY(), as it's essentially equivalent:

@Query(FROM Order o
INNER JOIN Person p ON o.customerID IN ( FUNCTION('UNNEST', p.customerIDs) )
WHERE p.id = :pid)
List<Order> getOrders(@Param("pid") String pid);

No semantic errors pop up when starting the server, but unfortunately, Postgres does not allow set returning functions (UNNEST) inside of JOIN conditions:

org.postgresql.util.PSQLException: ERROR: set-returning functions are not allowed in JOIN conditions

Basically, my question is, can I turn the aforementioned join statement into HQL without having to resort to native SQL?

Final Note: I'd prefer this be done with a JOIN rather than a subselect. This is a simplified example; my real object requires checking if two columns match an ID in the "ID list" (person.customerIDs), and Postgres refuses to use my indexes for some reason.

Thank you!

I haven't been able to test this on Postgres, just HSQLDB, but I got as far as this SQL being produced:

select o1_0.id,o1_0.customerid from order o1_0 join person p1_0 on o1_0.customerid=any(p1_0.customerids) where p1_0.id=?

I created this class:

package com.example.demo;

import org.hibernate.boot.model.FunctionContributions;
import org.hibernate.boot.model.FunctionContributor;
import org.hibernate.type.BasicType;
import org.hibernate.type.StandardBasicTypes;

public class MyFunctionContributor implements FunctionContributor {
    @Override
    public void contributeFunctions(FunctionContributions functionContributions) {
        BasicType<String> stringType = functionContributions.getTypeConfiguration()
                .getBasicTypeRegistry().resolve(StandardBasicTypes.STRING);

        functionContributions.getFunctionRegistry().registerPattern(
                "anyString", "any(?1)", stringType);
    }
}

I created the file org.hibernate.boot.model.FunctionContributor in the folder META-INF/services in my resources directory. It contains:

com.example.demo.MyFunctionContributor

I could then create the following queries (your SQL example selected from person, while your JPQL query selected from order):

@Query("FROM Order o" +
        " INNER JOIN Person p ON o.customerID = anyString(p.customerIDs)" +
        " WHERE p.id = :pid")
List<Order> getOrders(@Param("pid") int pid);

@Query("FROM Person p" +
        " INNER JOIN Order o ON o.customerID = anyString(p.customerIDs)" +
        " WHERE p.id = :pid")
List<Person> getPeople(@Param("pid") int pid);

Trying to get strict JPQL compliance:

@Query("SELECT o FROM Order o" +
        " INNER JOIN Person p ON o.customerID = FUNCTION('anyString', p.customerIDs)" +
        " WHERE p.id = :pid")
List<Order> getOrdersJpql(@Param("pid") int pid);

Note:
My actual table name for Order was myorder, since HSQLDB didn't like me using order. A quick test on a different machine and it seems that Postgres doesn't like a table called order also (I guess "ORDER BY" makes "order" a reserved word).