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

I've adjusted the statements so that you can clearly see that *there is a
difference* between a missing outer-joined entity and its columns being
actually set to NULL:
create table a (
id serial2 primary key,
name text
);

create table b (
name text,
a int2 references a(id)
);

insert into a (name) values ('a1'), ('a2'), ('a3');
insert into b (name, a) values
('b for a1', 1), ('another b for a1', 1), (null, null);

select a.*, json_agg(b) from
a
left join b on b.a = a.id or a.id = 3 and b.a is null
group by a.id;
select a.*, array_agg(b) from
a
left join b on b.a = a.id or a.id = 3 and b.a is null
group by a.id;
Therefore, *it is a bug*. Whether the collective handling of joined columns
as a NULL record has some justification or there are reasons for *not*
fixing this inconsistency, that's another matter.
Anyway, thanks for a second workaround.

On Fri, May 19, 2023 at 8:12 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)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.
>
>