Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN

sulfinu(at)gmail(dot)com writes:
> This problem was noticed in version *15.1*, as I don't have the latest
> version at hand, but I've read the release notes for *15.2* and *15.3* and
> there's no sign of any related change. It's about the outcome of a SELECT
> statement which involves computing aggregate functions like json_agg,
> jsonb_agg and array_agg over columns that originate from outer-joined
> entities, when these entities are *missing*.

I see no bug here. json_agg is defined as aggregating results equivalent
to what to_json() would produce, and what to_json() would produce for
NULL input is a NULL.

> *I expect the aggregation to produce either
> [{"name":null,"a":null}] or the SQL NULL for it, preferrably the latter.*

AFAICS, it *does* produce the latter, so you are not making yourself
very clear here.

Note that Postgres does draw a distinction between a NULL of composite
type and a value of composite type whose fields all happen to be NULL.
The SQL spec is a bit squishy on this, in that they require a
"composite_value IS NULL" test to return TRUE for both cases, but
careful parsing of the spec seems to indicate that they aren't saying
there is no distinction.

> Returning a simple SQL NULL is the most appropriate choice, in line with the
> general rule that aggregations over zero rows return NULL
> <https://www.postgresql.org/docs/current/functions-aggregate.html>.

Um, you did *not* aggregate over zero rows: the FROM/GROUP BY construct
definitely produced a row for a.id = 2.

> Although there is a workaround for this problem, it implies checking a
> stupid condition for absolutely every row in the JOIN result:
> select a.*, json_agg(b) filter (where b.name is not null) from
> a
> left join b on b.a = a.id
> group by a.id;

json_agg_strict() is easier.

regards, tom lane