Re: SQL/JSON: JSON_TRANSFORM (SQL standard, subclause 6.44)

Hi Zsolt, thanks for looking into this.

On Fri, Jul 3, 2026 at 3:48 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
wrote:

>
> + analyzed_jst_action->pathspec = coerced_path_spec;
> + jsexpr->action = analyzed_jst_action;
>
> Shouldn't jsexpr->patch_spec also be set? This currently crashes:
>
> CREATE TABLE t (id int, j jsonb);
> CREATE INDEX ON t ((JSON_TRANSFORM(j, REMOVE '$.a'))); -- crash
>

JSON_TRANSFORM doesn't set jsexpr->patch_spec, it because doesn't
use it, but yeah the same reason caused the segfault crash in
contain_mutable_functions_walker, so i used the jsexpr->action->pathspec
which JSON_TRANSFORM uses for each operation.

@@ -432,11 +432,17 @@ contain_mutable_functions_walker(Node *node, void
*context)
{
JsonExpr *jexpr = castNode(JsonExpr, node);
Const *cnst;
+ Node *path_spec;

- if (!IsA(jexpr->path_spec, Const))
+ if(jexpr->action)
+ path_spec = jexpr->action->pathspec;
+ else
+ path_spec = jexpr->path_spec;
+
+ if (!IsA(path_spec, Const))
return true;

- cnst = castNode(Const, jexpr->path_spec);
+ cnst = castNode(Const, path_spec);

Assert(cnst->consttype == JSONPATHOID);
if (cnst->constisnull)

>
> Another thing I noticed is that deparse support is missing:
>
> EXPLAIN (VERBOSE) SELECT JSON_TRANSFORM('{"a":1}'::jsonb, REMOVE '$.a');
>

This was missing because I didn't teach get_rule_expr about JSON_TRANSFORM
at all,
so now I added OPs, pathspec, value_expr and behaviours for each action
into the
expression parser.

EXPLAIN (VERBOSE) SELECT JSON_TRANSFORM('{"a":1}'::jsonb, REMOVE '$.a');
QUERY PLAN

-------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=32)
Output: JSON_TRANSFORM('{"a": 1}'::jsonb, REMOVE '$."a"' IGNORE ON
MISSING)
(2 rows)

>
>
> Shouldn't the following statement work?
>
> SELECT JSON_TRANSFORM('{"arr":[{"a":1}]}', REPLACE 'lax $.arr.a' = '9');
>

This is a valid statement, target path walker currently ignores the mode
and only
goes through objects, so it silently no-ops, that's the gap. To close it,
the walker needs
when a .key/.* step lands on an array, unwrap in lax mode (recurse into
each element),
error in strict mode, will work on this and will include the above changes
into next patch
set, along with some other todos.

--
Thanks :)
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/