From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | sulfinu(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN |
Date: | 2023-05-19 15:46:49 |
Message-ID: | CAKFQuwZUY0WMW5Jx6P6wdAv6XMXLoW_iPeLVGGRyA1p4ZUBV_Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Fri, May 19, 2023 at 8:12 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | sulfinu | 2023-05-19 16:59:30 | Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN |
Previous Message | Tom Lane | 2023-05-19 15:12:54 | Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN |