public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jelte Fennema-Nio <[email protected]>
To: Alexandra Wang <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Subject: Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023)
Date: Sat, 17 Jan 2026 10:54:15 +0100
Message-ID: <CAGECzQRWAJeqT8JWDOBp2wxFXrmyi9YNFgAhrPOMM6_feyNX9g@mail.gmail.com> (raw)
In-Reply-To: <CAK98qZ1P_edO9hZJVMuUpnXjN-9H=dz2zUj-06G5qW9CMjLqzA@mail.gmail.com>
References: <CAK98qZ1P_edO9hZJVMuUpnXjN-9H=dz2zUj-06G5qW9CMjLqzA@mail.gmail.com>
On Fri, 16 Jan 2026 at 22:27, Alexandra Wang
<[email protected]> wrote:
> In all cases above, json_query() follows the SQL/JSON standard
> specification. jsonb subscripting, which predates the standard,
> differs in several ways:
My thoughts on changing these 3 behaviours:
> 1. Array access on non-arrays (scalars or objects) does not use 'lax'
> mode wrapping. As a result, "[0]" does not return the original value.
I think it's unlikely anyone cares about the exact behaviour here in
practice. But changing the behaviour could corrupt expression indexes
that use this syntax.
> 2. Non-integer subscripts are not supported.
Changing this to not throw an error seems fine to me. Making something
that throw an error, now not throw an error should not cause breakage
for people. The new behaviour would be of arguable usefulness though.
> 3. Negative subscripts use a PostgreSQL-specific extension.
I think there are probably people relying on it. And postgres
behaviour actually seems way more useful than the SQL Standard
behaviour.
You said DuckDB does the same as Postgres. That doesn't surprise me
much, since DuckDB usually defaults to Postgres behaviour. They don't
care much about being strictly sql standard compliant, if that means
more sensible/useful SQL for their users. And since many of their
users are used to Postgres, they try to stay PostgreSQL compatible in
their SQL (unless they think the postgres behaviour is really
weird/confusing).
I do wonder what other databases do though. Does Oracle, MySQL or
MSSQL actually follow the standard here? i.e how incompatible is this
behaviour in practice with other databases?
> I would very much appreciate any thoughts or guidance on this.
If change 3 would not have been there, I would have probably been okay
with changing Postgres to behave like the SQL standard and telling
people to re-index their indexes that use this syntax in that major
release. But I think we should keep our current behaviour for option
3.
An approach that I think would be viable to do that is:
1. Define a new sqlpath mode (e.g. with the name lax_postgres or
something). And define that as our current behaviour (possibly with 2
changed to behave like lax).
2. Document that our SQL/JSON simplified accessors diverge slightly
from the SQL standard because they use lax_postgres instead of lax.
That would mean there's still an easy 1-to-1 translation between the
simplified accessor string and and JSON_QUERY (all that would be
different is the change from lax to lax_postgres in the string)
view thread (4+ 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], [email protected]
Subject: Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023)
In-Reply-To: <CAGECzQRWAJeqT8JWDOBp2wxFXrmyi9YNFgAhrPOMM6_feyNX9g@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