MIME-Version: 1.0
References: <CAK98qZ1P_edO9hZJVMuUpnXjN-9H=dz2zUj-06G5qW9CMjLqzA@mail.gmail.com>
 <CAGECzQRWAJeqT8JWDOBp2wxFXrmyi9YNFgAhrPOMM6_feyNX9g@mail.gmail.com>
In-Reply-To: <CAGECzQRWAJeqT8JWDOBp2wxFXrmyi9YNFgAhrPOMM6_feyNX9g@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Sat, 17 Jan 2026 11:22:04 +0100
Message-ID: <CAFj8pRD+Oi6tBqGOp-3oVj9TKu5=i9ms+-_m=8qV99x+a=kFvQ@mail.gmail.com>
Subject: Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023)
To: Jelte Fennema-Nio <postgres@jeltef.nl>
Cc: Alexandra Wang <alexandra.wang.oss@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, 
	Peter Eisentraut <peter@eisentraut.org>
Content-Type: multipart/alternative; boundary="00000000000075dbc3064892d68d"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRD%2BOi6tBqGOp-3oVj9TKu5%3Di9ms%2B-_m%3D8qV99x%2Ba%3DkFvQ%40mail.gmail.com>
Precedence: bulk

--00000000000075dbc3064892d68d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

so 17. 1. 2026 v 10:54 odes=C3=ADlatel Jelte Fennema-Nio <postgres@jeltef.n=
l>
napsal:

> On Fri, 16 Jan 2026 at 22:27, Alexandra Wang
> <alexandra.wang.oss@gmail.com> wrote:
> > In all cases above, json_query() follows the SQL/JSON standard
> > specification. jsonb subscripting, which predates the standard,
> > differs in several ways:
>
> My thoughts on changing these 3 behaviours:
>
> > 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.
>
> I think it's unlikely anyone cares about the exact behaviour here in
> practice. But changing the behaviour could corrupt expression indexes
> that use this syntax.
>
> > 2. Non-integer subscripts are not supported.
>
> Changing this to not throw an error seems fine to me. Making something
> that throw an error, now not throw an error should not cause breakage
> for people. The new behaviour would be of arguable usefulness though.
>
> > 3. Negative subscripts use a PostgreSQL-specific extension.
>
> I think there are probably people relying on it. And postgres
> behaviour actually seems way more useful than the SQL Standard
> behaviour.
>
> You said DuckDB does the same as Postgres. That doesn't surprise me
> much, since DuckDB usually defaults to Postgres behaviour. They don't
> care much about being strictly sql standard compliant, if that means
> more sensible/useful SQL for their users. And since many of their
> users are used to Postgres, they try to stay PostgreSQL compatible in
> their SQL (unless they think the postgres behaviour is really
> weird/confusing).
>
> I do wonder what other databases do though. Does Oracle, MySQL or
> MSSQL actually follow the standard here? i.e how incompatible is this
> behaviour in practice with other databases?
>
> > I would very much appreciate any thoughts or guidance on this.
>
> If change 3 would not have been there, I would have probably been okay
> with changing Postgres to behave like the SQL standard and telling
> people to re-index their indexes that use this syntax in that major
> release. But I think we should keep our current behaviour for option
> 3.
>
> An approach that I think would be viable to do that is:
> 1. Define a new sqlpath mode (e.g. with the name lax_postgres or
> something). And define that as our current behaviour (possibly with 2
> changed to behave like lax).
> 2. Document that our SQL/JSON simplified accessors diverge slightly
> from the SQL standard because they use lax_postgres instead of lax.
>
> That would mean there's still an easy 1-to-1 translation between the
> simplified accessor string and and JSON_QUERY (all that would be
> different is the change from lax to lax_postgres in the string)
>

I am not sure if the implementation of the third method is the best we can
do.

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.

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.

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

Regards

Pavel

--00000000000075dbc3064892d68d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">so 17. 1. 2026 =
v=C2=A010:54 odes=C3=ADlatel Jelte Fennema-Nio &lt;<a href=3D"mailto:postgr=
es@jeltef.nl">postgres@jeltef.nl</a>&gt; napsal:<br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">On Fri, 16 Jan 2026 at 22:27, Alexandra Wa=
ng<br>
&lt;<a href=3D"mailto:alexandra.wang.oss@gmail.com" target=3D"_blank">alexa=
ndra.wang.oss@gmail.com</a>&gt; wrote:<br>
&gt; In all cases above, json_query() follows the SQL/JSON standard<br>
&gt; specification. jsonb subscripting, which predates the standard,<br>
&gt; differs in several ways:<br>
<br>
My thoughts on changing these 3 behaviours:<br>
<br>
&gt; 1. Array access on non-arrays (scalars or objects) does not use &#39;l=
ax&#39;<br>
&gt; mode wrapping. As a result, &quot;[0]&quot; does not return the origin=
al value.<br>
<br>
I think it&#39;s unlikely anyone cares about the exact behaviour here in<br=
>
practice. But changing the behaviour could corrupt expression indexes<br>
that use this syntax.<br>
<br>
&gt; 2. Non-integer subscripts are not supported.<br>
<br>
Changing this to not throw an error seems fine to me. Making something<br>
that throw an error, now not throw an error should not cause breakage<br>
for people. The new behaviour would be of arguable usefulness though.<br>
<br>
&gt; 3. Negative subscripts use a PostgreSQL-specific extension.<br>
<br>
I think there are probably people relying on it. And postgres<br>
behaviour actually seems way more useful than the SQL Standard<br>
behaviour.<br>
<br>
You said DuckDB does the same as Postgres. That doesn&#39;t surprise me<br>
much, since DuckDB usually defaults to Postgres behaviour. They don&#39;t<b=
r>
care much about being strictly sql standard compliant, if that means<br>
more sensible/useful SQL for their users. And since many of their<br>
users are used to Postgres, they try to stay PostgreSQL compatible in<br>
their SQL (unless they think the postgres behaviour is really<br>
weird/confusing).<br>
<br>
I do wonder what other databases do though. Does Oracle, MySQL or<br>
MSSQL actually follow the standard here? i.e how incompatible is this<br>
behaviour in practice with other databases?<br>
<br>
&gt; I would very much appreciate any thoughts or guidance on this.<br>
<br>
If change 3 would not have been there, I would have probably been okay<br>
with changing Postgres to behave like the SQL standard and telling<br>
people to re-index their indexes that use this syntax in that major<br>
release. But I think we should keep our current behaviour for option<br>
3.<br>
<br>
An approach that I think would be viable to do that is:<br>
1. Define a new sqlpath mode (e.g. with the name lax_postgres or<br>
something). And define that as our current behaviour (possibly with 2<br>
changed to behave like lax).<br>
2. Document that our SQL/JSON simplified accessors diverge slightly<br>
from the SQL standard because they use lax_postgres instead of lax.<br>
<br>
That would mean there&#39;s still an easy 1-to-1 translation between the<br=
>
simplified accessor string and and JSON_QUERY (all that would be<br>
different is the change from lax to lax_postgres in the string)<br></blockq=
uote><div><br></div><div>I am not sure if the implementation of the third m=
ethod is the best we can do.</div><div><br></div><div>Described handling of=
 corner cases in SQL/JSON has some logic and consistency, but it is not com=
patible with the generic philosophy=C2=A0of PostgreSQL arrays. If I know AN=
SI/SQL doesn&#39;t know arrays, so this inconsistency is just a PostgreSQL =
problem, and because we don&#39;t like feature flags, I don&#39;t see any s=
olution to how this situation can be solved.=C2=A0</div><div><br></div><div=
>Any solution will be ugly. In=C2=A0 this situation I prefer current behavi=
or - (inconsistency between array access and JSON_QUERY) with good descript=
ion in documentation.=C2=A0</div><div><br></div><div>Theoretically it can b=
e introduced=C2=A0lax_postgres like you propose. But I don&#39;t see how it=
 can help with possible compatibility issues when somebody will migrate fro=
m other databases.</div><div><br></div><div>So anything inside JSON_XXXX fu=
nctions can be rigidly consistent with standard SQL/JSON. Outside should no=
t be true - and it is better to say it explicitly. I don&#39;t think introd=
ucing some JavaScripts concepts to Postgres (although just for some corner =
cases) is a good idea (when we have some specific handling of some corner c=
ases too).</div><div><br></div><div>Regards</div><div><br></div><div>Pavel<=
/div><div><br></div><div>=C2=A0</div></div></div>

--00000000000075dbc3064892d68d--