We have a view that gathers data from many tables in order to make a search feature inside our application.

The schema of this view can be simplified by this create table statement:

CREATE TABLE searchable_products(
    id BIGINT PRIMARY KEY,
    internal_key VARCHAR(20),
    product_codes VARCHAR[],
    descriptions TEXT,
    gtins VARCHAR[],
    references TEXT)

This table / view is mapped by this entity:

@Entity
@Table(name = "searchable_products")
@Immutable
@NoArgsConstructor
@Getter
@Setter
public class SearchableProduct {

    @Id
    @Column(name = "id")
    private Long id;

    @Column(name = "internal_key")
    private String internalKey;

    @Column(name = "product_codes")
    private List<String> productCodes;

    @Column(name = "descriptions")
    private String descriptions;

    @Column(name = "product_gtins")
    private List<String> gtins;

    @Column(name = "references")
    private String references;
}

I have to search that a searchString value is equal to some column or equal to a value inside an array column, so I wrote this JPQL query and this class/method:

@Repository
public interface SearchableProductRepository extends JpaRepository<SearchableProduct, Long> {
    @Query(value="""
            SELECT sp FROM SearchableProduct sp 
            WHERE  sp.internalKey = :searchString
             OR    sp.references ILIKE %:searchString%
             OR    sp.descriptions ILIKE %:searchString%
             OR    :searchString MEMBER OF sp.productCodes
             OR    :searchString MEMBER OF sp.gtins""")
    Page<SearchableProduct> findAllContainingSearchString(String searchString, Pageable pageable);

However, I get this exception:

Caused by: org.hibernate.query.SemanticException: Path argument to MEMBER OF must be a plural attribute

So, I tried to add the @ElementCollection annotation to the List type fields:

@ElementCollection
@Column(name = "product_codes")
private String[] productCodes;

@ElementCollection
@Column(name = "product_gtins")
private String[] gtins;

Now, I get this exception:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table “SEARCHABLE_PRODUCT_PRODUCT_CODES” not found

because the generated SQL query is:

select s1_0.id,s1_0.internal_key,s1_0.descriptions,s1_0.references from searchable_product_view s1_0 where s1_0.internal_key=? or lower(s1_0.references) like lower(?) escape '' or lower(s1_0.descriptions) like lower(?) escape '' or ? in(select p1_0.product_codes from searchable_product_product_codes p1_0 where s1_0.id=p1_0.searchable_product_id) or ? in(select g1_0.product_gtins from searchable_product_gtins g1_0 where s1_0.id=g1_0.searchable_product_id) order by s1_0.descriptions offset ? rows fetch first ? rows only

As I don’t have and don’t need this kind of table, I wonder if it is a table that should be generated by JPA. Maybe I’m missing some other annotations?

According to this question Mapping array with Hibernate I also tried to change the List type fields into Array type fields using the Hypersistence library to map database arrays to java arrays but I get the same errors :

pom.xml:

<dependency>
    <groupId>io.hypersistence</groupId>
    <artifactId>hypersistence-utils-hibernate-62</artifactId>
    <version>3.8.2</version>
</dependency>

And the entity:

@Type(StringArrayType.class)
@ElementCollection
@Column(name = "product_codes", columnDefinition = "text[]")
private String[] productCodes;

@Type(StringArrayType.class)
@ElementCollection
@Column(name = "product_gtins", columnDefinition = "text[]")
private String[] gtins;

I have checked this article How to JPA Query for Postgresql array?
But the accepted response is clearly wrong and other solutions propose to make a native query that we don’t want to do.

How do I make it work?

As additional information about libraries, we use Spring Boot 3.1.5, Hibernate 6.2.