Message-ID: <2cc680c1-12a9-4152-ad31-a1385a9d6912@dunslane.net>
Date: Mon, 2 Feb 2026 10:32:52 -0500
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: jsonb subscripting vs SQL/JSON array accessor semantics
 (SQL:2023)
To: Alexandra Wang <alexandra.wang.oss@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Peter Eisentraut <peter@eisentraut.org>
References: <CAK98qZ1P_edO9hZJVMuUpnXjN-9H=dz2zUj-06G5qW9CMjLqzA@mail.gmail.com>
From: Andrew Dunstan <andrew@dunslane.net>
Content-Language: en-US
Autocrypt: addr=andrew@dunslane.net; keydata=
 xsBNBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAHNJEFuZHJldyBEdW5zdGFuIDxhbmRyZXdAZHVuc2xhbmUubmV0PsLAlwQTAQgAQQIbAwIX
 gAIZAQULCQgHAwUVCgkICwUWAgMBAAIeBRYhBOQ+WEYd/Hy/RGkVpZn6f8tZ/DuBBQJoGNGd
 BQkdEO8nAAoJEJn6f8tZ/DuBq74H/jkTR4Zi3stbw+xC7v2u3QozssK7MYPL2AsVfh7OealS
 h182fiWXpfvmmAB7WUHbhk9GC2RAOnHI/2d2jgKaMLAHsGYOT0YopTVIwRY43fCw/mK67yxc
 wmDcX+zyKfLaivNbf5A7QPLNwda98bEAMSJ8Sn652Uc6cA8t3uKGsVzbRBQOoYzjgvBCfSrE
 9ql3PDNg0l4BfAqabd2f70ZUm9VAMEPrgv/v2xI7M2XiL4g5BVmqLCOwxLM8RMCotCuoweUr
 VO43DeBCIDwLxotMJKvGWDjBzQYlU1NPUAtNcz/gN9ITUe1VUGjyvGj4u1lxBOcQQUw7l1+T
 5moZ4iZxXzvOwE0ETspYWQEIANGc4zQULOxhbqO2dyD51YhqCNRmm9oKWaqf+wmW4tpDe/VV
 cxAnNizd4LWCHfzpb5cHAtGkOPePMfzWVf6nvdF7d3eglbtf59+zG7O7llV0xSSoFiieQBsr
 GvqDInXYX/4mRRXMtyhM353/tixC9RWLs1oofyYmCPPXXY7h9R7en3B8BoVrRFcdzlIY/NFN
 hFGW/9dkEiGjgna2Rk6e15kln4ZvFBWUg23p93w/pqXcxY6+k/8TEk+C4R+M6w7o2PLGOjdZ
 +kPiUcw5H85zf/yZJwQXzisXaNduwWB6Vads9YC9dj6kPR1c4VGRqAaYL++LAEOqrlvm2Tvq
 QqZRtnEAEQEAAcLAfAQYAQgAJgIbDBYhBOQ+WEYd/Hy/RGkVpZn6f8tZ/DuBBQJoGNI2BQkd
 EODdAAoJEJn6f8tZ/DuBfw0IAKTsfD40teP/pp+bsLLMSxPXUYrrprTj7WFB5v61p6dkpSr/
 qXmMlyahdxQFaPmfVgVirB1Vk/kHiWNnnGjfUV9nB2Zg9LI0Xb9/ts3LsUiRWXzG3tkMY6XL
 vsVOxW4XFRND9l2q+WW93aZ1DZl+fqWfYgMvsusFRhmGFOKTRfKPta2Pkv+AhA24N4+PrR5p
 bU4k2MO8PAGiK8eaYKGFG1bHKuAvoDoF7WXJ3FHxuWqLnKEt4dfOLm5pAe3zq1Lt6q8azT9i
 QWGpSAK5vQUWQHBHpiDjdPeqKZ6HiAXIIKfSmb+jrvXBqoP+D6/K7rUjG2aXiRtTIAXms9sm
 VRu7cmw=
In-Reply-To: <CAK98qZ1P_edO9hZJVMuUpnXjN-9H=dz2zUj-06G5qW9CMjLqzA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/2cc680c1-12a9-4152-ad31-a1385a9d6912%40dunslane.net>
Precedence: bulk


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