public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andrew Dunstan <[email protected]>
To: Alexandra Wang <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Subject: Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023)
Date: Mon, 2 Feb 2026 10:32:52 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAK98qZ1P_edO9hZJVMuUpnXjN-9H=dz2zUj-06G5qW9CMjLqzA@mail.gmail.com>
References: <CAK98qZ1P_edO9hZJVMuUpnXjN-9H=dz2zUj-06G5qW9CMjLqzA@mail.gmail.com>


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

view thread (4+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023)
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox