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 CAKFQuwazRkKKEe+rMZcXKcKWiO-QwcOQNviV5tf0KzGTx_KQ-A@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  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
Дерево обсуждения
On Fri, May 19, 2023 at 7:14 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> *I expect the aggregation to produce either
> [{"name":null,"a":null}] or the SQL NULL for it, preferrably the latter.*

AFAICS, it *does* produce the latter, so you are not making yourself
very clear here.

The OP is correct, the result for json_agg on an outer join where the input is a composite column of the nullable-side of the join is a json array with a single json null value.  Likewise, for array_agg we produce a length one array with a single SQL NULL.

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.  Absent that, representing "found no rows on the nullable side of the join" should be represented by SQL NULL as the overall result.  Producing a value in the JSON array that isn't an object when the input is a composite is a POLA violation.

That all said, it seems near impossible to change this behavior now.  But adding a note to the effect of: when aggregating a composite where the fields are all null the simple null representation form will be used in the resultant array instead of producing an object where all keys have null values. (I haven't experimented with cases where there are matching rows in the outer join but all the relevant columns actually end up with null values in them)

David J.


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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN