Thread: [HACKERS] json_agg produces nonstandard json
Hello!
select jsonb_agg((select 1 where false));
I apologize in advanced if this has been previously discussed;
A json(b)_agg() will produce the following result when no results are passed in:
"[null]"
per:
I believe, generally speaking, '[]' would be the more appropriate output.
Would postgres welcome a patch to handle the empty case of json(b)_agg?
Thanks!
---
Jordan Deitch
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
Thank you for responding!
select jsonb_agg((select 1 where false));
select sum((select 1 where false));
Good points.
However, I don't see consistency between the results of these two statements:
select sum((select 1 where false));
Therefore another option I would like to suggest is returning the same null value-types for the sum() and json_agg().
So the select jsonb_agg((select 1 where false)); would return null as opposed to [null]. In this case it would be compatible with coalesce()
---
Thanks
Jordan Deitch
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