Postgres Pro
Downloads
Home   >   mailing lists

Re: coalesce(json_agg [..] filter where [..], '[]' in left join returning null - Mailing list pgsql-novice

From David G. Johnston
Subject Re: coalesce(json_agg [..] filter where [..], '[]' in left join returning null
Date
Msg-id CAKFQuwb+d5sfx2ALRcM-H3N=aK4wH32j42BdJJpcOuOszEo4Cw@mail.gmail.com
Whole thread Raw
In response to coalesce(json_agg [..] filter where [..], '[]' in left join returning null  (Stefan Houtzager <stefan.houtzager@gmail.com>)
Responses Re: coalesce(json_agg [..] filter where [..], '[]' in left join returning null
List pgsql-novice
Tree view
On Wed, Jan 6, 2021 at 3:11 AM Stefan Houtzager <stefan.houtzager@gmail.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.

pgsql-novice by date:

Previous
From: Pól Ua Laoínecháin
Date:
Subject: Re: Best practice: call an internal postgresql function (e.g. raw_parser) from another C/Rust binary
Next
From: Stefan Houtzager
Date:
Subject: Re: coalesce(json_agg [..] filter where [..], '[]' in left join returning null