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

Question

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=2 will never return any rows and where 1=1 will return all rows. Of course the results are different. Why exactly do you find that surprising? — user330315
– user330315, Commented Jun 20, 2016 at 12:46
As explained in the question, IMHO json_agg should return a valid JSON array (i.e. []) when no row is selected. Instead, json_aggreturns an empty string. — pdagog
– pdagog, Commented Jun 20, 2016 at 12:56
It returns NULL not 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, use coalesce() — user330315
– user330315, Commented Jun 20, 2016 at 13:00

Clodoaldo Neto · Accepted Answer · 2016-06-20 13:08:29Z

49

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 
----------
 []

answered Jun 20, 2016 at 13:08

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dustin Hoffner Over a year ago

Works fine on 9.6

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related