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

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(dot)parragi(at)percona(dot)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/