prevent Hibernate from using table aliases in subselect via @Formula

Question

I have this definition in my Entity

 @Formula(value = "(select coalesce(sum(v.value::float),0) from jsonb_each(urlaub_korrekturen) as \"v\")")
 private Float urlaubKorrektur;

Hibernate generates this select query which would work perfectly if I remove the p1_0 in the subselect. Is there any way to tell hibernate to ommit the table alias?

select
m1_0.nachname,
m1_0.vorname,
p1_0.monat,
    (
        select coalesce(sum(v.value::float), 0) from jsonb_each(p1_0.urlaub_korrekturen) as p1_0."v"
    ),
    p1_0.urlaub_korrekturen,
    p1_0.urlaub_monatssaldo,
    p1_0.urlaub_neu
from
    erp.personal_zeiterfassung_monatsabschluss p1_0
left join erp.personal_mitarbeiter m1_0 on
    m1_0.id = p1_0.mitarbeiter_id    

Or is there another/better way to get the sum of all values within a josnb-Field?

Answer 1

You should definitely write a HQL query that does that and don't pollute your entity model with this.

Write a custom Hibernate function:

public class MySum implements SQLFunction {

    @Override
    public boolean hasArguments() {
        return true;
    }

    @Override
    public boolean hasParenthesesIfNoArguments() {
        return true;
    }

    @Override
    public Type getReturnType(Type firstArgumentType, Mapping mapping) throws QueryException {
        return StandardBasicTypes.FLOAT;
    }

    @Override
    public String render(Type firstArgumentType, List args, SessionFactoryImplementor factory) throws QueryException {
        String query = args.get(args.size() - 1);
        return "(select coalesce(sum(v.value::float), 0) from jsonb_each(" + args.get(0) + ") as v)";
    }
}

Then you can query like this:

select e, my_sum(e.urlaubKorrekturen) from MyEntity e