Thread
-
SQL/JSON: JSON_TRANSFORM (SQL standard, subclause 6.44)
Srinath Reddy Sadipiralla <srinath2133@gmail.com> — 2026-06-18T16:21:06Z
Hi, Peter Eisentraut suggested implementing JSON_TRANSFORM on the "Add jsonb_translate()" thread [1] and guided me off-list to work on this, thanks, Peter. Here is the WIP version. JSON_TRANSFORM (SQL/JSON, Feature T883, subclause 6.44) yields a new JSON value by applying a modification to an input JSON value. Per the standard a single call performs one operation, INSERT, REPLACE, REMOVE, or RENAME , at a jsonpath target, with optional per-operation behavior clauses: JSON_TRANSFORM(jsonb_doc, { INSERT path = value [ behaviors ] | REPLACE path = value [ behaviors ] | REMOVE path [ behaviors ] | RENAME path = name [ behaviors ] }) Examples: SELECT JSON_TRANSFORM('{"a":1,"b":2}', REMOVE '$.a'); -> {"b": 2} SELECT JSON_TRANSFORM('{"a":1}', REPLACE '$.a' = '9'::jsonb); -> {"a": 9} SELECT JSON_TRANSFORM('{"a":{"x":1}}', RENAME '$.a.x' = 'y'); -> {"a": {"y": 1}} -- wildcard member accessor: act on every member at a level SELECT JSON_TRANSFORM('{"p":{"k":1},"q":{"k":2}}', REMOVE '$.*.k'); -> {"p": {}, "q": {}} -- per-operation behavior SELECT JSON_TRANSFORM('{"a":1}', INSERT '$.a' = '9'::jsonb IGNORE ON EXISTING); -> {"a": 1} SELECT JSON_TRANSFORM('{"a":1}', REPLACE '$.x' = '5'::jsonb ERROR ON MISSING); -> ERROR: target in JSON_TRANSFORM does not exist Why have this, when jsonb_set / jsonb_insert / jsonb_delete_path already exist? - It is the SQL-standard, portable spelling for declarative JSON mutation; the jsonb_* functions are Postgres-specific. - This single operation already does things those functions cannot express in one call: the wildcard '.*' acts on every member at a level (e.g. REMOVE '$.*.password'); the ON EXISTING / ON MISSING / ON NULL clauses give conditional semantics they lack ("replace, else error"; "insert, but ignore if present"; "if the value is NULL, remove the key") that otherwise need CASE wrappers or jsonb_set_lax(). - NULL-safety: jsonb_set() is strict, so a NULL value collapses the whole result to NULL; JSON_TRANSFORM follows the standard's NULL ON NULL (store a JSON null). Scope and direction: Per the standard, JSON_TRANSFORM applies a single operation per call. Oracle's variant accepts a comma-separated list of operations applied in one pass. This patch follows the standard (one operation), which keeps the initial scope small, but the design has a clear path to multiple operations: JsonExpr.action becomes a list, and the executor applies each action as one streaming doc->doc pass in a loop, the per-action walker is already independent of any single-action assumption. I'd like to hear community's view on whether Postgres should stay with the standard's single-operation form or extend to Oracle-style multiple operations. Patch set (applies on master; each commit builds and the core regression suite passes): 0001 - Initial JSON_TRANSFORM implementation: grammar, parse analysis, and executor for INSERT / REPLACE / REMOVE / RENAME at a member-accessor jsonpath target, returning jsonb. 0002 - Rework execution into a single streaming pass over the input jsonb (rebuilt via the JsonbIterator / pushJsonbValue API) instead of delegating to jsonb_set / jsonb_insert / jsonb_delete_path on a text[] path. This enables the '.*' wildcard member accessor, the RENAME operation, and NULL ON NULL for INSERT/REPLACE. 0003 - Per-operation behavior clauses ON EXISTING / ON MISSING / ON NULL, resolved during parse analysis with the standard's implicit defaults. Each commit message has more details. Not yet implemented (planned): - '= PATH <jsonpath>' source form for INSERT/REPLACE. - ON EMPTY / ON ERROR behaviors (parsed but currently rejected; they are meaningful only with the PATH source form, and ON ERROR needs soft-error handling). - PASSING arguments (parsed but unused, a member/wildcard target path cannot reference a variable; I plan to reject them in parse analysis until the PATH source form lands). - Oracle-style multiple operations per call (pending the direction question above). - A column reference or sub-select used as a pathspec or value currently crashes the backend (e.g. REPLACE '$.a' = some_column). I'll register it in the July commitfest. [1] https://www.postgresql.org/message-id/8d3c7094-4b22-4c6c-a9e7-3f0b55f5ec04%40eisentraut.org -- Thanks :) Srinath Reddy Sadipiralla EDB: https://www.enterprisedb.com/ -
Re: SQL/JSON: JSON_TRANSFORM (SQL standard, subclause 6.44)
Zsolt Parragi <zsolt.parragi@percona.com> — 2026-06-20T21:30:58Z
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] 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], 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 -
Re: SQL/JSON: JSON_TRANSFORM (SQL standard, subclause 6.44)
Srinath Reddy Sadipiralla <srinath2133@gmail.com> — 2026-06-21T13:48:19Z
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/ -
Re: SQL/JSON: JSON_TRANSFORM (SQL standard, subclause 6.44)
Srinath Reddy Sadipiralla <srinath2133@gmail.com> — 2026-07-02T05:54:40Z
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/ -
Re: SQL/JSON: JSON_TRANSFORM (SQL standard, subclause 6.44)
Zsolt Parragi <zsolt.parragi@percona.com> — 2026-07-02T22:18:23Z
> 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'); -
Re: SQL/JSON: JSON_TRANSFORM (SQL standard, subclause 6.44)
Srinath Reddy Sadipiralla <srinath2133@gmail.com> — 2026-07-08T17:23:46Z
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/