Обсуждение: [HACKERS] json_agg produces nonstandard json

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

Jordan Deitch <jwdeitch@gmail.com> writes:
> However, I don't see consistency between the results of these two
> statements:

> select jsonb_agg((select 1 where false));
> select sum((select 1 where false));

Well, SUM() is defined to ignore null input values, which is not too
surprising as it couldn't do anything very useful with them.  So it ends
up deciding there are no input rows.  jsonb_agg() is defined to translate
null input values to JSON "null", which seems like a sane behavior to me
although I agree that they aren't exactly the same concept.
If you don't want that, you could suppress the null inputs with a FILTER
clause:

regression=# select jsonb_agg(x) from (values (1),(2),(null),(4)) v(x);   jsonb_agg
-----------------[1, 2, null, 4]
(1 row)

regression=# select jsonb_agg(x) filter (where x is not null) from (values (1),(2),(null),(4)) v(x);jsonb_agg
-----------[1, 2, 4]
(1 row)

regression=# select jsonb_agg(x) filter (where x is not null) from (values (null),(null),(null)) v(x);jsonb_agg
-----------
(1 row)

We could perhaps invent a "jsonb_agg_strict()" variant that skips
nulls for you.  But I'd want to see multiple requests before
concluding that it was worth carrying such a function.  The FILTER
workaround seems good enough if it's an infrequent need.
        regards, tom lane