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

From: Thom Brown <[email-masked]>
Date: Thu, 2 Jul 2026 17:58:07 +0100

Hi,

EXPLAIN (VERBOSE) throws an internal error when a query's target list
contains an SQL/JSON aggregate (JSON_ARRAYAGG or JSON_OBJECTAGG)
together with a window function (although I suspect it's for anything
that forces a plan node on top of the aggregation node). I ran into it
when trying to construct an obscenely complex SELECT statement.

The query itself executes fine. Only the attempt to deparse its plan
for EXPLAIN (VERBOSE) fails.

I'm using the latest master branch, but I believe the bug is also
present in 16, 17 and 18, since the responsible code has been
unchanged since SQL/JSON constructors were added by commit 7081ac46ace
in 2023.

Debian, Linux 6.12.90 (6.12.90+deb13.1-amd64), x86_64
gcc (Debian 14.2.0-19) 14.2.0
configure options:
  --with-llvm --with-libxml --with-ossp-uuid --with-lz4
  --with-liburing --with-openssl

No non-default server settings are required to reproduce.

Reproducible test case:

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;

Result:

ERROR:  invalid JsonConstructorExpr underlying node type: 6

The same happens with JSON_OBJECTAGG. Note that without EXPLAIN
(VERBOSE), or just using EXPLAIN with any options except VERBOSE, the
query runs and returns correct results.

Removing the window function makes EXPLAIN (VERBOSE) work, because
then a single Aggregate node computes the aggregate and there is no
upper node to reference it from.

Expected result:

 WindowAgg
   Output: g, JSON_ARRAYAGG(name RETURNING jsonb), row_number() OVER w1
   ...
   ->  HashAggregate
         Output: g, jsonb_agg_strict(name)

Regards

Thom

---

From: Richard Guo <[email-masked]>
Date: Fri, 3 Jul 2026 07:10:08 +0900

> 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

---

From: Richard Guo <[email-masked]>
Date: Fri, 3 Jul 2026 12:06:45 +0900

> 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

---

From: Thom Brown <[email-masked]>
Date: Fri, 3 Jul 2026 09:36:09 +0100

> 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

---

From: Richard Guo <[email-masked]>
Date: Mon, 6 Jul 2026 11:17:16 +0900

> 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

---

From: Thom Brown <[email-masked]>
Date: Tue, 7 Jul 2026 14:49:23 +0100

> 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

---

From: Thom Brown <[email-masked]>
Date: Tue, 7 Jul 2026 15:20:14 +0100

> With that last one, why is that colon there?

Actually, I don't know what I was thinking. This is clearly a
key:value pairing, so ignore that remark. But we do get more info with
this line in the 2nd patch.

So all looks good to me.

Thanks

Thom

---

From: Richard Guo <[email-masked]>
Date: Wed, 8 Jul 2026 09:08:12 +0900

> So all looks good to me.

Pushed and back-patched.

- Richard

---

From: Thom Brown <[email-masked]>
Date: Wed, 8 Jul 2026 01:42:28 +0100

> On Tue, Jul 7, 2026 at 11:20 PM Thom Brown <[email-masked]> wrote:

Thanks. Much appreciate you working on this.

Thom