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

On 2026-01-16 Fr 4:26 PM, Alexandra Wang wrote:

[ ... ]

> ## Summary
>
> In all cases above, json_query() follows the SQL/JSON standard
> specification. jsonb subscripting, which predates the standard,
> differs in several ways:
>
> 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.
> 2. Non-integer subscripts are not supported.
> 3. Negative subscripts use a PostgreSQL-specific extension.
>
> These are all edge cases and likely low-impact in practice. However,
> if we were to add broader support for SQL/JSON simplified accessors
> (dot notation, wildcards, item methods, etc.), which can be chained
> together and include array access using the existing bracket syntax,
> it seems useful to clarify what semantics we would want for array
> access in those situations.
>
> For example, with expressions such as
>
>     select (jb)[0].a
>     select (jb).a[0].b
>     select (jb).a[-1].b
>
> (where jb is a jsonb value), it would be potentially confusing if
> their behavior differed from the equivalent json_query() calls using
> the same JSON path.
>
> Given this, it is unclear to me whether the expectation should be to
> move closer to the SQL/JSON path semantics, or to preserve the
> existing jsonb subscripting behavior and document it as
> PostgreSQL-specific.
>
> This question comes up in the context of the dot-notation work I
> mentioned at the beginning, but more generally it seems like something
> we may want to be explicit about before extending the syntax further.
>
> I would very much appreciate any thoughts or guidance on this.
>
>

I'd be inclined to move to the standard for 1 and 2, and document that
people might need to reindex after an upgrade if they have expression
indexes, but keep supporting negative subscripts. Not sure how feasible
that is exactly.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com