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