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 |
On Wed, Jan 6, 2021 at 3:11 AM Stefan Houtzager <[hidden email]> wrote:
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. |
Thanks David! When I run the following in pgadmin I get the error that follows at the bottom. Any idea how to get the query right? select json_agg( (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(NULLIF( 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 _) ), 'null'::json), '[]'::json) vat_percentage from vat_percentage vp group by vatcat_id ) vp on vc.id = vp.vatcat_id ERROR: operator does not exist: json = json
LINE 11: COALESCE(NULLIF(
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 306 On Wed, Jan 6, 2021 at 4:10 PM David G. Johnston <[hidden email]> wrote:
Kind regards,
Stefan Houtzager Houtzager ICT consultancy & development www.linkedin.com/in/stefanhoutzager |
On Wed, Jan 6, 2021 at 8:59 AM Stefan Houtzager <[hidden email]> wrote:
Right, json doesn't implement equality. You will need to cast to and compare text values. David J. |
Thanks again David. I am very novice, and tried the following which does not give an error but returns null instead of [] select json_agg( (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(NULLIF( 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 _) )::text, 'null'::text), '[]'::text) vat_percentage from vat_percentage vp group by vatcat_id ) vp on vc.id = vp.vatcat_id On Wed, Jan 6, 2021 at 5:10 PM David G. Johnston <[hidden email]> wrote:
Kind regards,
Stefan Houtzager Houtzager ICT consultancy & development www.linkedin.com/in/stefanhoutzager |
On Wed, Jan 6, 2021 at 9:32 AM Stefan Houtzager <[hidden email]> wrote:
Please don't top-post here. The general concept should work. You may at least wish to experiment in a simple query to get the feel for the mechanics. With respect to this query I'd probably need to have a working query to play with (and time) to give a more precise answer. David J. |
On Wed, Jan 6, 2021 at 5:38 PM David G. Johnston <[hidden email]> wrote:
Thanks David, I do not get what you suggest working within the left-join. What does work is the following: select json_agg(_) from ( select vc.id, vc.descr, vc.expense, vc.version, (select coalesce(json_agg(_), '[]') 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 from vat_percentage vp where vp.vatcat_id = vc.id ) _ ) as percentages from vat_cat vc group by vc.id ) _ Kind regards, Stefan Houtzager Houtzager ICT consultancy & development www.linkedin.com/in/stefanhoutzager |
Free forum by Nabble | Edit this page |