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

Hello,

I'm sorry if this message brings up once more an already settled issue, but there's no public list of  bug reports for PostgreSQL (the release notes contain only acknowledged, solved bugs).

This problem was noticed in version 15.1, as I don't have the latest version at hand, but I've read the release notes for 15.2 and 15.3 and there's no sign of any related change. It's about the outcome of a SELECT statement which involves computing aggregate functions like json_agg, jsonb_agg and array_agg over columns that originate from outer-joined entities, when these entities are missing.

Here's a very simple schema to illustrate the problem:

create table a (
  id serial2 primary key,
  name text
);

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

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

Notice that entity named a2 in table a has no corresponding bs. Now run this query:

select a.*, json_agg(b) from
  a
  left join b on b.a = a.id
  group by a.id;

The aggregation column for entity a2 has value [null] (if array_agg() was used, the obtained value would be {NULL}).

Since the query without aggregation

select a.*, b.* from
  a
  left join b on b.a = a.id;

produces an entry for entity a2 with NULL values for columns belonging to table b, I expect the aggregation to produce either [{"name":null,"a":null}] or the SQL NULL for it, preferrably the latter.

I suppose there's a dillema regarding what to return: the aggregation is computed over one row, but its values are actually missing. The curent choice of PostgreSQL is somewhere in-between, which is inappropriate from both perspectives - a political compromise. Moreover, in the case of array_agg(), the returned value could have also originated from a single-row single-column actual NULL value!

Returning a simple SQL NULL is the most appropriate choice, in line with the general rule that aggregations over zero rows return NULL. For the database client, it has the benefit of being both cheap to detect and distinguishable from other cases.

Although there is a workaround for this problem, it implies checking a stupid condition for absolutely every row in the JOIN result:

select a.*, json_agg(b) filter (where b.name is not null) from
  a
  left join b on b.a = a.id
  group by a.id;

Thoughts?

> *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.

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

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.

sulfinu@gmail.com writes:
> This problem was noticed in version *15.1*, as I don't have the latest
> version at hand, but I've read the release notes for *15.2* and *15.3* and
> there's no sign of any related change. It's about the outcome of a SELECT
> statement which involves computing aggregate functions like json_agg,
> jsonb_agg and array_agg over columns that originate from outer-joined
> entities, when these entities are *missing*.

I see no bug here.  json_agg is defined as aggregating results equivalent
to what to_json() would produce, and what to_json() would produce for
NULL input is a NULL.

> *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.

Note that Postgres does draw a distinction between a NULL of composite
type and a value of composite type whose fields all happen to be NULL.
The SQL spec is a bit squishy on this, in that they require a
"composite_value IS NULL" test to return TRUE for both cases, but
careful parsing of the spec seems to indicate that they aren't saying
there is no distinction.

> Returning a simple SQL NULL is the most appropriate choice, in line with the
> general rule that aggregations over zero rows return NULL
> <https://www.postgresql.org/docs/current/functions-aggregate.html>.

Um, you did *not* aggregate over zero rows: the FROM/GROUP BY construct
definitely produced a row for a.id = 2.

> Although there is a workaround for this problem, it implies checking a
> stupid condition for absolutely every row in the JOIN result:
> select a.*, json_agg(b) filter (where b.name is not null) from
>   a
>   left join b on b.a = a.id
>   group by a.id;

json_agg_strict() is easier.

                        regards, tom lane

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:
Therefore, it is a bug.