Re: [HACKERS] json_agg produces nonstandard json

[Tom Lane]

Jordan Deitch  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] json_agg produces nonstandard json

[Jordan Deitch]

Thank you for responding!

Good points.

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));

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

Re: [HACKERS] json_agg produces nonstandard json

[Tom Lane]

Jordan Deitch  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] json_agg produces nonstandard json

[Jordan Deitch]

Hello!

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:

select jsonb_agg((select 1 where false));

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