From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: sulfinu@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Aggregation results with json(b)_agg and array_agg in a SELECT
 with OUTER JOIN
In-reply-to: 
 <CAKFQuwazRkKKEe+rMZcXKcKWiO-QwcOQNviV5tf0KzGTx_KQ-A@mail.gmail.com>
References: 
 <CAGH1kmzre_Nr+mnErVLbAgGpOAmho_VQwY+uMzmVqEzaZKkPEA@mail.gmail.com>
 <35907.1684505642@sss.pgh.pa.us>
 <CAKFQuwazRkKKEe+rMZcXKcKWiO-QwcOQNviV5tf0KzGTx_KQ-A@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Fri, 19 May 2023 07:36:49 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <99859.1684509174.1@sss.pgh.pa.us>
Date: Fri, 19 May 2023 11:12:54 -0400
Message-ID: <99860.1684509174@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/99860.1684509174%40sss.pgh.pa.us>
Precedence: bulk

"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(null::b) is null;
 ?column? 
----------
 t
(1 row)

regression=# select to_json(row(null,null)::b);
        to_json         
------------------------
 {"name":null,"a":null}
(1 row)

As I said, we do not treat null::b and row(null,null)::b exactly
alike.  We could spend a long time arguing about the merits of that
and whether or not it exactly satisfies the SQL spec, but at the end
of the day, the odds of it changing in Postgres are epsilon.  I do
not agree that it's wrong, and even if I did I doubt we'd take the
compatibility hit of changing it.

			regards, tom lane