Criteria API error: operator does not exist: integer = integer[]

Question

I have an entity where I have a field of type integer array.

class User { 
    @ElementCollection
    @Column(name = "`location_ids`", columnDefinition = "int[]")
    @Type(ListArrayType.class)
    private List<Integer> locationIds;

    @Column(name = "`email`")
    private String email;

    @Column(name = "`deletedAt`")
    @Temporal(TIMESTAMP)
    private LocalDateTime deletedAt;

}

I want to use criteria API to compare the requested locationIds list with the existing array. For example, I want to check if the location ID 1,2 or 3 is part of the "locationIds" array column or not.

final List<Predicate> predicates = new ArrayList<>();

predicates.add(criteriaBuilder.equal(root.get("email"), email));
predicates.add(criteriaBuilder.isNull(root.get("deletedAt")));

List<Predicate> arrayPredicates = new ArrayList<>();
final Expression<List<Integer>> exp = root.get("locationIds");
for (Integer id : locationIdsFromSearchModel) {
     arrayPredicates.add(criteriaBuilder.isMember(id, exp));
}
predicates.add(criteriaBuilder.or(arrayPredicates.toArray(new Predicate[0])));

The error "operator does not exist: integer = integer[]" is thrown from the call criteriaBuilder.isMember(id, exp)

I looked up and it seems I need to do an "ANY" operation on the array. I didn't find any reference on how to do that with Criteria API.

Disjunctive

Answer 1

isMember is not designed to work with arrays - it works with collections of elements - stored in a separate table.

Hibernate 6 added support for array types, and more specifically, Hibernate 6.4 added support for some query functions: https://in.relation.to/2023/10/26/orm-64cr1/

array_contains() Whether an array contains an element - e.g. array_contains(an.array, 1)

See the Query Guide for full coverage of array functions and examples in HQL

List<EntityWithArrays> results = em.createQuery( 
    "from EntityWithArrays e where array_contains(e.theArray, 'abc')", 
    EntityWithArrays.class 
);

array_contains is rendered to SQL as @> operator. Check your db docs for details, for example Postgres Array Functions and Operators

anyarray @> anyarray → boolean

Does the first array contain the second, that is, does each element appearing in the second array equal some element of the first array? (Duplicates are not treated specially, thus ARRAY[1] and ARRAY[1,1] are each considered to contain the other.)

ARRAY[1,4,3] @> ARRAY[3,1,3] → t

Use array in your entity definition

@Column(name = "`location_ids`", columnDefinition = "int[]")
private int[] locationIds = new int[0];

Change your predicate to use array_contains

var newPredicate = builder.function(
        "array_contains",
        Boolean.class,
        mailingCodesPath.as(int[].class),
        builder.literal(mailingCode.getId())
);

Undirected

Answer 2

JPA Criteria API doesn't directly support the ANY SQL operation for array types. You can use a custom function expression as workaround.

...
 Expression<Boolean> anyExpression = criteriaBuilder.function(
        "ANY", 
        Boolean.class, 
        criteriaBuilder.literal(locationIdsFromSearchModel.toArray(new Integer[0])), 
        root.get("locationIds"));

 predicates.add(anyExpression);

 criteriaQuery.where(criteriaBuilder.and(predicates.toArray(new Predicate[0])));
 List<User> users= entityManager.createQuery(criteriaQuery).getResultList();
...

You can get some input from another question link.

Amphiaster