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

Hello

I do not have access to the standard, so I can only look at the patch based on the Oracle documentation - my questions are based on that.

1. Isn't rename supposed to default to `REPLACE ON EXISTING`? [1]https://docs.oracle.com/en/database/oracle/oracle-database/26/adjsn/json_transform-operator-rename.html

SELECT JSON_TRANSFORM('{"a":1,"b":2}'::jsonb, RENAME '$.a' = 'b'); -- returns {"b":2}, shouldn't be {"b":1}?

2. I'm also not sure about the insert behavior [2]https://docs.oracle.com/en/database/oracle/oracle-database/26/adjsn/json_transform-operator-insert.html, currently the above query doesn't do anything:

SELECT JSON_TRANSFORM('{"x":1}'::jsonb, INSERT '$.a.b' = '9');

The Oracle documentation says that the default behavior is `INSERT ON MISSING`, but also that "path expression must target either a field of an object or an array position (otherwise, an error is raised).". The correct behavior for this should be either inserting the record, or raising an error. Probably the latter.

3. Unfortunately I wasn't able to find anything in the documentation about the case where a wildcard doesn't match anything, such as:

SELECT JSON_TRANSFORM('{}', REMOVE '$.*' ERROR ON MISSING);
SELECT JSON_TRANSFORM('{}', REPLACE '$.*' = '9' ERROR ON MISSING);

Currently these report an error, which might be correct, but seems somewhat strange to me, so I wanted to mention it to confirm the behavior.

[1]: https://docs.oracle.com/en/database/oracle/oracle-database/26/adjsn/json_transform-operator-rename.html
[2]: https://docs.oracle.com/en/database/oracle/oracle-database/26/adjsn/json_transform-operator-insert.html

Hi Zsolt,

Thanks for looking into this, currently for this patch set, i am following
SQL
standard but I'd really like the community's view on the overall direction
for JSON_TRANSFORM: should we follow the SQL standard strictly, aim for
Oracle compatibility, or take a Postgres-native approach where they
conflict? I'm happy to go whichever way there's consensus on.

On Sun, Jun 21, 2026 at 3:01 AM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:

1. Isn't rename supposed to default to `REPLACE ON EXISTING`? [1]

SELECT JSON_TRANSFORM('{"a":1,"b":2}'::jsonb, RENAME '$.a' = 'b'); --
returns {"b":2}, shouldn't be {"b":1}?

This is simply a bug on my side. The current {"b":2} is an result of
jsonb's silent key de-duplication, not intended behavior: renaming '$.a' to
'b' produces {"b":1,"b":2}, and since a JSON object can't have duplicate
keys this should raise an error , which is what both the SQL standard and
Oracle do:
- SQL standard: data exception , non-unique keys in a JSON object.
- Oracle: ORA-40767, "field with this name already exists".
So it doesn't actually default to REPLACE ON EXISTING, on Oracle this
raises an error rather than producing {"b":1}. I'll fix RENAME to detect
the
collision and raise an error, which matches both the standard and Oracle.

2. I'm also not sure about the insert behavior [2], currently the
above query doesn't do anything:

SELECT JSON_TRANSFORM('{"x":1}'::jsonb, INSERT '$.a.b' = '9');

The Oracle documentation says that the default behavior is `INSERT ON
MISSING`, but also that "path expression must target either a field of
an object or an array position (otherwise, an error is raised).". The
correct behavior for this should be either inserting the record, or
raising an error. Probably the latter.

This one is as expected, and the standard and Oracle agree. Per the
standard, INSERT adds the member to the objects matched by the parent path
($.a); since $.a matches nothing in {"x":1}, there's nothing to insert into,
so the document is returned unchanged (the standard has no ON MISSING for
INSERT and doesn't create the intermediate object). Oracle does the same,
the exact query returns the input unchanged:
SQL> SELECT JSON_TRANSFORM('{"x":1}', INSERT '$.a.b' = '9');
{"x":1}
So the no-op the patch produces matches both.

3. Unfortunately I wasn't able to find anything in the documentation
about the case where a wildcard doesn't match anything, such as:

SELECT JSON_TRANSFORM('{}', REMOVE '$.*' ERROR ON MISSING);
SELECT JSON_TRANSFORM('{}', REPLACE '$.*' = '9' ERROR ON MISSING);

Currently these report an error, which might be correct, but seems
somewhat strange to me, so I wanted to mention it to confirm the
behavior.

This one comes straight from the standard: a wildcard matching no
members is an empty result, which counts as "target does not
exist", so ERROR ON MISSING raises; with the default (IGNORE ON MISSING)
it's a quiet no-op. I agree it reads a little odd for a wildcard, but it
falls out of the same rule as a named path matching nothing.

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

Hi,

changes in v2:

On Thu, Jun 18, 2026 at 9:51 PM Srinath Reddy Sadipiralla <
srinath2133@gmail.com> wrote:

- A column reference or sub-select used as a pathspec or value
currently crashes the backend (e.g. REPLACE '$.a' = some_column).

0001 - The cause was that expression_tree_walker did not go into the
action's sub-expressions (action->pathspec, action->value_expr). Because of
that, expr_setup_walker never counted the value's attribute number, so
last_scan was too low; the EEOP_SCAN_FETCHSOME step then deformed too few
attributes (tts_nvalid), and evaluating the Var failed
Assert(attnum >= 0 && attnum < scanslot->tts_nvalid).

The same gap in expression_tree_mutator caused a segfault with joins,
fix_join_expr runs through the mutator, so the value's Var was never
remapped;
a varno that isn't INNER/OUTER then defaults to a SCAN var, but a join node
has no scan slot, so CheckVarSlotCompatibility dereferenced a NULL scanslot.

Fixed by teaching both the walker and the mutator to traverse
action->pathspec and action->value_expr.

Also fixed the "label followed by a declaration" compile error clang
reported
in the RENAME parse-analysis case.

On Sun, Jun 21, 2026 at 7:18 PM Srinath Reddy Sadipiralla <
srinath2133@gmail.com> wrote:

On Sun, Jun 21, 2026 at 3:01 AM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:

1. Isn't rename supposed to default to `REPLACE ON EXISTING`? [1]

SELECT JSON_TRANSFORM('{"a":1,"b":2}'::jsonb, RENAME '$.a' = 'b'); --
returns {"b":2}, shouldn't be {"b":1}?

This is simply a bug on my side. The current {"b":2} is an result of
jsonb's silent key de-duplication, not intended behavior: renaming '$.a' to
'b' produces {"b":1,"b":2}, and since a JSON object can't have duplicate
keys this should raise an error , which is what both the SQL standard and
Oracle do:
- SQL standard: data exception , non-unique keys in a JSON object.
- Oracle: ORA-40767, "field with this name already exists".
So it doesn't actually default to REPLACE ON EXISTING, on Oracle this
raises an error rather than producing {"b":1}. I'll fix RENAME to detect
the
collision and raise an error, which matches both the standard and Oracle.

0002 - Fixed by enabling object key-uniqueness (parseState->unique_keys =
true) for the object targeted by RENAME, so a collision now raises an error
instead of silently de-duplicating. (It currently reuses jsonb's generic
"duplicate JSON object key value" error)

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

Thanks for looking into this, currently for this patch set, i am following
SQL
standard but I'd really like the community's view on the overall direction
for JSON_TRANSFORM: should we follow the SQL standard strictly, aim for
Oracle compatibility, or take a Postgres-native approach where they
conflict? I'm happy to go whichever way there's consensus on.

I would prefer the approach of following the standard - it just means I can't review it with standard compliance in mind, as I only have access to the publicly available oracle documentation.

+		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

Another thing I noticed is that deparse support is missing:

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

Shouldn't the following statement work?

SELECT JSON_TRANSFORM('{"arr":[{"a":1}]}', REPLACE 'lax $.arr.a' = '9');

Hi Zsolt, thanks for looking into this.

On Fri, Jul 3, 2026 at 3:48 AM Zsolt Parragi <zsolt.parragi@percona.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/

Hi,

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

Hi Zsolt, thanks for looking into this.

On Fri, Jul 3, 2026 at 3:48 AM Zsolt Parragi <zsolt.parragi@percona.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"

Hi,

Here is v4 of the JSON_TRANSFORM patch set. This revision closes the two
items that were still open in v3.

0005 is new. It adds the "= PATH <jsonpath>" source form for INSERT and
REPLACE, and enables the ON EMPTY and ON ERROR behaviour clauses that v3
parsed but rejected.

With a PATH source, the value to insert or replace is produced at run
time by evaluating the source jsonpath against the input document, the
same way JSON_QUERY without a wrapper does. The routing follows
SQL:2023 6.44 GR 5:

- a source path returning no items triggers ON EMPTY
(default NULL ON EMPTY);
- a source path returning more than one item, or raising a structural
error, triggers ON ERROR (default ERROR ON ERROR).

A few representative cases, checked against the standard's General Rules:

SELECT JSON_TRANSFORM(jsonb '{"a":10}', INSERT '$.x' = PATH '$.a');
=> {"a": 10, "x": 10}

SELECT JSON_TRANSFORM(jsonb '{"a":10}', INSERT '$.x' = PATH '$.nope');
=> {"a": 10, "x": null} (empty source, default NULL ON EMPTY)

SELECT JSON_TRANSFORM(jsonb '{"arr":[1,2,3]}', INSERT '$.x' = PATH
'$.arr[*]');
=> ERROR (more than one item, ERROR ON ERROR)

SELECT JSON_TRANSFORM(jsonb '{"a":10}', INSERT '$.x' = PATH 'strict
$.nope');
=> ERROR (structural error, routed to ON ERROR)

Patches 0001 through 0004 are unchanged from v3 apart from the version
number.

--
Thanks :)
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/
"there's no hogwarts without you hagrid"