MIME-Version: 1.0
References: 
 <CAA-aLv5QYTaMOk=Qhv6cgwceeHETZV8YJvWZ_rH+yVZCuchATA@mail.gmail.com>
 <CAMbWs4_0_DmnVc=oHbkqOCMnwwdM5GUDZzioPA+o4-WTsNnYSQ@mail.gmail.com>
 <CAMbWs4-b2By5XFEn-_ZJKgN4-8SBhAoRZJrt4gwmM2ctOQzAyw@mail.gmail.com>
 <CAA-aLv6p3tMdY4KJ9wG_DxL1_3MTMCeKj8zUo+1-MctSBmvfdw@mail.gmail.com>
 <CAMbWs4_+Xh6JRTUrxBHhr2-5e+5Y74vne_xR_Dr_=Y0t=5cjgg@mail.gmail.com>
In-Reply-To: 
 <CAMbWs4_+Xh6JRTUrxBHhr2-5e+5Y74vne_xR_Dr_=Y0t=5cjgg@mail.gmail.com>
From: Thom Brown <thom@linux.com>
Date: Tue, 7 Jul 2026 14:49:23 +0100
Message-ID: 
 <CAA-aLv5Tybb_PAG8vmxB8KJXNgwqEBtAnd_nneP2YNahxs9irg@mail.gmail.com>
Subject: Re: EXPLAIN (VERBOSE) fails with for JSON_ARRAYAGG/JSON_OBJECTAGG +
 window function
To: Richard Guo <guofenglinux@gmail.com>
Cc: pgsql-bugs <pgsql-bugs@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAA-aLv5Tybb_PAG8vmxB8KJXNgwqEBtAnd_nneP2YNahxs9irg%40mail.gmail.com>
Precedence: bulk

On Mon, 6 Jul 2026 at 03:17, Richard Guo <guofenglinux@gmail.com> wrote:
>
> On Fri, Jul 3, 2026 at 5:36=E2=80=AFPM Thom Brown <thom@linux.com> wrote:
> > Thanks for taking a look at this. It's unfortunate that reconstructing
> > the syntax is problematic, because in the case of the original query
> > that tripped on this bug, I lose the following from my original query:
> > ...
> > I guess the relevant information can be gleaned from this, but not for
> > JSON_ARRAYAGG(i RETURNING text) because we get the same output whether
> > we're returning text or json.
>
> Yeah, the v1 patch is lossy, and the lost information cannot be
> recovered from the plan at all.
>
> So I'm switching back to the resolve_special_varno() reconstruction I
> had tried and set aside.  My earlier objection was that it makes the
> WindowAgg look like it computes a JSON aggregate when it only passes
> through a value computed below.  But the lower node still prints
> jsonb_agg_strict(name), so the plan does show where the aggregate is
> computed, and I think that is a smaller cost than losing the
> information outright.  Also, partial aggregation already deparses this
> way.  The combining aggregate's argument is a Var referencing the
> partial aggregate's output, and get_agg_expr() resolves it back with
> resolve_special_varno() to reprint the aggregate at the finalizing
> node.
>
> I still don't want to hack the planner to keep the JsonConstructorExpr
> with its Aggref in make_window_input_target(), because I still think
> that that is too invasive and changes which node evaluates the
> wrapper.
>
> Hence, I end up with the attached v2 patch.

I've tested it against the output from the 1st patch.

Patch 1:
((jsonb_agg_strict(e.emp_name ORDER BY e.emp_name)))

Patch 2:
(JSON_ARRAYAGG(e.emp_name ORDER BY e.emp_name RETURNING jsonb))

1:
((jsonb_object_agg_strict(e.emp_name, e.salary)))

2:
(JSON_OBJECTAGG(e.emp_name : e.salary ABSENT ON NULL RETURNING jsonb))

With that last one, why is that colon there?

Thom