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.
"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:
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)
