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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN
Дата
Msg-id CAKFQuwZUY0WMW5Jx6P6wdAv6XMXLoW_iPeLVGGRyA1p4ZUBV_Q@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN  (sulfinu@gmail.com)
Список pgsql-bugs
Дерево обсуждения
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.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: 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