coalesce(json_agg [..] filter where [..], '[]' in left join returning null

From: Stefan Houtzager <stefan(dot)houtzager(at)gmail(dot)com>
To: pgsql-novice(at)lists(dot)postgresql(dot)org
Subject: coalesce(json_agg [..] filter where [..], '[]' in left join returning null
Date: 2021-01-06 10:11:24
Message-ID: CANJtTDkpE980enFKd1ViGq569GYFwPzOEnKguyY8y84Zjok4Tg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

This query

select
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`

Gives as one of it's vat_cat (with no related vat_percentage records)
results

{ "vat_cat": { "id": 10, "descr": "nonsense", "expense": true, "version":
1, "vat_percentage": null } }

How do I get the query right so that it display [] instead of null?

--
Kind regards,

Stefan Houtzager

Houtzager ICT consultancy & development

www.linkedin.com/in/stefanhoutzager

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Pól Ua Laoínecháin 2021-01-06 13:40:56 Re: Best practice: call an internal postgresql function (e.g. raw_parser) from another C/Rust binary
Previous Message Bruce Momjian 2021-01-05 19:36:56 Re: project thru time