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

On Fri, May 19, 2023 at 7:14 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

The OP is correct, the result for json_agg on an outer join where the input
is a composite column of the nullable-side of the join is a json array with
a single json null value. Likewise, for array_agg we produce a length one
array with a single SQL NULL.

I agree that, in at least the json_agg case, the json array that is
produced should be an json object with keys matching the names of the
fields of the composite. Absent that, representing "found no rows on the
nullable side of the join" should be represented by SQL NULL as the overall
result. Producing a value in the JSON array that isn't an object when the
input is a composite is a POLA violation.

That all said, it seems near impossible to change this behavior now. But
adding a note to the effect of: when aggregating a composite where the
fields are all null the simple null representation form will be used in the
resultant array instead of producing an object where all keys have null
values. (I haven't experimented with cases where there are matching rows in
the outer join but all the relevant columns actually end up with null
values in them)

David J.