2

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.

Rick DeBay
  • 315
  • 1
  • 5
  • 13
  • Does this answer your question? [Error while mapping postgres arrays in Spring JPA](https://stackoverflow.com/questions/49309772/error-while-mapping-postgres-arrays-in-spring-jpa) – Andronicus Aug 27 '20 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 Aug 27 '20 at 16:05
  • There is always an option to write native queries, where you're not limited by jpql etc. – Andronicus Aug 27 '20 at 16:10
  • I just changed it to ListArrayType from StringArrayType to test my idea about Collections, and on build got the same error: Validation failed for query...Caused by: java.lang.NullPointerException – Rick DeBay Aug 27 '20 at 16:22
  • 1
    Yea, native query is my fallback. Not so bad as almost only Postgresql supports SQL arrays. – Rick DeBay Aug 27 '20 at 16:23
  • 1
    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](https://vladmihalcea.com/postgresql-array-java-list/#comment-74332) Try using a native query: `@Query(value = "SELECT * FROM Names t WHERE :tag = ANY(t.tags)", nativeQuery = true)` – Fabricio Colombo Aug 27 '20 at 16:42
  • That worked, thanks. I remember now reading that blog post, but didn't notice the date at the time and thought it was old, so probably supported by now. – Rick DeBay Aug 27 '20 at 17:31

2 Answers2

3

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)
shubniggurath
  • 31
  • 2
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.

Saurabh Nigam
  • 795
  • 6
  • 12