How to use array functions in JPA Query without using Native Query?

I want to use the array_overlaps() function in a JPA query without explicitly resorting to a native query.

Here is my @Query definition:

  @Query(value = """
                SELECT view FROM FilterView view
                WHERE (:selectedIds IS NULL
                        OR array_overlaps(view.ids, (:selectedIds)) IS true)
                """)
  List<FilterView> findAllByFilter(Collection<Long> selectedIds);

View Entity definition:

@Getter
@Setter
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "filter_view")
public class FilterView {

  @JdbcTypeCode(Types.ARRAY)
  @Column(name = "ids")
  private Collection<Long> ids;

  // other fields
}

View SQL file:

DROP VIEW IF EXISTS filter_view;
CREATE VIEW filter_view AS
SELECT
    ARRAY_AGG(DISTINCT p.id) AS ids,
    -- other columns

FROM my_entity p
         LEFT JOIN ...
GROUP BY ...

In this configuration I encounter the following runtime error:

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint[] && bytea

The error suggests that the selectedIds parameter is being treated as bytea, even though it is defined as Collection in the method signature, which I don't fully understand why. This leads to a type mismatch.

This problem is not related to this hibernate issue, removing null check (:selectedIds IS NULL OR) does not resolve the problem. Furthermore, in my test scenario, the input value is always present, so the absence of the null check is not the cause either.

I've tried using cast() or treat(), but didn't find the way to do it properly. For example cast(:selectedIds as Collection<Long>) gives compilation error.

I've also tried wrapping the parameter with elements(:selectedIds), but it leads to compilation error as well:

Caused by: org.hibernate.query.sqm.produce.function.FunctionArgumentException:
Parameter 1 of function 'array_overlaps()' requires an array type
java.util.Collection<java.lang.Long>, but argument is of type 'java.lang.Object'

How can I correctly pass a Collection<Long> as an array parameter in JPA so that array_overlaps() works without using a native query? Is there an alternative way to represent arrays in JPA that avoids the bytea conversion issue?

Solution

JPQL does not support PostgreSQL's array_overlaps function directly. You can try like this if you don't want to use the native query. This way it should work.

Make the parameter type Long[]

@Query("SELECT view FROM FilterView view " +
            "WHERE (:selectedIds IS NULL OR array_overlaps(view.ids, :selectedIds) IS TRUE)")
    List<FilterView> findAllByFilter(@Param("selectedIds") Long[] selectedIds);

Change the field type as Long[]

  @JdbcTypeCode(Types.ARRAY)
  @Column(name = "ids")
  private Long[] ids;