| From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
|---|---|
| To: | Stefan Houtzager <stefan(dot)houtzager(at)gmail(dot)com> |
| Cc: | pgsql-novice <pgsql-novice(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: coalesce(json_agg [..] filter where [..], '[]' in left join returning null |
| Date: | 2021-01-06 15:09:45 |
| Message-ID: | CAKFQuwb+d5sfx2ALRcM-H3N=aK4wH32j42BdJJpcOuOszEo4Cw@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-novice |
On Wed, Jan 6, 2021 at 3:11 AM Stefan Houtzager <stefan(dot)houtzager(at)gmail(dot)com>
wrote:
> 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
> { "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?
>
Use COALESCE(NULLIF(..., 'null'::json), '[]'::json); NULLIF converts the
JSON null into SQL NULL which the COALESCE then replaces with a empty json
array.
David J.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Stefan Houtzager | 2021-01-06 15:59:44 | Re: coalesce(json_agg [..] filter where [..], '[]' in left join returning null |
| Previous 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 |