I'm returning a JSON array from a PostgreSQL request with the json_agg function. However, when no row is found, json_agg returns an empty string instead of an empty JSON array [] (square brackets are mandatory if I understand json.org).
For example:
SELECT json_agg(t.*) FROM (SELECT 'test' AS mycol WHERE 1 = 2) AS t ;
returns an empty string, whereas the same command with '1 = 1' returns a valid JSON array (tested with PostgreSQL 9.5).
Any idea?
where 1=2will never return any rows andwhere 1=1will return all rows. Of course the results are different. Why exactly do you find that surprising? – a_horse_with_no_name Jun 20 at 12:46json_aggshould return a valid JSON array (i.e.[]) when no row is selected. Instead,json_aggreturns an empty string. – pdagog Jun 20 at 12:56NULLnot an empty string. And this behavior is documented: "It should be noted that except for count, these functions return a null value when no rows are selected". If you don't want that, usecoalesce()– a_horse_with_no_name Jun 20 at 13:00