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

On Fri, Jul 3, 2026 at 1:58 AM Thom Brown <thom(at)linux(dot)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