How to filter a PostgreSQL array column with the JPA Criteria API?

Question

I am using:

• Hibernate 4.3.5
• Spring JPA 1.6.0
• Javax Persistence API 2.1

The "refcodemailing" column is defined as an array of int: int[]

My entity object:

@Entity
@Table
public class CalendarEvent implements Serializable {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private int id = 0;

  @Convert(converter = IntegerArrayConverter.class)
  @Column(name = "refcodemailing")
  private final List<Integer> mailingCodes = new ArrayList<>();

  // ....

}

I am trying to filter the column array with the following JPA Specification method:

private final List<MailingCode> mailingCodes = new ArrayList<>();

@Override
public Predicate toPredicate(Root<CalendarEvent> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

  // Mailing codes
  if(!mailingCodes.isEmpty()){
    List<Predicate> mailingCodePred = new ArrayList<>();

    for(MailingCode mailingCode: mailingCodes){
      restrictions.add(cb.isMember(mailingCode.getId(), root.<List<Integer>>get("mailingCodes")));
    }

    restrictions.add(cb.and(cb.isNotNull(root.<List<Integer>>get("mailingCodes")),       cb.or(mailingCodePred.toArray(new Predicate[]{}))));
  }
}

But the following exception is thrown:

java.lang.IllegalArgumentException: unknown collection expression type [org.hibernate.jpa.criteria.path.SingularAttributePath]
    at org.hibernate.jpa.criteria.CriteriaBuilderImpl.isMember(CriteriaBuilderImpl.java:1332)
    at com.agenda.CalendarEventQuery.toPredicate(CalendarEventQuery.java:100)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.applySpecificationToCriteria(SimpleJpaRepository.java:521)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery(SimpleJpaRepository.java:472)

Is there a way to do it?

Answer 1

According to JPA 2.0 specs:

Expressions that evaluate to embeddable types are not supported in collection member expressions. Support for use of embeddables in collection member expressions may be added in a future release of this specification.

However, I built a working example on GitHub using Hibernate.

Assuming we have this CalendarEvent entity and the MailingCode DTO object:

@Entity(name = "CalendarEvent")
@Table
public static class CalendarEvent implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @ElementCollection
    private final List<Integer> mailingCodes = new ArrayList<>();

}

public static class MailingCode {
    private Integer id;

    public MailingCode(Integer id) {
        this.id = id;
    }

    public Integer getId() {
        return id;
    }
}

You can write the Criteria API code as follows:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<CalendarEvent> criteria = builder.createQuery(CalendarEvent.class);
Root<CalendarEvent> root = criteria.from(CalendarEvent.class);

List<MailingCode> mailingCodes = Arrays.asList(
    new MailingCode(1),
    new MailingCode(2),
    new MailingCode(3)
);

Expression<List<Integer>> mailingCodesPath = root.get("mailingCodes");

Predicate predicate = builder.conjunction();

for(MailingCode mailingCode: mailingCodes){
    predicate = builder.and(predicate, builder.isMember(mailingCode.getId(), mailingCodesPath));
}

criteria.where(predicate);
List<CalendarEvent> events = entityManager.createQuery(criteria).getResultList();

However, an IN query is a much better choice since the SQL query above is suboptimal.

Answer 2

i v s narayana got me there. I simplified their answer a bit for my use case and just used the built in sql function.

To check if valueName is a member of the sql array stored in columnName:

            cb.isNotNull(cb.function("array_position", Integer.class, root.get(columnName), cb.literal(valueName))),

Answer 3

I tried various option but did not work for me. Finally understood, if the second parameter is array for built-in function, it is expanding variables and converting to myVarArgMethod. So what I did, I have written my own custom db functions like below.

If searchKey is single value we can use arrayContains and if searchKey contains multiple values, we can convert list to postrgress array format string using java util function and in postgres function, we can convert that to array by type casting.

Java util methods for converting list to postgress array string and vice versa

public static String convertToPGArray(List<String> content){
        StringBuilder str = new StringBuilder();
        if(content != null){
            str.append("{");
            int counter = 0;
            for(String text : content){
                if(counter != 0){
                    str.append(",");
                    counter++;
                }else{
                    counter++;
                }
                str.append("\"").append(text).append("\"");
            }
            str.append("}");
        }else{
            str.append("{}");
        }
        return str.toString();
    }

    public static List<String> convertToList(String content){
        List<String> returnList = new ArrayList<>();
        if(!(content == null || content.equals("{}") || content.trim().equals(""))){
            String tempContent = content;

            String[] tokens = tempContent.replace("{", "").replace("}", "").split(",");
            returnList = Arrays.stream(tokens).collect(Collectors.toList());
        }

        return returnList;
    }

Custom postgres function for arrayContains and arrayContainsAny

CREATE OR REPLACE FUNCTION arrayContains(arrayContent text[], searchKey text) RETURNS BOOLEAN as
'
DECLARE arrContent text[];
countVal integer :=0;
BEGIN
    arrContent = $1::text[];

    countVal = (SELECT count(array_position(arrContent, searchKey)));
    IF countVal = 0 THEN
        RETURN FALSE;
    ELSE
        RETURN TRUE;
    END IF;

EXCEPTION WHEN others THEN
    RETURN FALSE;
END;'
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION arrayContainsAny(arrayContent text[], searchKeys text) RETURNS BOOLEAN as
'
DECLARE arrContent text[];
DECLARE serKeys text[];
countVal integer :=0;
result boolean;
searchkey text;
BEGIN
    arrContent = $1::text[];
    serKeys = $2::text[];

    IF (count(cardinality(arrContent)) = 0 OR count(cardinality(serKeys)) = 0 OR cardinality(arrContent) = 0 OR cardinality(serKeys) = 0) THEN
        RAISE NOTICE $quote$array is null$quote$;
        RETURN TRUE;
    END IF;

    RAISE NOTICE $quote$after if condition$quote$;

    FOREACH searchkey IN ARRAY serKeys
    LOOP
        result = arrayContains(arrContent, searchkey);
        IF result = true THEN
            RETURN TRUE;
        END IF;
    END LOOP;

    RETURN FALSE;

EXCEPTION WHEN others THEN
    RAISE NOTICE $quote$exception$quote$;
    RETURN FALSE;
END;'
LANGUAGE plpgsql;

And we can call above function either in QueryBuilder or @Query annotation like below

In case of QueryBuilder Sample output like below

Specification<T> siteReqSpec1 = new Specification<T>() {
                            @Override
                            public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                                // TODO Auto-generated method stub
                                logger.info("getRBACResourceTagSpec entityName {} value {}", root.getModel().getName(), root.get(colName).getJavaType());
                                //return cb.isNotNull(root.get(colName));
                                //return cb.isMember(roleTagName, root.get(colName));
                                return cb.or(cb.isNull(root.get(colName)),
                                        cb.isTrue(cb.function("arrayContains", Boolean.class, root.get(colName), cb.literal(roleTagName))));
                            }

                        };

In case of @Query annotation like below

@Query("from DeviceworkFlowLite wf where wf.orgName = :organization and arrayContainsAny(rbac_resource_tags, :rbacResourceTags) = true")
    Page<DeviceworkFlowLite> findAllByOrgNameAndRBACResourceTagsIn(@Param("organization")String organization,  @Param("rbacResourceTags")String rbacResourceTags, Pageable pageable);

In case of jdbc SQL statement

    private static final String GET_ALL_TEMPLATE_FILTER_BY_ORG = 
            "select name,rbac_resource_tags from template_metadata "

            + " and arrayContainsAny(rbac_resource_tags, ?) = true ";
   ps.setString(4, jsonArrayRBACRoleResourceTag);