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.

Answer 1

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)