pdagog pdagog - 5 months ago 88
JSON Question

Why PostgreSQL json_agg() function does not return an empty array?

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?

Answer

json_agg returns null from an empty set:

select json_agg(t.*) is null
from (select 'test' as mycol where 1 = 2) t ;
 ?column? 
----------
 t

If you want an empty json array coalesce it:

select coalesce(json_agg(t.*), '[]'::json)
from (select 'test' as mycol where 1 = 2) t ;
 coalesce 
----------
 []