MIME-Version: 1.0
References: <CAK98qZ1P_edO9hZJVMuUpnXjN-9H=dz2zUj-06G5qW9CMjLqzA@mail.gmail.com>
In-Reply-To: <CAK98qZ1P_edO9hZJVMuUpnXjN-9H=dz2zUj-06G5qW9CMjLqzA@mail.gmail.com>
From: Jelte Fennema-Nio <postgres@jeltef.nl>
Date: Sat, 17 Jan 2026 10:54:15 +0100
Message-ID: <CAGECzQRWAJeqT8JWDOBp2wxFXrmyi9YNFgAhrPOMM6_feyNX9g@mail.gmail.com>
Subject: Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023)
To: Alexandra Wang <alexandra.wang.oss@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, 
	Peter Eisentraut <peter@eisentraut.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAGECzQRWAJeqT8JWDOBp2wxFXrmyi9YNFgAhrPOMM6_feyNX9g%40mail.gmail.com>
Precedence: bulk

On Fri, 16 Jan 2026 at 22:27, Alexandra Wang
<alexandra.wang.oss@gmail.com> 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)