Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023)

so 17. 1. 2026 v 10:54 odesílatel Jelte Fennema-Nio <postgres(at)jeltef(dot)nl>
napsal:

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

I am not sure if the implementation of the third method is the best we can
do.

Described handling of corner cases in SQL/JSON has some logic and
consistency, but it is not compatible with the generic philosophy of
PostgreSQL arrays. If I know ANSI/SQL doesn't know arrays, so this
inconsistency is just a PostgreSQL problem, and because we don't like
feature flags, I don't see any solution to how this situation can be
solved.

Any solution will be ugly. In this situation I prefer current behavior -
(inconsistency between array access and JSON_QUERY) with good description
in documentation.

Theoretically it can be introduced lax_postgres like you propose. But I
don't see how it can help with possible compatibility issues when somebody
will migrate from other databases.

So anything inside JSON_XXXX functions can be rigidly consistent with
standard SQL/JSON. Outside should not be true - and it is better to say it
explicitly. I don't think introducing some JavaScripts concepts to Postgres
(although just for some corner cases) is a good idea (when we have some
specific handling of some corner cases too).

Regards

Pavel