Using JPA query: search in postgres array and using 'in' clause in 1 query

Question

There's a filter endpoint to query on multiple properties. (Spring Boot 2.4.5 is used) This is the entity:

@Data
@Entity
@Table(name = "organization")
@TypeDefs({@TypeDef(name = "dbArray", typeClass = CustomArrayType.class)})
public class Organization implements Serializable {

  @Id
  private Long id;

  @Column
  private String name;

  @Column
  private String otherName;

  @Column(name = "organization_types")
  @Type(type = "dbArray")
  private List<String> organizationTypes;
}

CustomArrayType is a custom Hibernate UserType. In postgres, the definition of this column is varchar[].

This is the repository:

@Repository
public interface OrganizationRepository extends JpaRepository<Organization, Long> {

@Query(
      value =
          "select o from Organization o where"
              + " (:organizationType is null OR :organizationType in (o.organizationTypes)) AND"
              + " (:otherNames is null OR o.otherName in (:otherNames))")
  List<Organization> findOrganizationByTypeAndOtherNamesIn(
      @Param("organizationType") String organizationType,
      @Param("otherNames") List<String> otherNames);
}

However, I get an exception with the following cause: Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = character varying[] Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 465

I tried to use a native query:

@Query(
        value =
            "select * from organization o where"
                + " (:organizationType is null OR :organizationType = ANY(o.organization_types)) AND"
                + " (:otherNames is not null AND o.otherName in (:otherNames))",
        nativeQuery = true)
    List<Organization> findOrganizationByTypeAndOtherNamesIn(
        @Param("organizationType") String organizationType,
        @Param("otherNames") List<String> otherNames);

But unfortunately, I get the following cause:

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bytea
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 116

(position 116 is where otherNames query start). I don't want to cast the otherNames to a varchar, because I provide a List, thus that will also not result in a working query.

Does anyone know how to get this query working in either a native query (postgres 10) or in JPQL?

Can you please check again removing :otherNames is not null part in your query ? I think this is where it is breaking — Pramod
– Pramod, Commented Jun 25, 2021 at 9:31

Christian Beikov · Accepted Answer · 2021-07-01 11:53:20Z

0

You need a custom function e.g. my_any that wraps o.organizationTypes and renders the any "function". This question is similar to the following: unexpected token: '<function_name>' error when using hibernate, JPQL and postgres function

Then you could use it like this:

@Repository
public interface OrganizationRepository extends JpaRepository<Organization, Long> {

@Query(
      value =
          "select o from Organization o where"
              + " (:organizationType is null OR :organizationType = my_any(o.organizationTypes)) AND"
              + " (:otherNames is null OR o.otherName in (:otherNames))")
  List<Organization> findOrganizationByTypeAndOtherNamesIn(
      @Param("organizationType") String organizationType,
      @Param("otherNames") List<String> otherNames);
}

answered Jul 1, 2021 at 11:53

Christian Beikov

16.6k2 gold badges36 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

coladict · Accepted Answer · 2021-07-05 12:19:27Z

Okay, so you have a couple of extra problem you haven't even run into yet, even if you solve these elegantly. Here's my suggestion. You'll need two of the queries to be native.

@Query(value = "select o from Organization o where o.otherName in :otherNames")
List<Organization> findOrganizationByOtherNamesIn(
      @Param("otherNames") List<String> otherNames);
}

@Query(value = "select * from organization o where"
        + " array_position(o.organization_types, :organizationType) is not null "
        + " AND o.otherName in (:otherNames)",
        nativeQuery = true)
List<Organization> findOrganizationByTypeAndOtherNamesIn(
      @Param("organizationType") String organizationType,
      @Param("otherNames") List<String> otherNames);
}

@Query(value = "select * from organization o where"
        + " array_position(o.organization_types, :organizationType) is not null ",
        nativeQuery = true)
List<Organization> findOrganizationByType(
      @Param("organizationType") String organizationType);
}

And in your controller/service you call it like this:

if (organizationType != null && otherNames != null && !otherNames.isEmpty()) {
// that isEmpty is vital to avoid a syntax error on an empty list
    repo.findOrganizationByTypeAndOtherNamesIn(organizationType, otherNames);
}
else if (otherNames != null && !otherNames.isEmpty()) {
    repo.findOrganizationByOtherNamesIn(otherNames);
}
else if (organizationType != null) {
    repo.findOrganizationByType(organizationType);
}
else {
    // this method already exists and is the default logic when both parameters are null
    repo.findAll();
}

Technically there is another possible workaround, but it still leaves you with a problem if that otherNames list is not null, but empty.

I wanted to avoid the if-else construction in the controller/service and only use 1 query. I know it is possible when I use raw postgres, so I hoped it would be possible with JPA as well. But unfortunately not. Thanks for your answer anyways!

Collectives™ on Stack Overflow

Using JPA query: search in postgres array and using 'in' clause in 1 query

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related