json_agg(
json_build_object(
'vat_cat',
(select row_to_json(_) from
(select
vc.id, vc.descr, vc.expense, vc.version, vat_percentage) as _
)
)
)
from vat_cat vc
left join (
select
vatcat_id,
COALESCE(
json_agg(
(select row_to_json(_) from
(select vp.percentage, vp.version, json_build_object(
'lower', lower(vp.validity),
'upper', upper(vp.validity),
'lower_inc', lower_inc(vp.validity),
'upper_inc', upper_inc(vp.validity)
) validity)
as _)
) FILTER (WHERE vp.vatcat_id IS NOT NULL), '[]'::JSON) vat_percentage
from vat_percentage vp
group by vatcat_id
) vp on
vc.id = vp.vatcat_id`
{ "vat_cat": { "id": 10, "descr": "nonsense", "expense": true, "version": 1, "vat_percentage": null } }