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

 I did spend some time putting together this bug report, so, out of minimal
courtesy, please reply *only after* reading my message and executing those
statements.
Also, there is no such thing as "json_agg_strict()" in PostgreSQL *15*.


sulfinu@gmail.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
>