Hi, does anyone have experience aggregating a boolean column in JPQL with a PostgreSQL database? For example, I would like to do the following to list all the customers and show which have special orders.
select e.customer.name, max(e.specialOrder)
from sample$Order e
group by e.customer.name
But postgres does not allow using max with boolean columns. They have custom functions bool_and and bool_or for aggregation, which are not supported by JPQL.
I’ll share what I did to resolve this issue. I used the case statement.
select e.customer.name, max(case when e.specialOrder = true then 1 else 0 end)
from sample$Order e
group by e.customer.name
Using max is like bool_or, resulting in 1 when a mix of true and false exists but 0 when all false exists. Using min is like bool_and, resulting in 0 when a mix of true and false exists but 1 when all true exists.
Since I am using Groovy, the integer result is naturally converted back to a boolean value upon assignment.