Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN
| От | sulfinu@gmail.com |
|---|---|
| Тема | Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN |
| Дата | |
| Msg-id | CAGH1kmxga8Bw7zHrg31a=BGOK7f_U=jVnH+=XGR+HV9_eCfkwA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Ответы |
Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN
|
| Список | pgsql-bugs |
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.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;
Anyway, thanks for a second workaround.
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.(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.
В списке pgsql-bugs по дате отправления: