Re: EXPLAIN (VERBOSE) fails with for JSON_ARRAYAGG/JSON_OBJECTAGG + window function

On Fri, Jul 3, 2026 at 7:10 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> Reproduced here. get_json_agg_constructor() expects that ctor->func
> is Aggref or WindowFunc, but what it gets here is a Var.
>
> This is because the query has both window functions and grouped
> aggregates. make_window_input_target() flattens the final tlist using
> pull_var_clause, which pulls the Aggref out of its JsonConstructorExpr
> wrapper. Then fix_upper_expr() matches that inner Aggref against the
> Agg subplan's tlist and replaces it with an OUTER Var.

Here is the patch. It's a bit annoying that the original JSON agg
syntax then appears nowhere in the EXPLAIN output. All we get is the
bare jsonb_agg_strict Aggref.

WindowAgg
Output: g, (jsonb_agg_strict(name)), row_number() OVER w1
Window: w1 AS (ROWS UNBOUNDED PRECEDING)
-> HashAggregate
Output: g, jsonb_agg_strict(name)

The reason is that the JsonConstructorExpr wrapper and the Aggref it
wraps end up in different plan nodes, and neither alone suffices to
reconstruct the syntax.

I had an attempt to reconstruct the JSON syntax for the WindowAgg node
by leveraging resolve_special_varno(), and that works. But I don't
know how to do that for the HashAggregate node, because the
JsonConstructorExpr wrapper simply doesn't exist at that plan level.
Maybe we can hack the planner to make make_window_input_target() keep
the JsonConstructorExpr together with its Aggref. But I think that is
too invasive and it changes which node evaluates the wrapper.

So that attempt ended up with:

WindowAgg
Output: g, JSON_ARRAYAGG(name RETURNING jsonb), row_number() OVER w1
Window: w1 AS (ROWS UNBOUNDED PRECEDING)
-> HashAggregate
Output: g, jsonb_agg_strict(name)

But I don't think this is good. It fails to state the fact that the
WindowAgg doesn't compute a JSON aggregate; it passes through a value
that the HashAggregate computed. So I gave up this idea.

- Richard