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

On Fri, May 19, 2023 at 8:12 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > 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.
>
> Well, it *is*, if the input is a composite value.  A bare NULL isn't
> a composite value.  Observe the difference:
>
> regression=# select to_json(null::b);
>  to_json
> ---------
>
> (1 row)
>
> regression=# select to_json(row(null,null)::b);
>         to_json
> ------------------------
>  {"name":null,"a":null}
> (1 row)
>
>
Is there a place in our docs where the reader can learn that in the query:
"SELECT b FROM a LEFT JOIN b":

The reference to "b" in the target list, for rows where there is no match,
is constructed semantically via null:b as opposed to (b.col1, b.col2,
...)::b ?

David J.

The following does work if the object form of the JSON is desired.

select a.*, json_agg((b.name, b.a)::b) from
  a
  left join b on b.a = a.id
  group by a.id;

(one cannot avoid writing out the column names here since any reference to
plain "b" or "b.*" results in the scalar null construction of b coming into
play)

David J.