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.
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).