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