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

On Fri, Jul 3, 2026 at 1:58 AM Thom Brown <thom@linux.com> wrote:

CREATE TEMP TABLE t (g int, name text);
INSERT INTO t VALUES (1, 'a'), (1, 'b'), (2, 'c');

EXPLAIN (VERBOSE)
SELECT g,
JSON_ARRAYAGG(name RETURNING jsonb) AS names,
row_number() OVER (ORDER BY g) AS rn
FROM t
GROUP BY g;

ERROR: invalid JsonConstructorExpr underlying node type: 6

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.

A simple fix is:

--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -12389,6 +12389,8 @@ get_json_agg_constructor(JsonConstructorExpr
*ctor, deparse_context *context,
        get_windowfunc_expr_helper((WindowFunc *) ctor->func, context,
                                   funcname, options.data,
                                   is_json_objectagg);
+   else if (IsA(ctor->func, Var))
+       get_rule_expr((Node *) ctor->func, context, false);
    else
        elog(ERROR, "invalid JsonConstructorExpr underlying node type: %d",
             nodeTag(ctor->func));

Will work on a patch on it.

- Richard

On Fri, Jul 3, 2026 at 7:10 AM Richard Guo <guofenglinux@gmail.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

On Fri, 3 Jul 2026 at 04:06, Richard Guo <guofenglinux@gmail.com> wrote:

On Fri, Jul 3, 2026 at 7:10 AM Richard Guo <guofenglinux@gmail.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.

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:

JSON_ARRAYAGG(i RETURNING jsonb)
is instead:
jsonb_agg_strict(i)

And the same with: JSON_ARRAYAGG(…)

JSON_ARRAYAGG(i RETURNING json)
is instead:
json_agg_strict(i)

And the same with: JSON_ARRAYAGG(i RETURNING text)

WITH UNIQUE KEYS
is instead:
json_object_agg_unique(i,i)

NULL ON NULL
This isn't reconstructed

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.

Thom

On Fri, Jul 3, 2026 at 5:36 PM 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.

- Richard

On Mon, 6 Jul 2026 at 03:17, Richard Guo <guofenglinux@gmail.com> wrote:

On Fri, Jul 3, 2026 at 5:36 PM 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

On Tue, Jul 7, 2026 at 11:20 PM Thom Brown <thom@linux.com> wrote:

So all looks good to me.

Pushed and back-patched.

- Richard

On Wed, 8 Jul 2026 at 01:08, Richard Guo <guofenglinux@gmail.com> wrote:

On Tue, Jul 7, 2026 at 11:20 PM Thom Brown <thom@linux.com> wrote:

So all looks good to me.

Pushed and back-patched.

Thanks. Much appreciate you working on this.

Thom