JPARepository @Query: Filtering out empty String[] values (PostgreSQL _text)

I have a PostgreSQL-database table with nullable _text column. In backend entity it is configured as String[].

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

I'm trying to use JPARepository's @Query-annotation to find the latest entity with above column's values not empty or null, but haven't found a way to do it yet (one of the examples below).

@Query("SELECT e FROM Entity e " +
        "WHERE e.orderNo = 1 " +
        "AND e.texts IS NOT EMPTY " + // not working
        "ORDER BY e.createdAt DESC " +
        "LIMIT 1")
Optional<Entity> findLatestEntityWithTextsValues();

Is there a good way to filter out empty/null texts values in a query? I can always filter them later, but I'd rather get the result immediately as an Optional from the database instead of returning a List that then needs iteration.

Solution

Since you are already dealing with a Postgresql's type and syntax while declaring the texts attribute and column, perhaps using a native based query can solve your problem without too much impact on the other components of your application.

You can try to use the array_length function (see documentation here: https://www.postgresql.org/docs/16/functions-array.html) and change the repository query accordingly:

"AND array_length(e.texts, 1) > 0 " +

Of course remember to adapt the class / attribute names that are used in the JPQL query to the table / column ones, and set the query as native (i.e. nativeQuery = true).

As an alternative (but I don't know if this works on fields that are managed by StringArrayType and are specified with columnDefinition attribute), I saw that also JPQL, using Hibernate 6.5.3.Final, can manage and transcode to native sql an array_length function, so that you can directly write in you query:

"AND array_length(e.texts) > 0 " +

In my tests on h2 or hsqldb, this is converted into the correct function for getting the size of the array (i.e. cardinality, in this case).