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

Hi,

On Wed, Jul 8, 2026 at 10:53 PM Srinath Reddy Sadipiralla <
srinath2133(at)gmail(dot)com> wrote:

> 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.
>

v3-0004: included above 2 fixes and added lax/strict mode into
the JSON_TRANSFORM
walker reusing the JSONPATH_LAX logic similar to jsonb_path_query.

--
Thanks :)
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/
"without the rope then fear will find you again"