Jordan Deitch <jwdeitch@gmail.com> writes:
> A json(b)_agg() will produce the following result when no results are
> passed in:
> "[null]"
> per:
> select jsonb_agg((select 1 where false));
Looks fine to me.
> I believe, generally speaking, '[]' would be the more appropriate output.
Why? What you gave it was one null value. An empty array result would
imply that there were zero inputs, which is wrong.
Perhaps you're confused about the way scalar sub-selects work? The
above is equivalent to "select jsonb_agg(null::integer)"; it's not
the same as
# select jsonb_agg(1) where false;jsonb_agg
-----------
(1 row)
Now you could legitimately argue that this case, where there are zero
input rows, should produce '[]' rather than a SQL null. But I think we
had that discussion already, and agreed that this behavior is more in
keeping with the behavior of SQL's standard aggregates, notably SUM().
You can use coalesce() to inject '[]' (or whatever result you want) for
the no-rows case:
# select coalesce(jsonb_agg(1), '[]') where false;coalesce
----------[]
(1 row)
regards, tom lane
