Обсуждение: coalesce(json_agg [..] filter where [..], '[]' in left join returning null

                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?

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  

Thanks again David. I am very novice, and tried the following which does not give an error but returns null instead of []

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.