MIME-Version: 1.0
From: Alexandra Wang <alexandra.wang.oss@gmail.com>
Date: Fri, 16 Jan 2026 13:26:37 -0800
Message-ID: <CAK98qZ1P_edO9hZJVMuUpnXjN-9H=dz2zUj-06G5qW9CMjLqzA@mail.gmail.com>
Subject: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Peter Eisentraut <peter@eisentraut.org>
Content-Type: multipart/alternative; boundary="0000000000005b1bf40648880185"
Archived-At: <https://www.postgresql.org/message-id/CAK98qZ1P_edO9hZJVMuUpnXjN-9H%3Ddz2zUj-06G5qW9CMjLqzA%40mail.gmail.com>
Precedence: bulk

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

Hi hackers,

While working on a patch [1] to add SQL/JSON simplified accessor
support (dot notation, wildcards, etc.) to jsonb, I ran into a
semantic mismatch between PostgreSQL=E2=80=99s existing jsonb subscripting =
[2]
syntax (value[...]), which predates the SQL/JSON standard, and the
SQL:2023 specification of the JSON array accessor (also value[...]) in
the SQL/JSON path language.

Because both use bracket notation for array access, this creates
several edge cases where the existing behavior in PostgreSQL differs
from what the standard specifies, and from what json_query() already
implemented today.

I=E2=80=99d like to summarize the differences, discuss whether we should
reconcile them, and get guidance on how we want to proceed.

---

## Context

PostgreSQL currently allows jsonb subscripts that are coercible to
int4 or text:

-- object member access
SELECT ('{"a": 1}'::jsonb)['a']; -- returns 1 (a jsonb number)

-- array element access
SELECT ('[1, "2", null]'::jsonb)[1]; -- returns "2" (a jsonb string)

In contrast, SQL:2023 defines array access as part of the SQL/JSON
path language:

<JSON array accessor> ::=3D
  <left bracket> <JSON subscript list> <right bracket>
<JSON subscript list> ::=3D
  <JSON subscript> [ { <comma> <JSON subscript> }... ]

where each subscript expression must evaluate to a singleton numeric
value (except for the special *last* keyword). Object member access is
performed using dot notation, not ['key'].

The standard further specifies that a simplified accessor expression
is semantically equivalent to:

    JSON_QUERY (VEP, 'lax $JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY
NULL ON ERROR)

    where:
      VEP =3D <value expression primary>
      JC =3D <JSON simplified accessor op chain>

For example:

select ('[1, "2", null]'::jsonb)[1];

by the standard's specification is semantically equivalent to:

select json_query('[1, "2", null]'::jsonb, 'lax $[1]' with conditional
array wrapper);

However, as Peter Eisentraut pointed out in [3], these two forms
currently diverge in several cases.

---

## Observed differences

Below are the main cases where jsonb subscripting and SQL/JSON path
semantics differ.

### Case 1a: Array access on a jsonb primitive value
select ('123'::jsonb)[0];
  jsonb
-------

(1 row)

select json_query('123'::jsonb, 'lax $[0]' with conditional array wrapper);
  json_query
------------
  123
(1 row)

The json_query() behavior is correct and comply with the SQL standard:

> In lax mode:
> If an operation requires an SQL/JSON array but the operand is not an
> SQL/JSON array, then the operand is first "wrapped" in an SQL/JSON
> array prior to performing the operation.

So, whenever there is a 'lax $[0]' jsonpath expression applying on a
non-json array, the return value should always be the same as the
original json value expression, in this case, the same number 123.

The jsonb subscripting result is not only different, but also
contradicts its current documentation [2], which suggests raising
error:
> However, if any val itself or one of the intermediary values is
> defined as a non-object such as a string, number, or jsonb null,
> traversal cannot proceed so an error is raised and the transaction
> aborted.

---

### Case 1b: Array access on a jsonb object
select ('{"a": 1}'::jsonb)[0];
  jsonb
-------

(1 row)

select json_query('{"a": 1}'::jsonb, 'lax $[0]' with conditional array
wrapper);
  json_query
------------
  {"a": 1}
(1 row)

Same issue as Case 1a, but with an jsonb object instead of a scalar.
json_query() returns the same input json object as expected, whereas
the jsonb subscripting returns null. The jsonb subscripting behavior
here is undocumented.

### Case 1c: Chained array access on a one-dimensional json array

select ('[1, "2", null]'::jsonb)[1][0];
  jsonb
-------

(1 row)

select json_query('[1, "2", null]'::jsonb, 'lax $[1][0]' with
conditional array wrapper);
  json_query
------------
  "2"
(1 row)

This reduces to Case 1a: the intermediate result of [1] is a scalar
("2"), which SQL/JSON treats as a single-element array in 'lax' mode.

---

### Case 2: Non-integer array index

select ('[1, "2", null]'::jsonb)[1.0];
ERROR:  subscript type numeric is not supported
LINE 1: select ('[1, "2", null]'::jsonb)[1.0];
                                         ^
HINT:  jsonb subscript must be coercible to either integer or text.

select json_query('[1, "2", null]'::jsonb, 'lax $[1.0]' with conditional
array wrapper);
  json_query
------------
  "2"
(1 row)

select json_query('[1, "2", null]'::jsonb, 'lax $[1.9]' with conditional
array wrapper);
  json_query
------------
  "2"
(1 row)

The standard allows non-integer numeric subscripts, with
implementation-defined rounding or truncation (IA073). PostgreSQL=E2=80=99s
json_query() uses truncation, which is compliant.

jsonb subscripting does not support this at all, which I think is
reasonable, but again diverges from the standard behavior.

For reference, since DuckDB and Oracle both support JSON simplified
accessor, I tried similar queries on them as well:

-- DuckDB
duckdb> select ('[1, "2", null]'::json)[0.4]; -- returns 1
duckdb> select ('[1, "2", null]'::json)[0.5]; -- returns 2

-- Oracle
WITH data AS (
  SELECT JSON('[1, "2", null]') AS jcol
)
SELECT d.jcol[1.5] FROM data d; -- error

So DuckDB does rounding, and Orcale errors due to "an invalid array
index".

---

### Case 3: Negative array index
select ('[1, "2", null]'::jsonb)[-2];
  jsonb
-------
  "2"
(1 row)

select json_query('[1, "2", null]'::jsonb, 'lax $[-2]' with conditional
array wrapper);
  json_query
------------

(1 row)

select json_query('[1, "2", null]'::jsonb, 'lax $[-0.9]' with conditional
      array wrapper);
 json_query
------------
 1
(1 row)

The json_query() behavior is correct according to the standard for out
of range index in 'lax' mode; and the jsonb subscripting is a custom
extension (-1 means last element, etc.).

Interestingly, DuckDB's JSON array accessor made the same custom
choice as PostgreSQL for negative index.

---

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

Best,
Alex

[1] https://commitfest.postgresql.org/patch/5214/
[2]
https://www.postgresql.org/docs/current/datatype-json.html#JSONB-SUBSCRIPTI=
NG
[3]
https://www.postgresql.org/message-id/a8843d0a-8adb-4fdc-9ac8-8efd22f7d29c%=
40eisentraut.org
[4]
https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJS=
ON-STRICT-AND-LAX-MODES

--=20
Alexandra Wang
EDB: https://www.enterprisedb.com

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

<div dir=3D"ltr"><div>Hi hackers,<br><br>While working on a patch [1] to ad=
d SQL/JSON simplified accessor<br>support (dot notation, wildcards, etc.) t=
o jsonb, I ran into a<br>semantic mismatch between PostgreSQL=E2=80=99s exi=
sting jsonb subscripting [2]<br>syntax (value[...]), which predates the SQL=
/JSON standard, and the<br>SQL:2023 specification of the JSON array accesso=
r (also value[...]) in<br>the SQL/JSON path language.<br><br>Because both u=
se bracket notation for array access, this creates<br>several edge cases wh=
ere the existing behavior in PostgreSQL differs<br>from what the standard s=
pecifies, and from what json_query() already<br>implemented today.<br><br>I=
=E2=80=99d like to summarize the differences, discuss whether we should<br>=
reconcile them, and get guidance on how we want to proceed.<br><br>---<br><=
br>## Context<br><br>PostgreSQL currently allows jsonb subscripts that are =
coercible to<br>int4 or text:<br><br>-- object member access<br>SELECT (=
9;{&quot;a&quot;: 1}&#39;::jsonb)[&#39;a&#39;]; -- returns 1 (a jsonb numbe=
r)<br><br>-- array element access<br>SELECT (&#39;[1, &quot;2&quot;, null]&=
#39;::jsonb)[1]; -- returns &quot;2&quot; (a jsonb string)<br><br>In contra=
st, SQL:2023 defines array access as part of the SQL/JSON<br>path language:=
<br><br>&lt;JSON array accessor&gt; ::=3D<br>=C2=A0 &lt;left bracket&gt; &l=
t;JSON subscript list&gt; &lt;right bracket&gt;<br>&lt;JSON subscript list&=
gt; ::=3D<br>=C2=A0 &lt;JSON subscript&gt; [ { &lt;comma&gt; &lt;JSON subsc=
ript&gt; }... ]<br><br>where each subscript expression must evaluate to a s=
ingleton numeric<br>value (except for the special *last* keyword). Object m=
ember access is<br>performed using dot notation, not [&#39;key&#39;].<br><b=
r>The standard further specifies that a simplified accessor expression<br>i=
s semantically equivalent to:<br><br>=C2=A0 =C2=A0 JSON_QUERY (VEP, &#39;la=
x $JC&#39; WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR)<br><=
br>=C2=A0 =C2=A0 where:<br>=C2=A0 =C2=A0 =C2=A0 VEP =3D &lt;value expressio=
n primary&gt;<br>=C2=A0 =C2=A0 =C2=A0 JC =3D &lt;JSON simplified accessor o=
p chain&gt;<br><br>For example:<br><br>select (&#39;[1, &quot;2&quot;, null=
]&#39;::jsonb)[1];<br>=C2=A0 =C2=A0 =C2=A0 <br>by the standard&#39;s specif=
ication is semantically equivalent to:<br><br>select json_query(&#39;[1, &q=
uot;2&quot;, null]&#39;::jsonb, &#39;lax $[1]&#39; with conditional array w=
rapper);<br><br>However, as Peter Eisentraut pointed out in [3], these two =
forms<br>currently diverge in several cases.<br><br>---<br><br>## Observed =
differences<br>=C2=A0 =C2=A0 =C2=A0 <br>Below are the main cases where json=
b subscripting and SQL/JSON path<br>semantics differ.<br>=C2=A0 =C2=A0 =C2=
=A0 <br>### Case 1a: Array access on a jsonb primitive value<br>select (=
9;123&#39;::jsonb)[0];<br>=C2=A0 jsonb<br>-------<br><br>(1 row)<br><br>sel=
ect json_query(&#39;123&#39;::jsonb, &#39;lax $[0]&#39; with conditional ar=
ray wrapper);<br>=C2=A0 json_query<br>------------<br>=C2=A0 123<br>(1 row)=
<br>=C2=A0 =C2=A0 =C2=A0 <br>The json_query() behavior is correct and compl=
y with the SQL standard: <br>=C2=A0 =C2=A0 =C2=A0 <br>&gt; In lax mode:<br>=
&gt; If an operation requires an SQL/JSON array but the operand is not an<b=
r>&gt; SQL/JSON array, then the operand is first &quot;wrapped&quot; in an =
SQL/JSON<br>&gt; array prior to performing the operation.<br><br>So, whenev=
er there is a &#39;lax $[0]&#39; jsonpath expression applying on a<br>non-j=
son array, the return value should always be the same as the<br>original js=
on value expression, in this case, the same number 123.<br><br>The jsonb su=
bscripting result is not only different, but also<br>contradicts its curren=
t documentation [2], which suggests raising<br>error:<br>&gt; However, if a=
ny val itself or one of the intermediary values is<br>&gt; defined as a non=
-object such as a string, number, or jsonb null,<br>&gt; traversal cannot p=
roceed so an error is raised and the transaction<br>&gt; aborted.<br><br>--=
-<br><br>### Case 1b: Array access on a jsonb object<br>select (&#39;{&quot=
;a&quot;: 1}&#39;::jsonb)[0];<br>=C2=A0 jsonb<br>-------<br><br>(1 row)<br>=
<br>select json_query(&#39;{&quot;a&quot;: 1}&#39;::jsonb, &#39;lax $[0]=
9; with conditional array<br>wrapper);<br>=C2=A0 json_query<br>------------=
<br>=C2=A0 {&quot;a&quot;: 1}<br>(1 row)<br><br>Same issue as Case 1a, but =
with an jsonb object instead of a scalar.<br>json_query() returns the same =
input json object as expected, whereas<br>the jsonb subscripting returns nu=
ll. The jsonb subscripting behavior<br>here is undocumented.<br><br>### Cas=
e 1c: Chained array access on a one-dimensional json array<br><br>select (&=
#39;[1, &quot;2&quot;, null]&#39;::jsonb)[1][0];<br>=C2=A0 jsonb<br>-------=
<br><br>(1 row)<br><br>select json_query(&#39;[1, &quot;2&quot;, null]&#39;=
::jsonb, &#39;lax $[1][0]&#39; with<br>conditional array wrapper);<br>=C2=
=A0 json_query<br>------------<br>=C2=A0 &quot;2&quot;<br>(1 row)<br><br>Th=
is reduces to Case 1a: the intermediate result of [1] is a scalar<br>(&quot=
;2&quot;), which SQL/JSON treats as a single-element array in &#39;lax&#39;=
 mode.<br><br>---<br><br>### Case 2: Non-integer array index<br><br>select =
(&#39;[1, &quot;2&quot;, null]&#39;::jsonb)[1.0];<br>ERROR: =C2=A0subscript=
 type numeric is not supported<br>LINE 1: select (&#39;[1, &quot;2&quot;, n=
ull]&#39;::jsonb)[1.0];<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0^<br>HINT: =C2=A0jsonb subscript must be coercible =
to either integer or text.<br><br>select json_query(&#39;[1, &quot;2&quot;,=
 null]&#39;::jsonb, &#39;lax $[1.0]&#39; with conditional<br>array wrapper)=
;<br>=C2=A0 json_query<br>------------<br>=C2=A0 &quot;2&quot;<br>(1 row)<b=
r><br>select json_query(&#39;[1, &quot;2&quot;, null]&#39;::jsonb, &#39;lax=
 $[1.9]&#39; with conditional<br>array wrapper);<br>=C2=A0 json_query<br>--=
----------<br>=C2=A0 &quot;2&quot;<br>(1 row)<br><br>The standard allows no=
n-integer numeric subscripts, with<br>implementation-defined rounding or tr=
uncation (IA073). PostgreSQL=E2=80=99s<br>json_query() uses truncation, whi=
ch is compliant.<br>=C2=A0 =C2=A0 =C2=A0 <br>jsonb subscripting does not su=
pport this at all, which I think is<br>reasonable, but again diverges from =
the standard behavior.<br>=C2=A0 =C2=A0 =C2=A0 <br>For reference, since Duc=
kDB and Oracle both support JSON simplified<br>accessor, I tried similar qu=
eries on them as well:<br>=C2=A0 =C2=A0 =C2=A0 <br>-- DuckDB<br>duckdb&gt; =
select (&#39;[1, &quot;2&quot;, null]&#39;::json)[0.4]; -- returns 1<br>duc=
kdb&gt; select (&#39;[1, &quot;2&quot;, null]&#39;::json)[0.5]; -- returns =
2<br><br>-- Oracle<br>WITH data AS (<br>=C2=A0 SELECT JSON(&#39;[1, &quot;2=
&quot;, null]&#39;) AS jcol<br>)<br>SELECT d.jcol[1.5] FROM data d; -- erro=
r<br>=C2=A0 =C2=A0 =C2=A0 <br>So DuckDB does rounding, and Orcale errors du=
e to &quot;an invalid array<br>index&quot;. <br><br>---<br><br>### Case 3: =
Negative array index<br>select (&#39;[1, &quot;2&quot;, null]&#39;::jsonb)[=
-2];<br>=C2=A0 jsonb<br>-------<br>=C2=A0 &quot;2&quot;<br>(1 row)<br><br>s=
elect json_query(&#39;[1, &quot;2&quot;, null]&#39;::jsonb, &#39;lax $[-2]&=
#39; with conditional<br>array wrapper);<br>=C2=A0 json_query<br>----------=
--<br><br>(1 row)<br>=C2=A0 =C2=A0 =C2=A0 <br>select json_query(&#39;[1, &q=
uot;2&quot;, null]&#39;::jsonb, &#39;lax $[-0.9]&#39; with conditional =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 array wrapper);<br>=C2=A0json_query<br>-----------=
-<br>=C2=A01<br>(1 row)<br><br>The json_query() behavior is correct accordi=
ng to the standard for out<br>of range index in &#39;lax&#39; mode; and the=
 jsonb subscripting is a custom<br>extension (-1 means last element, etc.).=
<br><br>Interestingly, DuckDB&#39;s JSON array accessor made the same custo=
m<br>choice as PostgreSQL for negative index.<br><br>---<br><br>## Summary<=
br>=C2=A0 =C2=A0 =C2=A0 <br>In all cases above, json_query() follows the SQ=
L/JSON standard<br>specification. jsonb subscripting, which predates the st=
andard,<br>differs in several ways:<br><br>1. Array access on non-arrays (s=
calars or objects) does not use &#39;lax&#39;<br>mode wrapping. As a result=
, &quot;[0]&quot; does not return the original value.<br>2. Non-integer sub=
scripts are not supported. <br>3. Negative subscripts use a PostgreSQL-spec=
ific extension.<br>=C2=A0 =C2=A0 =C2=A0 <br>These are all edge cases and li=
kely low-impact in practice. However,<br>if we were to add broader support =
for SQL/JSON simplified accessors<br>(dot notation, wildcards, item methods=
, etc.), which can be chained<br>together and include array access using th=
e existing bracket syntax,<br>it seems useful to clarify what semantics we =
would want for array<br>access in those situations.<br><br>For example, wit=
h expressions such as<br><br>=C2=A0 =C2=A0 select (jb)[0].a<br>=C2=A0 =C2=
=A0 select (jb).a[0].b<br>=C2=A0 =C2=A0 select (jb).a[-1].b<br><br>(where j=
b is a jsonb value), it would be potentially confusing if<br>their behavior=
 differed from the equivalent json_query() calls using<br>the same JSON pat=
h.<br><br>Given this, it is unclear to me whether the expectation should be=
 to<br>move closer to the SQL/JSON path semantics, or to preserve the<br>ex=
isting jsonb subscripting behavior and document it as<br>PostgreSQL-specifi=
c.<br><br>This question comes up in the context of the dot-notation work I<=
br>mentioned at the beginning, but more generally it seems like something<b=
r>we may want to be explicit about before extending the syntax further.<br>=
<br>I would very much appreciate any thoughts or guidance on this.<br>=C2=
=A0 =C2=A0 =C2=A0 <br>Best,<br>Alex<br>=C2=A0 =C2=A0 =C2=A0 <br>[1] <a href=
=3D"https://commitfest.postgresql.org/patch/5214/">https://commitfest.postg=
resql.org/patch/5214/</a><br>[2] <a href=3D"https://www.postgresql.org/docs=
/current/datatype-json.html#JSONB-SUBSCRIPTING">https://www.postgresql.org/=
docs/current/datatype-json.html#JSONB-SUBSCRIPTING</a><br>[3] <a href=3D"ht=
tps://www.postgresql.org/message-id/a8843d0a-8adb-4fdc-9ac8-8efd22f7d29c%40=
eisentraut.org">https://www.postgresql.org/message-id/a8843d0a-8adb-4fdc-9a=
c8-8efd22f7d29c%40eisentraut.org</a><br>[4]<a href=3D"https://www.postgresq=
l.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-STRICT-AND-LAX-MOD=
ES">https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-S=
QLJSON-STRICT-AND-LAX-MODES</a></div><div><br></div><span class=3D"gmail_si=
gnature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature" da=
ta-smartmail=3D"gmail_signature"><div dir=3D"ltr"><font face=3D"monospace">=
Alexandra Wang</font><div><font face=3D"monospace">EDB: <a href=3D"https://=
www.enterprisedb.com" target=3D"_blank">https://www.enterprisedb.com</a></f=
ont></div></div></div></div>

--0000000000005b1bf40648880185--