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

On Sat, 17 Jan 2026 at 11:22, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 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.

Array subscripting (aka indexing) and jsonb subscripting work
completely differently. A very important difference is that arrays use
1-based subscripting, while jsonb uses 0-based subscripting.

> 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.

I didn't mean to suggest it for compatibility reasons (although I do
think there's very little practical compatibility risk with keeping
our current behaviour). It seemed mostly nice so that we can have a
simplified accessor parsetree be transformed to the same plan as json
query based query. That will make explain plans look the same/similar
and it also means that expression indexes can be easily used with both
syntaxes.

> 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).

What does Javascript have to do with this topic?