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

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

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

PostgreSQL arrays can be 0 based too. I don't see a problem with this -
Postgres is more general than SQL/JSON and then there is not a problem

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

I see some JavaScript philosophy (or HTML) in design of SQL/JSON -

'{[1,2,3]}'[0,1] -> 1 (SQL/JSON) versus -> NULL (Postgres)

but I can be wrong

Regards

Pavel