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 <[email protected]> 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/
