How to JPA Query for Postgresql array?

Question

How do I query this model of a Postgresql table with a text[] column:

@TypeDefs({
    @TypeDef(
        name = "string-array", 
        typeClass = StringArrayType.class
    )
})

@Entity
@Table(name = "names")
public class Names implements Serializable
{  
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false, updatable = false)
    private Integer id;
    
    @Column(name = "name", nullable = false)
    private String name;
    
    @Type(type = "string-array")
    @Column(name = "tags", columnDefinition = "text[]")
    private String[] tags;
    
    ...
}

This is the CrudRepository query I tried and it fails validation:

@Query("SELECT t FROM Names t WHERE :tag MEMBER OF t.tags")
Iterable<Names> findByTag(@Param("tag") String tag);

I can find examples and documentation on how to insert, update, and delete SQL arrays, but nothing on how to query them.

Does this answer your question? Error while mapping postgres arrays in Spring JPA — Andronicus
– Andronicus, Commented Aug 27, 2020 at 15:55
Unfortunately not. The mapping works fine, queries return data. The problem is searching within the array. I'm guessing that MEMBER OF requires that the attribute be a Collection. Hopefully Vlad Mihalcea appears and explains it to me with small words :) — Rick DeBay
– Rick DeBay, Commented Aug 27, 2020 at 16:05
There is always an option to write native queries, where you're not limited by jpql etc. — Andronicus
– Andronicus, Commented Aug 27, 2020 at 16:10
Yea, native query is my fallback. Not so bad as almost only Postgresql supports SQL arrays. — Rick DeBay
– Rick DeBay, Commented Aug 27, 2020 at 16:23
According this blog post, this doesn't work. Quoting Vlad comment: "JPQL doesn’t have support for basic types that store multiple attributes. So, you should use SQL instead.". How to map a PostgreSQL ARRAY to a Java List with JPA and Hibernate Try using a native query: @Query(value = "SELECT * FROM Names t WHERE :tag = ANY(t.tags)", nativeQuery = true) — Fabricio Colombo
– Fabricio Colombo, Commented Aug 27, 2020 at 16:42

shubniggurath · Accepted Answer · 2021-06-02 09:09:01Z

4

I had the same problem when I wanted to query whether a specific parameter value (INTEGER) was contained in an array type (INTEGER[]) column.

... WHERE :type MEMBER OF (TABLENAME.typeArray)... => failed validation with a NullPointerException

... WHERE :type ANY(TABLENAME.typeArray)... => failed validation because 'TABLENAME' was an unexpected token

...WHERE :type IN (TABLENAME.typeArray)... => passed validation, but failed during execution with operator does not exist: integer = integer[]

What finally worked for me was the solution proposed by Fabricio Colombo in a comment to one of the answers above - credits to him, I'm just re-posting the solution for better visibility because I also struggled for some time before I found it.

@Query(value = "SELECT * FROM TABLE WHERE :type = ANY(TABLE.ARRAY_COLUMN)", nativeQuery = true)

answered Jun 2, 2021 at 9:09

shubniggurath

491 silver badge2 bronze badges

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

1 Comment

Rajesh Sep 3 at 13:19

@Query(value = "SELECT * FROM TABLE WHERE :type = ANY(TABLE.ARRAY_COLUMN)", nativeQuery = true) This query works only if we have simple single value on place of type , but if we have list of strings , it gets failed.

Saurabh Nigam · Accepted Answer · 2020-08-27 16:11:20Z

0

Try

@Query("SELECT t FROM Names t WHERE t.tags  @> CAST(?1 AS text[])")
List<Names> findByTag(String tag);

If this doesn't work then share the exception.

answered Aug 27, 2020 at 16:11

Saurabh Nigam

8237 silver badges12 bronze badges

2 Comments

Rick DeBay Over a year ago

org.hibernate.QueryException: unexpected char: '@' [SELECT t FROM Names t WHERE t.tags @> CAST(:tag AS text[])]

Saurabh Nigam Over a year ago

this notation works with eclipselink. You can use native query though for hibernate.

Collectives™ on Stack Overflow

How to JPA Query for Postgresql array?

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related