Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1us9qo-000lZd-3U for pgsql-hackers@arkaria.postgresql.org; Sat, 30 Aug 2025 00:53:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1us9ql-00DUUh-HV for pgsql-hackers@arkaria.postgresql.org; Sat, 30 Aug 2025 00:53:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1us9qk-00DUTm-Tl for pgsql-hackers@lists.postgresql.org; Sat, 30 Aug 2025 00:53:47 +0000 Received: from mail-pj1-x1044.google.com ([2607:f8b0:4864:20::1044]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1us9qh-002S3F-2d for pgsql-hackers@postgresql.org; Sat, 30 Aug 2025 00:53:46 +0000 Received: by mail-pj1-x1044.google.com with SMTP id 98e67ed59e1d1-327f95f750eso867779a91.2 for ; Fri, 29 Aug 2025 17:53:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1756515223; x=1757120023; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=wOzvEQLtjdhhIDQpxRVLcUrwSKRoTRFiPlNhvY3ajCA=; b=mVkJGT6BwDUtbZZQSsNqETK79sCV247uRpqtaO3DvGzqCR+42xzyFNdThnsHZcRcQO xv+/uK3ojFrfpI7RPC/RedgKlztvYx1OUNlkboRg8karnhmiS+EckRtZ5T7kS18wEtxu AdoTG+2JCRZF8FYdj8+2DdJMoYfn2NRg5yxX09c0tCJfW74ZhkXJRPgQJvY9qy3l/Niu h31/H3i4xFU4NfX4Tw+24/Yhjn4BUXGfY/wDtEywrwXSdjVKxGTTbA0t7M0zrpDYvD/E UM1IY/VsSBVsg5uDLrkmWL/gkeMRRTz1o8rNuFGgZqYhsZ+BjNxJZpmlnbGeVrftRk7b SfBg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1756515223; x=1757120023; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=wOzvEQLtjdhhIDQpxRVLcUrwSKRoTRFiPlNhvY3ajCA=; b=bw6NA5IQpC5RTlnypp38JUs7qaf6H5Ri0ewEpyFP4jEWAINUAfw4OrCGQs1ccEQlRZ 6Fk3HsnWceCsOXGAFe+gAt+UvIZs4dtTHFOo7tKW9rInjGadakJ7FawPcfYcsYcgMgwE Z2ydrFe0gz99SMQldOYeHyR4zSN/+63PS27kRqjOP7d7C8TeWMa4mu+FwuNlqSeutmPI //CniRyh+JvpXq/m9kQh97ZhCcVDUEmvrGklthCIAIpBxMvCrUfhfu4yam8btyZG7hi6 975Ha5rD3CjUSvx60Knvh8ghjrOA3U871Y3onyRx71PVf1uJ04DAeEyRJ2wcoWXAWcXV wLgQ== X-Forwarded-Encrypted: i=1; AJvYcCU6ycZnSxY+HTnVweVzaDuQdQdCbWhCjHk4O5Wn7SY5yf2yYidEohhlz6esMaQXPR4Mw3ZDdJzBr2+W8YhF@postgresql.org X-Gm-Message-State: AOJu0YwKxD8hlwtmlj+uR/36oztxmaWHNakiM7vD9mT+vMl23kKihuHG nnm/QpDbckE7uhcbAKLOLErrYPzfooPKm1UI3hH+88jmBG9VTTXXlNO6fVKmTTFIG0FQlREnQfN WEm3Hfl1c/IyID44shkmlVEMXOlkJUf8= X-Gm-Gg: ASbGncsiMfAPmEYqQfFQldoyxPopAhuIGoS1e/vpltk2fHbWPXkMjVBwz1u4IdIMj/a +DKEm7h4PJ5pEzoNQzrhAFr2YwSwJ/rc06s0fH7DjiG4ZkG1icCntBQ3EDRXM09Jct+LRLOxtFq ZXE5sp9iEFNkbO6id0KAPsS5GqIzTyAfhGOfcIIWwpF9Nj7gVKlgzkWPJjstSvvSlGJPFuHrPuN SpM9DSY X-Google-Smtp-Source: AGHT+IG/k/yaf5Ynb2eyoiEsF4A5K3grxbvu4/qzzBs+EwUC1y9WVjMmxoKFg/XvVQPOBXnsIDJVb+tcinQQVtt6cNc= X-Received: by 2002:a17:90b:4b51:b0:327:8c05:f89e with SMTP id 98e67ed59e1d1-32815412c6bmr753535a91.8.1756515223341; Fri, 29 Aug 2025 17:53:43 -0700 (PDT) MIME-Version: 1.0 References: <21fa36b4-6d56-4556-b524-9e3955b922c8@eisentraut.org> <263CAF0D-7776-4349-93CA-CC1AB15384CA@gmail.com> In-Reply-To: From: Alexandra Wang Date: Fri, 29 Aug 2025 17:53:06 -0700 X-Gm-Features: Ac12FXzwHamU3qITltu95lORKwduLC2yfMsNnhVC9OEwLdT9WoB3iJ3Vgwpcu3g Message-ID: Subject: Re: SQL:2023 JSON simplified accessor support To: Chao Li Cc: jian he , Nikita Malakhov , Vik Fearing , Mark Dilger , Matheus Alcantara , Peter Eisentraut , Andrew Dunstan , Nikita Glukhov , PostgreSQL Hackers , "David E. Wheeler" Content-Type: multipart/alternative; boundary="000000000000ee0ea4063d8a918f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ee0ea4063d8a918f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Chao, Thanks for reviewing! On Thu, Aug 28, 2025 at 8:29=E2=80=AFPM Chao Li wr= ote: > > On Aug 26, 2025, at 11:52, Alexandra Wang > wrote: > > Best, > Alex > > > > > > > > > > > I found a bug. > ``` > INSERT INTO test_jsonb_types (data) VALUES > ('[1, 2, "three"]'), > ('{"con": {"a": [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": 100}}}]}}=E2= =80=99); > ``` > > If I use a index following a slice, it doesn=E2=80=99t work: > > ``` > evantest=3D# select data[0] from test_jsonb_types; > data > ------ > > 1 > > (2 rows) > > evantest=3D# select data[0:2][1] from test_jsonb_types; # This should ret= urn > =E2=80=9C2" > data > ------ > > > (2 rows) > > evantest=3D# select (t.data)['con']['a'][0:1] from test_jsonb_types t; # > returned the slice properly > data > ----------------------------------------------------- > > [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": 100}}}] > (2 rows) > > evantest=3D# select (t.data)['con']['a'][0:1][0] from test_jsonb_types t;= # > also returned the slice, which is wrong > data > ----------------------------------------------------- > > [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": 100}}}] > (2 rows) > ``` > > We should consider a slice as a container, so the fix is simple. My quick > unpolished fix is: > > ``` > chaol@ChaodeMacBook-Air postgresql % git diff > diff --git a/src/backend/utils/adt/jsonbsubs.c > b/src/backend/utils/adt/jsonbsubs.c > index cb72d12ca3f..8845dcf239a 100644 > --- a/src/backend/utils/adt/jsonbsubs.c > +++ b/src/backend/utils/adt/jsonbsubs.c > @@ -247,6 +247,7 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, Lis= t > **indirection, Subscripti > ListCell *lc; > Datum jsp; > int pathlen =3D 0; > + bool isSlice =3D false; > > sbsref->refupperindexpr =3D NIL; > sbsref->reflowerindexpr =3D NIL; > @@ -285,6 +286,7 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, Lis= t > **indirection, Subscripti > > if (ai->is_slice) > { > + isSlice =3D true; > while > (list_length(sbsref->reflowerindexpr) < > list_length(sbsref->refupperindexpr)) > sbsref->reflowerindexpr =3D > lappend(sbsref->reflowerindexpr, NULL); > > @@ -369,6 +371,9 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, Lis= t > **indirection, Subscripti > path->next =3D jpi; > path =3D jpi; > pathlen++; > + > + if (isSlice) > + break; > } > > if (pathlen =3D=3D 0) > ``` > > After the fix, let=E2=80=99s test again: > > ``` > evantest=3D# select data[0:2][1] from test_jsonb_types; # good result > data > ------ > 2 > > (2 rows) > > evantest=3D# select (t.data)['con']['a'][0:1][0] from test_jsonb_types t;= # > good result > data > ------------------------- > > {"b": {"c": {"d": 99}}} > (2 rows) > ``` > TL;DR: It is a feature, not a bug. See longer explanation below: This behavior aligns with the SQL:2023 standard. While the result you expected is more intuitive in my opinion, it is incorrect according to the spec. As I mentioned in the commit message of patch v14-0005: * The SQL standard states that simplified access is equivalent to: JSON_QUERY (VEP, 'lax $.JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) where: VEP =3D JC = =3D * The most relevant detail that I want to highlight is "WITH CONDITIONAL ARRAY WRAPPER". The documentation[1] says: *> If the path expression may return multiple values, it might be> necessary to wrap those values using the WITH WRAPPER clause to make> it a valid JSON string, because the default behavior is to not wrap> them, as if WITHOUT WRAPPER were specified. The WITH WRAPPER clause is> by default taken to mean WITH UNCONDITIONAL WRAPPER, which means that> even a single result value will be wrapped. To apply the wrapper only> when multiple values are present, specify WITH CONDITIONAL WRAPPER.> Getting multiple values in result will be treated as an error if> WITHOUT WRAPPER is specified.* So, for your test queries: select data[0:2] from test_jsonb_types; select data[0:2][1] from test_jsonb_types; select (t.data)['con']['a'][0:1] from test_jsonb_types t; select (t.data)['con']['a'][0:1][0] from test_jsonb_types t; We have these equivalents using json_query(): select json_query(data, 'lax $[0 to 2]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types; select json_query(data, 'lax $[0 to 2][1]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types; select json_query(data, 'lax $.con.a[0 to 1]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types; -- **[NOTE]** select json_query(data, 'lax $.con.a[0 to 1][0]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types; -- **[NOTE]** **[NOTE]**: .a and ['a'] (as well as .con and ['con']) are not syntactically equivalent, as the dot-notation .a is in "lax" mode, whereas the pre-standard subscript ['a'] is in "strict" mode. I will discuss this more in a separate reply to your other comment. However, for the specific data we inserted in your example table, they happen to return the same results. Since our focus here is not dot-notation, we won=E2=80=99t go further into it here. You can verify correctness with: test=3D# select data[0:2] =3D json_query(data, 'lax $[0 to 2]' WITH CONDITI= ONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types; ?column? ---------- t t (2 rows) test=3D# select (data[0:2][1] is NULL) AND (json_query(data, 'lax $[0 to 2][1]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) is NULL) from test_jsonb_types; ?column? ---------- t t (2 rows) test=3D# select (((t.data)['con']['a'][0:1] IS NULL) AND (json_query(data, 'lax $.con.a[0 to 1]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) IS NULL)) OR ((t.data)['con']['a'][0:1] =3D json_query(data, 'lax $.con.a[0 to 1]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR)) from test_jsonb_types t; ?column? ---------- t t (2 rows) test=3D# select (((t.data)['con']['a'][0:1][0] IS NULL) AND (json_query(dat= a, 'lax $.con.a[0 to 1][0]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) IS NULL)) OR ((t.data)['con']['a'][0:1][0] =3D json_query(data, 'lax $.con.a[0 to 1][0]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR)) from test_jsonb_types t; ?column? ---------- t t (2 rows) All tests show "t", confirming the current results are all correct. I think the root of your confusion is the meaning of CONDITIONAL ARRAY WRAPPER. So let=E2=80=99s try more examples: -- keep your previous setup drop table test_jsonb_types; create table test_jsonb_types (data jsonb); INSERT INTO test_jsonb_types (data) VALUES ('[1, 2, "three"]'), ('{"con": {"a": [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": 100}}}]}}'); Let's start with: select data[0:2] from test_jsonb_types; It is equivalent to: select json_query(data, 'lax $[0 to 2]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types; They all have the following output: test=3D# select json_query(data, 'lax $[0 to 2]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types; json_query --------------------------------------------------------------------- [1, 2, "three"] {"con": {"a": [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": 100}}}]}} (2 rows) To find out what WITH CONDITIONAL ARRAY WRAPPER does, let's toggle it to WITHOUT ARRAY WRAPPER: test=3D# select json_query(data, 'lax $[0 to 2]' WITHOUT ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types; json_query --------------------------------------------------------------------- {"con": {"a": [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": 100}}}]}} (2 rows) The first row return NULL, because we've specified NULL ON ERROR, so let's toggle that as well to ERROR ON ERROR: test=3D# select json_query(data, 'lax $[0 to 2]' WITHOUT ARRAY WRAPPER NULL ON EMPTY ERROR ON ERROR) from test_jsonb_types; ERROR: 22034: JSON path expression in JSON_QUERY must return single item when no wrapper is requested HINT: Use the WITH WRAPPER clause to wrap SQL/JSON items into an array. LOCATION: JsonPathQuery, jsonpath_exec.c:3987 As shown, without ARRAY WRAPPER, the query produces a sequence of JSON value items, not a JSON array. Note that array wrapping is only applied to the final result of a jsonpath, not to each intermediate result in the chain. See the following example: -- new setup truncate test_jsonb_types; INSERT INTO test_jsonb_types (data) VALUES ('[1, 2, "three"]'), ('[1, [2, 22], "three"]'); test=3D# select json_query(data, 'lax $[0 to 2][1]' WITHOUT ARRAY WRAPPER NULL ON EMPTY ERROR ON ERROR) from test_jsonb_types; json_query ------------ 22 (2 rows) In this case, you can see more clearly that "[0 to 2]" fetches three individual jsonb array elements, and "[1]" treats each of the three jsonb values as independent jsonb arrays, reading the first element of each. This is different from wrapping the intermediate result into an array and accessing the first element of that wrapped array. Another thing I want to point out is that there is a trivial case for "lax" mode when accessing a jsonb object (not a jsonb array) using a JSON array accessor "[0]". For example: -- another setup, still use your data truncate test_jsonb_types; insert into test_jsonb_types VALUES ('{"con": {"a": [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": 100}}}]}}'); test=3D# select (data).con[0] from test_jsonb_types; con ------------------------------------------------------------ {"a": [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": 100}}}]} (1 row) This is equivalent to: test=3D# select json_query(data, 'lax $.con[0]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types; json_query ------------------------------------------------------------ {"a": [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": 100}}}]} (1 row) which is different from the result if we use "strict" mode: test=3D# select json_query(data, 'strict $.con[0]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types; json_query ------------ (1 row) According to SQL:2023: *In lax mode:=E2=80=94 If an operation requires an SQL/JSON array but the o= perand is not an SQL/JSON array, then the operand is first =E2=80=9Cwrapped=E2=80= =9D in an SQL/JSON array prior to performing the operation.=E2=80=94 If an operation = requires something other than an SQL/JSON array, but the operand is an SQL/JSON array, then the operand is =E2=80=9Cunwrapped=E2=80=9D by converting its el= ements into an SQL/JSON sequence prior to performing the operation.=E2=80=94 After applyin= g the preceding resolutions to structural errors, if there is still a structural error , the result is an empty SQL/JSON sequence.* Please refer to the first point to understand the example queries. Because "lax" mode wraps a jsonb object into an array of a single element, accessing it with [0] will always return the same jsonb object. In fact, you can access it with a chain of [0]s and still get the same jsonb object: test=3D# select json_query(data, 'lax $.con[0][0][0][0][0][0]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types; json_query ------------------------------------------------------------ {"a": [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": 100}}}]} (1 row) test=3D# select (data).con[0][0][0][0][0][0] from test_jsonb_types; con ------------------------------------------------------------ {"a": [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": 100}}}]} (1 row) I hope this long explanation helps! [1] https://www.postgresql.org/docs/current/functions-json.html#SQLJSON-QUERY-F= UNCTIONS Best, Alex --000000000000ee0ea4063d8a918f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Chao,=C2=A0

Thanks for re= viewing!=C2=A0

On Thu, Aug 28, 2025 at 8:29=E2=80=AFPM= Chao Li <li.evan.chao@gmail.c= om> wrote:
On Aug 26, 2025, at 11:52, Alexandra Wang <alexandra.wang.oss@gmai= l.com> wrote:

Best,
Alex
<v14-0002-Allo= w-Generic-Type-Subscripting-to-Accept-Dot-No.patch><v14-0003-Export-jsonPathFromParseRe= sult.patch><= v14-0001-Allow-transformation-of-only-a-sublist-of-subscr.patch><= span id=3D"m_1142362315726775605cid:f_mes0fgbb4"><v14-0005-Implement-rea= d-only-dot-notation-for-jsonb.patch><v14-0007-Implement-jsonb-wildcard-member-accessor.= patch><v14-0= 006-Implement-Jsonb-subscripting-with-slicing.patch><v14-0004-Extract-coerce_jsonpath_s= ubscript.patch>


I found a bug.=C2=A0
```<= /div>
INSERT INTO test_jsonb_types (data) VALUES
('[= 1, 2, "three"]'),
('{"con": {"a&= quot;: [{"b": {"c": {"d": 99}}}, {"b&quo= t;: {"c": {"d": 100}}}]}}=E2=80=99);
``= `

If I use a index following a slice, it doesn=E2= =80=99t work:

```
evantest=3D# sele= ct data[0] from test_jsonb_types;
=C2=A0data
------

=C2=A01

(2 rows)
<= br>
evantest=3D# select data[0:2][1] from test_jsonb_types; # Thi= s should return =E2=80=9C2"
=C2=A0data
------


(2 rows)

evantest=3D# select (t.data)['con']['a'][0:1] from te= st_jsonb_types t; # returned the slice properly
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 data
-----------------------------------------------------

=
=C2=A0[{"b": {"c": {"d": 99}}}, {&= quot;b": {"c": {"d": 100}}}]
(2 rows)

evantest=3D# select (t.data)['con']['a&#= 39;][0:1][0] from test_jsonb_types t; # also returned the slice, which is w= rong
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 data
-----------------------------------= ------------------

=C2=A0[{"b": {"c= ": {"d": 99}}}, {"b": {"c": {"d&quo= t;: 100}}}]
(2 rows)
```

We should consider a slice as a container, so the fix is simple. My quick = unpolished fix is:

```
chaol@Chaode= MacBook-Air postgresql % git diff
diff --git a/src/backend/utils/= adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index cb72d12= ca3f..8845dcf239a 100644
--- a/src/backend/utils/adt/jsonbsubs.c<= /div>
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -247,6 +247,= 7 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirection, = Subscripti
=C2=A0 =C2=A0 =C2=A0 =C2=A0 ListCell =C2=A0 *lc;
=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 Datum =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 j= sp;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 int =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 pathlen =3D 0;
+ =C2=A0= =C2=A0 =C2=A0 bool =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0isSlice =3D fa= lse;

=C2=A0 =C2=A0 =C2=A0 =C2=A0 sbsref->refupp= erindexpr =3D NIL;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 sbsref->reflowe= rindexpr =3D NIL;
@@ -285,6 +286,7 @@ jsonb_subscript_make_jsonpa= th(ParseState *pstate, List **indirection, Subscripti

<= div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 if (ai->is_slice)
=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 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 isSlice =3D true;
=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 while (list_length(sbsref->reflowerindexpr) < li= st_length(sbsref->refupperindexpr))
=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 sbsref->reflowerindexpr =3D la= ppend(sbsref->reflowerindexpr, NULL);

@@ -369,6= +371,9 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirec= tion, Subscripti
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 path->next =3D jpi;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 path =3D jpi;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 pathlen++;
+
+ =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 if (isSlice)
+ =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 break;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 }

=C2=A0 =C2= =A0 =C2=A0 =C2=A0 if (pathlen =3D=3D 0)
```

<= /div>
After the fix, let=E2=80=99s test again:

```
evantest=3D# select data[0:2][1] from test_jsonb_types;= # good result
=C2=A0data
------
=C2=A02

(2 rows)

evantest= =3D# select (t.data)['con']['a'][0:1][0] from test_jsonb_ty= pes t; # good result
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 data
-------------------------

=C2=A0{"b&quo= t;: {"c": {"d": 99}}}
(2 rows)
```

TL;DR: It is a feature, n= ot a bug.

See longer explanation below:
=
This behavior aligns with the SQL:2023 standard. While the r= esult you
expected is more intuitive in my opinion, it is incorrect acco= rding to
the spec.

As I mentioned in the co= mmit message of patch v14-0005:

=C2=A0 =C2=A0 T= he SQL standard states that simplified access is equivalent to:
=C2=A0 = =C2=A0 JSON_QUERY (VEP, 'lax $.JC' WITH CONDITIONAL ARRAY WRAPPER N= ULL ON EMPTY NULL ON ERROR)

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

=
The most relevant detail that I want to highlight is "WITH CONDIT= IONAL
ARRAY WRAPPER". The documentation[1] says:
> If the path expression may return multiple values, it = might be
> necessary to wrap those values using the WITH WRAPPER clau= se to make
> it a valid JSON string, because the default behavior is = to not wrap
> them, as if WITHOUT WRAPPER were specified. The WITH WR= APPER clause is
> by default taken to mean WITH UNCONDITIONAL WRAPPER= , which means that
> even a single result value will be wrapped. To a= pply the wrapper only
> when multiple values are present, specify WIT= H CONDITIONAL WRAPPER.
> Getting multiple values in result will be tr= eated as an error if
> WITHOUT WRAPPER is specified.

So, for your test queries:

=
select data[0:2] from test_jsonb_types;
select data[0:2= ][1] from test_jsonb_types;
select (t.data)['con']['a= '][0:1] from test_jsonb_types t;
select (t.data)['con'= ;]['a'][0:1][0] from test_jsonb_types t;

=
We have these equivalents using json_query():

=
select json_query(data, 'lax $[0 to 2]' WITH CONDITIONAL ARRAY= WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types;
sele= ct json_query(data, 'lax $[0 to 2][1]' WITH CONDITIONAL ARRAY WRAPP= ER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types;
select jso= n_query(data, 'lax $.con.a[0 to 1]' WITH CONDITIONAL ARRAY WRAPPER = NULL ON EMPTY NULL ON ERROR) from test_jsonb_types; -- **[NOTE]**
select json_query(data, 'lax $.con.a[0 to 1][0]' WITH CONDITIONAL = ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types; -- **[NOT= E]**

**[NOTE]**: .a and ['a'] (as well as = .con and ['con']) are not
syntactically equivalent, as the dot-n= otation .a is in "lax" mode,
whereas the pre-standard subscrip= t ['a'] is in "strict" mode. I will
discuss this more = in a separate reply to your other comment. However,
for the specific dat= a we inserted in your example table, they happen
to return the same resu= lts. Since our focus here is not dot-notation,
we won=E2=80=99t g= o further into it here.

You can verify correctness with:

test=3D# sel= ect data[0:2] =3D json_query(data, 'lax $[0 to 2]' WITH CONDITIONAL= ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types;
=C2= =A0?column?
----------
=C2=A0t
=C2=A0t
(2 rows)

<= /div>
test=3D# select (data[0:2][1] is NULL) AND (json_query(data, '= ;lax $[0 to 2][1]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON= ERROR) is NULL) from test_jsonb_types;
=C2=A0?column?
----------
= =C2=A0t
=C2=A0t
(2 rows)

test=3D# select (((= t.data)['con']['a'][0:1] IS NULL) AND (json_query(data, = 9;lax $.con.a[0 to 1]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NUL= L ON ERROR) IS NULL)) OR ((t.data)['con']['a'][0:1] =3D jso= n_query(data, 'lax $.con.a[0 to 1]' WITH CONDITIONAL ARRAY WRAPPER = NULL ON EMPTY NULL ON ERROR)) from test_jsonb_types t;
=C2=A0?column?----------
=C2=A0t
=C2=A0t
(2 rows)

test= =3D# select (((t.data)['con']['a'][0:1][0] IS NULL) AND (js= on_query(data, 'lax $.con.a[0 to 1][0]' WITH CONDITIONAL ARRAY WRAP= PER NULL ON EMPTY NULL ON ERROR) IS NULL)) OR ((t.data)['con']['= ;a'][0:1][0] =3D json_query(data, 'lax $.con.a[0 to 1][0]' WITH= CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR)) from test_jsonb_ty= pes t;
=C2=A0?column?
----------
=C2=A0t
=C2=A0t
(2 rows)

All tests show "t", confirming the current = results are all correct.


I think the root = of your confusion is the meaning of CONDITIONAL ARRAY
WRAPPER. So= let=E2=80=99s try more examples:

-- keep your= previous setup
drop table test_jsonb_types;
create tab= le test_jsonb_types (data jsonb);
INSERT INTO test_jsonb_types (d= ata) VALUES ('[1, 2, "three"]'), ('{"con": = {"a": [{"b": {"c": {"d": 99}}}, {&q= uot;b": {"c": {"d": 100}}}]}}');
Let's start with:

select data[0:2]= from test_jsonb_types;

It is equivalent to:=C2=A0=

select json_query(data, 'lax $[0 to 2]' W= ITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_= types;

They all have the following output:

test=3D# select json_query(data, 'lax $[0 to 2]' = WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb= _types;
=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=A0json_query
--------------------= -------------------------------------------------
=C2=A0[1, 2, "thr= ee"]
=C2=A0{"con": {"a": [{"b": {&quo= t;c": {"d": 99}}}, {"b": {"c": {"d&= quot;: 100}}}]}}
(2 rows)

To find out what WITH= CONDITIONAL ARRAY WRAPPER does, let's toggle it
to WITHOUT ARRAY WR= APPER:

test=3D# select json_query(data, 'l= ax $[0 to 2]' WITHOUT ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t= est_jsonb_types;
=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=A0json_query
------------= ---------------------------------------------------------

=C2=A0{&qu= ot;con": {"a": [{"b": {"c": {"d&quo= t;: 99}}}, {"b": {"c": {"d": 100}}}]}}
(2 = rows)

The first row return NULL, because we've= specified NULL ON ERROR, so
let's toggle that as well to ERROR ON E= RROR:

test=3D# select json_query(data, 'la= x $[0 to 2]' WITHOUT ARRAY WRAPPER NULL ON EMPTY ERROR ON ERROR) from t= est_jsonb_types;
ERROR: =C2=A022034: JSON path expression in JSON_QUERY = must return single item when no wrapper is requested
HINT: =C2=A0Use the= WITH WRAPPER clause to wrap SQL/JSON items into an array.
LOCATION: =C2= =A0JsonPathQuery, jsonpath_exec.c:3987

As shown, w= ithout ARRAY WRAPPER, the query produces a sequence of JSON
value items,= not a JSON array.

Note that array wrapping is onl= y applied to the final result of a
jsonpath, not to each intermediate re= sult in the chain. See the
following example:

-- new setup
truncate test_jsonb_types;
INSERT INTO = test_jsonb_types (data) VALUES ('[1, 2, "three"]'), ('= ;[1, [2, 22], "three"]');

test=3D# s= elect json_query(data, 'lax $[0 to 2][1]' WITHOUT ARRAY WRAPPER NUL= L ON EMPTY ERROR ON ERROR) from test_jsonb_types;
=C2=A0json_query
--= ----------

=C2=A022
(2 rows)

In this cas= e, you can see more clearly that "[0 to 2]" fetches three
indi= vidual jsonb array elements, and "[1]" treats each of the threejsonb values as independent jsonb arrays, reading the first element ofeach. This is different from wrapping the intermediate result into an
a= rray and accessing the first element of that wrapped array.
<= br>
Another thing I want to point out is that there is a trivial = case for
"lax" mode when accessing a jsonb object (not a jsonb= array) using a
JSON array accessor "[0]". For example:

-- another setup, still use your data
trun= cate test_jsonb_types;
insert into test_jsonb_types VALUES ('= {"con": {"a": [{"b": {"c": {"d= ": 99}}}, {"b": {"c": {"d": 100}}}]}}= 9;);

test=3D# select (data).con[0] from test_jsonb= _types;
=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 con
---------------------------------= ---------------------------
=C2=A0{"a": [{"b": {&quo= t;c": {"d": 99}}}, {"b": {"c": {"d&= quot;: 100}}}]}
(1 row)

This is equivalent to:= =C2=A0

test=3D# select json_query(data, 'lax $= .con[0]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) fr= om test_jsonb_types;
=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=A0json_query
--------------------= ----------------------------------------
=C2=A0{"a": [{"b= ": {"c": {"d": 99}}}, {"b": {"c&quo= t;: {"d": 100}}}]}
(1 row)

which is d= ifferent from the result if we use "strict" mode:

<= /div>
test=3D# select json_query(data, 'strict $.con[0]' WITH C= ONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types= ;
=C2=A0json_query
------------

(1 row)

According to SQL:2023:

In lax mode:
=E2=80=94 = If an operation requires an SQL/JSON array but the operand is not an SQL/JS= ON array, then the operand is first =E2=80=9Cwrapped=E2=80=9D in an SQL/JSO= N array prior to performing the operation.
=E2=80=94 If an operation req= uires something other than an SQL/JSON array, but the operand is an SQL/JSO= N array, then the operand is =E2=80=9Cunwrapped=E2=80=9D by converting its = elements into an SQL/JSON sequence prior to performing the operation.
= =E2=80=94 After applying the preceding resolutions to structural errors, if= there is still a structural error , the result is an empty SQL/JSON sequen= ce.

Please refer to the first point to understand the example queries.
Because "lax"=C2=A0mode wraps a jsonb object into an array of a= single
element, accessing it with [0] will always return the same jsonb=
object. In fact, you can access it with a chain of [0]s and still getthe same jsonb object:

test=3D# select json_= query(data, 'lax $.con[0][0][0][0][0][0]' WITH CONDITIONAL ARRAY WR= APPER NULL ON EMPTY NULL ON ERROR) from test_jsonb_types;
=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= =A0json_query
----------------------------------------------------------= --
=C2=A0{"a": [{"b": {"c": {"d"= : 99}}}, {"b": {"c": {"d": 100}}}]}
(1 row= )

test=3D# select (data).con[0][0][0][0][0][0] from test_jsonb_types= ;
=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 con
----------------------------------------= --------------------
=C2=A0{"a": [{"b": {"c&quo= t;: {"d": 99}}}, {"b": {"c": {"d": = 100}}}]}
(1 row)

I hope this long explanation h= elps!


--000000000000ee0ea4063d8a918f--