pdagog pdagog - 1 year ago 220
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

function. However, when no row is found,
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 Source

json_agg returns null from an empty set:

select json_agg(t.*) is null
from (select 'test' as mycol where 1 = 2) 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 ;