public inbox for [email protected]
help / color / mirror / Atom feedFrom: Srinath Reddy Sadipiralla <[email protected]>
To: Zsolt Parragi <[email protected]>
Cc: [email protected]
Subject: Re: SQL/JSON: JSON_TRANSFORM (SQL standard, subclause 6.44)
Date: Sun, 21 Jun 2026 19:18:19 +0530
Message-ID: <CAFC+b6rX08OYaQSGNWmGfjhBCE-Qf7tAtbXzv=D9K++4W=LDBg@mail.gmail.com> (raw)
In-Reply-To: <CAN4CZFPN=887T0njqw+bOHUiO6ion6rR=b1+Ecx+gAaCuL89Wg@mail.gmail.com>
References: <CAFC+b6pQxA0-YV8B-8KFub_bix_wdhA+oHrv7S1rYb2tQjjPzg@mail.gmail.com>
<CAN4CZFPN=887T0njqw+bOHUiO6ion6rR=b1+Ecx+gAaCuL89Wg@mail.gmail.com>
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/
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: SQL/JSON: JSON_TRANSFORM (SQL standard, subclause 6.44)
In-Reply-To: <CAFC+b6rX08OYaQSGNWmGfjhBCE-Qf7tAtbXzv=D9K++4W=LDBg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox