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 1utd7v-00BU4S-Ti for pgsql-hackers@arkaria.postgresql.org; Wed, 03 Sep 2025 02:21:37 +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 1utd7u-006Xw5-05 for pgsql-hackers@arkaria.postgresql.org; Wed, 03 Sep 2025 02:21:34 +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 1utd7t-006Xvr-AU for pgsql-hackers@lists.postgresql.org; Wed, 03 Sep 2025 02:21:34 +0000 Received: from mail-pj1-x1042.google.com ([2607:f8b0:4864:20::1042]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1utd7r-000FrC-0H for pgsql-hackers@postgresql.org; Wed, 03 Sep 2025 02:21:32 +0000 Received: by mail-pj1-x1042.google.com with SMTP id 98e67ed59e1d1-328015abe0bso484953a91.0 for ; Tue, 02 Sep 2025 19:21:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1756866090; x=1757470890; 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=6xzRdfH4CYtW2pU3gqbJNzTQ+qUsjJs+QGVAGU3P8bY=; b=jaEfU6exBDfeT2DAdpkSgcflYURHJEQkZ4vT8wvb1v6MukoKR20XK7wMnH4XzzQxv5 45RgocErpuchck8MKzvglrrv+5rNeCTxHYEjT5uJWCyvLSkU2BHZgEOzFkxD2xzopwQn +eOdhrstICQx1eAruq/TDxKI+IFlss8XsKM2l3OHXQv/e4c1OnVbRlkAoZIy6HXLAFO8 w8zN30ReG3m1/rolR4UC8E3awY9Zbr9fZZyoV29uXPWplSeL3XxEmQVcZU6aFHa4regj bnmnw3O5B3N5bNmVo5lliCSiN7awYwwZoi60J1WoxtepbhQ5lsnM0IUydSHoXfqb4clv W1kg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1756866090; x=1757470890; 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=6xzRdfH4CYtW2pU3gqbJNzTQ+qUsjJs+QGVAGU3P8bY=; b=dRv1BH0pIq9tVjYrNwosx6PEoGAtv8rfzBfxPZKteZQTZUV5rRtlDthl4+TfaShFny jAss7ltFvJvN+ELuHc75+eF8UZ7/Q7HQSnqnK2NTlOffoC+c4H6QKFVPeXfWgjG5DdmU XoyEg+iXsM2wkVJhMn3mLsmb1Vxd4PxbNGux4L/dp2Q6KlTvHomiXAr+SAdBESDjxO64 itKai5+aXbFjvevrRNXBRM2yDVG7F/CuSF8XYCpNvBUTVNxGxnafyJHs6Pfk/Jexh8k7 9cdHCk1kl46QvxJlHEhrzpHfjEM1OrA6G2xZu9YzM5gKRz95qBU3gCfN0b1xWDOjFibM SyEg== X-Forwarded-Encrypted: i=1; AJvYcCVbRlE6CPbBwBfHGhGAosL3H9r29iZbUxEReQuo6IOFdyjP9DwCaSs7967CGhJhG0HfnD+e4ZfkooeFKfi3@postgresql.org X-Gm-Message-State: AOJu0YxMSNI3Ehq/87z4DARDb2ro3yKS4XjlCXKLeOXBrNfLhXVBfPzY DgFlBXNsnxYzv9L5fayhiUzWx7dKDsnnvx0ZYRoCTW4PNG45Me2oGtjM9SoBznowm/hCwJycpOa Dhz1WC4vsZKq8pi07g1skrczpgJQwNy8= X-Gm-Gg: ASbGncs/btZobfUA+nrpcUYkKUq7OUQ/Dx4oA558fHE694HdZtpasXJvp5ZZ6R6T+Z5 YzhAI2HQT+4qaT8vhpNYjUUbnti2g1VGr4AAcLxg+2e+0NFVC73FEweOI8eLgWSfWrAwfquNyII RvLI+QA7AboFizROlmyttGq1sA6iu+HxxK8xewWMqmMZxmXLxlusMW3o1iRyWOWz9GNvogBu1Cs nSte1rE4FVIHqBsTORxGsLyIUCeCg== X-Google-Smtp-Source: AGHT+IHspT3Idt25Y6mIgY4FPpl/p2r9e8FKAzXmCwMTDEPBjpc0OAEc4ayG+JbbHMQ2PMZdzlhtRQP43oRtlUapgUk= X-Received: by 2002:a17:90b:52cf:b0:325:15bf:4dc2 with SMTP id 98e67ed59e1d1-3281531d3femr18409416a91.0.1756866090092; Tue, 02 Sep 2025 19:21:30 -0700 (PDT) MIME-Version: 1.0 References: <21fa36b4-6d56-4556-b524-9e3955b922c8@eisentraut.org> <290418A2-3A9B-4304-AAAD-4A40CFF1CB93@gmail.com> <98C6F445-773B-4C82-8198-7F5CD56D5252@gmail.com> In-Reply-To: From: Alexandra Wang Date: Tue, 2 Sep 2025 19:20:53 -0700 X-Gm-Features: Ac12FXxHxGtQNpm40FYEMlV8oPFsDwyIlfh_2V2QLrbb0_beb6bPFhS7o_rPmDs Message-ID: Subject: Re: SQL:2023 JSON simplified accessor support To: Chao Li Cc: Nikita Glukhov , jian he , Nikita Malakhov , Vik Fearing , Mark Dilger , Matheus Alcantara , Peter Eisentraut , Andrew Dunstan , PostgreSQL Hackers , "David E. Wheeler" Content-Type: multipart/alternative; boundary="00000000000037cbd4063ddc4315" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000037cbd4063ddc4315 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Chao, Continue from my previous email. On Mon, Sep 1, 2025 at 8:32=E2=80=AFPM Chao Li wro= te: > > > On Sep 2, 2025, at 10:59, Chao Li wrote: > > jsonbsubs doesn=E2=80=99t need to check is_slice flag as well, I will exp= lain that > in my next email tougher with my new comments. > > > > Now jsonb_check_json_needed() loops over all indirections: > > static bool > jsonb_check_jsonpath_needed(List *indirection) > { > ListCell *lc; > > foreach(lc, indirection) > { > Node *accessor =3D lfirst(lc); > > if (IsA(accessor, String)) > return true; > else > { > Assert(IsA(accessor, A_Indices)); > > if (castNode(A_Indices, accessor)->is_slice) > return true; > } > } > > return false; > } > > Let=E2=80=99s consider this statement: select data[=E2=80=98a=E2=80=99][= =E2=80=98b=E2=80=99].c from jsonb_table. > > For this indirection list, first two elements are non-slice indices, and > the third is a string accessor. So json_check_jsonpath_needed() will retu= rn > true. > > Then: > > static void > jsonb_subscript_transform(SubscriptingRef *sbsref, > List **indirection, > ParseState *pstate, > bool isSlice, > bool isAssignment) > { > List *upperIndexpr =3D NIL; > ListCell *idx; > > /* Determine the result type of the subscripting operation; always jsonb = */ > sbsref->refrestype =3D JSONBOID; > sbsref->reftypmod =3D -1; > > if (isSlice || jsonb_check_jsonpath_needed(*indirection)) > { > jsonb_subscript_make_jsonpath(pstate, indirection, sbsref); > if (sbsref->refjsonbpath) > return; > } > > It will fall into the call to json_subscript_make_jsonpath(), and it > cannot process [=E2=80=98a=E2=80=99], so sbsref->refjsonbpath will be NUL= L, then it will > fall through down to the logic of processing [=E2=80=98a=E2=80=99]. > > That=E2=80=99s actually a waste of looping over the entire indirection li= st and > making an unnecessary call to jsonb_subscript_make_jsonpath(). > > In json_check_jsonpath_needed(), we can only check the first item. Also, > as jsonb_check_jsonpath_needed() already has the logic of checking > is_slice, here in jsonb_subscript_transform(), we don=E2=80=99t need to c= heck > =E2=80=9CisSlice=E2=80=9D in the =E2=80=9Cif=E2=80=9D condition at all. > > I made the change locally, and =E2=80=9Cmake check=E2=80=9D passed. > > My dirty change is like: > ``` > @@ -118,11 +118,11 @@ coerce_jsonpath_subscript_to_int4_or_text(ParseStat= e > *pstate, Node *subExpr) > static bool > jsonb_check_jsonpath_needed(List *indirection) > { > - ListCell *lc; > + //ListCell *lc; > > - foreach(lc, indirection) > - { > - Node *accessor =3D lfirst(lc); > + //foreach(lc, indirection) > + //{ > + Node *accessor =3D lfirst(list_head(indirection)); > > if (IsA(accessor, String) || IsA(accessor, A_Star)) > return true; > @@ -133,7 +133,8 @@ jsonb_check_jsonpath_needed(List *indirection) > if (castNode(A_Indices, accessor)->is_slice) > return true; > } > - } > + //break; > + //} > > return false; > } > > @@ -401,7 +435,7 @@ jsonb_subscript_transform(SubscriptingRef *sbsref, > sbsref->refrestype =3D JSONBOID; > sbsref->reftypmod =3D -1; > > - if (isSlice || jsonb_check_jsonpath_needed(*indirection)) > + if (jsonb_check_jsonpath_needed(*indirection)) > { > jsonb_subscript_make_jsonpath(pstate, indirection, sbsref= ); > if (sbsref->refjsonbpath) > ``` > This change would give an incorrect result for an accessor like "[0].a" when the jsonb column data is a jsonb object instead of a jsonb array. I've added two new test cases to cover this scenario: In jsonb.sql, the newly added tests are: select (jb)[0].a from test_jsonb_dot_notation; -- returns same result as (jb).a select (jb)[1].a from test_jsonb_dot_notation; -- returns NULL In v5, I included isSlice as an argument of jsonb_check_jsonpath_needed(), so we no longer need to check the is_slice field of each indirection element. That should make the check more efficient. In jsonb_subscript_make_jsonpath(), I also moved the check for cases like ['a'] earlier, as you suggested. These changes should eliminate some unnecessary comparisons and allocate then immediately free. I also want to point out that we should not recommend users mix the standard simplified accessor syntax with the pre-standard jsonb subscripting, because: 1. It is confusing. I would assume that a user who uses dot-notation understands its definition. As we discussed earlier, (jb).a and (jb)['a'] can return different results: the former is in "lax" mode and with a conditional array wrapper, while the latter has neither. If a user queries something like (jb)['a'].b['c'], I think most likely she wants either (jb)['a']['b']['c'] or (jb).a.b.c. 2. As you said, it hurts performance. When a pre-standard non-integer subscript appears before a dot-notation, we bail out of creating a jsonpath. Alternatively, if a user really wants (jb)['a'].b['c'], it could be explicitly written as (((jb)['a']).b)['c']. This avoids unnecessary looping and bailout. In my earlier revisions (up to v12), I actually emitted warnings when users mixed syntaxes. Starting from v13, I removed the warning based on Jian=E2=80=99s feedback. Here's the context: On Wed, Aug 20, 2025 at 9:53=E2=80=AFPM Alexandra Wang wrote: > On Thu, Jul 10, 2025 at 1:54=E2=80=AFAM jian he > wrote: > >> after applied v12-0001 to v12-0006 >> + /* emit warning conditionally to minimize duplicate warnings */ >> + if (list_length(*indirection) > 0) >> + ereport(WARNING, >> + errcode(ERRCODE_WARNING), >> + errmsg("mixed usage of jsonb simplified accessor syntax and jsonb >> subscripting."), >> + errhint("use dot-notation for member access, or use non-null integer >> constants subscripting for array access."), >> + parser_errposition(pstate, warning_location)); >> >> src7=3D# select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['1'::int= 8]; >> WARNING: mixed usage of jsonb simplified accessor syntax and jsonb >> subscripting. >> LINE 1: ...t ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['1'::int8].= .. >> ^ >> HINT: use dot-notation for member access, or use non-null integer >> constants subscripting for array access. >> ERROR: subscript type bigint is not supported >> LINE 1: ...t ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['1'::int8].= .. >> ^ >> HINT: jsonb subscript must be coercible to either integer or text. >> >> The above example looks very bad. location printed twice, hint message >> is different. >> two messages level (ERROR, WARNING). >> ... omitting some irelavent comments... > > I see your confusion about the WARNING/ERROR messages. My intent was > to encourage users to use consistent syntax flavors rather than mixing > the SQL standard JSON simplified accessor with the pre-standard > PostgreSQL jsonb subscripting. In the meantime, I want to support > mixed syntax flavors as much as possible. However, your feedback makes > it clear that users don=E2=80=99t need that level of details. So, in v13 = I=E2=80=99ve > removed the WARNING message and instead added a comment explaining how > we support mixed syntaxes. > > Here's the relevant diff between v12 and v13, hope it helps: > > --- a/src/backend/utils/adt/jsonbsubs.c > +++ b/src/backend/utils/adt/jsonbsubs.c > @@ -247,7 +247,6 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, Lis= t > **indirection, Subscripti > ListCell *lc; > Datum jsp; > int pathlen =3D 0; > - int warning_location =3D -1; > > sbsref->refupperindexpr =3D NIL; > sbsref->reflowerindexpr =3D NIL; > @@ -311,10 +310,27 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, > List **indirection, Subscripti > if (jpi_from =3D=3D NULL) > { > /* > - * postpone emitting the warning > until the end of this > - * function > + * Break out of the loop if the > subscript is not a > + * non-null integer constant, so > that we can fall back to > + * jsonb subscripting logic. > + * > + * This is needed to handle cases > with mixed usage of SQL > + * standard json simplified > accessor syntax and PostgreSQL > + * jsonb subscripting syntax, e.g= : > + * > + * select (jb).a['b'].c from > jsonb_table; > + * > + * where dot-notation (.a and .c) > is the SQL standard json > + * simplified accessor syntax, an= d > the ['b'] subscript is > + * the PostgreSQL jsonb > subscripting syntax, because 'b' > + * is not a non-null constant > integer and cannot be used > + * for json array access. > + * > + * In this case, we cannot create > a JsonPath item, so we > + * break out of the loop and let > + * jsonb_subscript_transform() > handle this indirection as > + * a PostgreSQL jsonb subscript. > */ > - warning_location =3D > exprLocation(ai->uidx); > pfree(jpi->value.array.elems); > pfree(jpi); > break; > @@ -360,14 +376,6 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, > List **indirection, Subscripti > > *indirection =3D list_delete_first_n(*indirection, pathlen); > > - /* emit warning conditionally to minimize duplicate warnings */ > - if (list_length(*indirection) > 0) > - ereport(WARNING, > - errcode(ERRCODE_WARNING), > - errmsg("mixed usage of jsonb simplified > accessor syntax and jsonb subscripting."), > - errhint("use dot-notation for member > access, or use non-null integer constants subscripting for array access."= ), > - parser_errposition(pstate, > warning_location)); > - > jsp =3D jsonPathFromParseResult(&jpres, 0, NULL); > I think we should definitely add detailed documentation about mixed syntax. However, I'm not convinced it's worth putting more effort into optimizing performance for those cases. Alternatively, we could fail outright in those cases, but I don't think that would be the best user experience. Thoughts? On Mon, Sep 1, 2025 at 8:32=E2=80=AFPM Chao Li wr= ote: > The other comment is that jsonb_subscript_make_jsonpath() can be optimize= d > a little bit. When indices and dot notations are mixed, it will always hi= t > the clause =E2=80=9Cif (api_from =3D=3D NULL)=E2=80=9D. In this case, mem= ory of jpi has been > allocated, and with the =E2=80=9Cif=E2=80=9D we need to free the memory. = So, we can pull up > calculating jpi_from, if it is NULL, then we don=E2=80=99t need to alloca= te and > free memory. My dirty change is like: > > ``` > else if (IsA(accessor, A_Indices)) > { > + JsonPathParseItem *jpi_from =3D NULL; > A_Indices *ai =3D castNode(A_Indices, accessor); > > + if (!ai->is_slice) > + { > + Assert(ai->uidx && !ai->lidx); > + jpi_from =3D make_jsonpath_item_expr(psta= te, > ai->uidx, &sbsref->refupperindexpr, true); > + if (jpi_from =3D=3D NULL) > + { > + /* > + * Break out of the loop if the > subscript is not a > + * non-null integer constant, so > that we can fall back to > + * jsonb subscripting logic. > + * > + * This is needed to handle cases > with mixed usage of SQL > + * standard json simplified > accessor syntax and PostgreSQL > + * jsonb subscripting syntax, e.g= : > + * > + * select (jb).a['b'].c from > jsonb_table; > + * > + * where dot-notation (.a and .c) > is the SQL standard json > + * simplified accessor syntax, an= d > the ['b'] subscript is > + * the PostgreSQL jsonb > subscripting syntax, because 'b' > + * is not a non-null constant > integer and cannot be used > + * for json array access. > + * > + * In this case, we cannot create > a JsonPath item, so we > + * break out of the loop and let > + * jsonb_subscript_transform() > handle this indirection as > + * a PostgreSQL jsonb subscript. > + */ > + break; > + } > + } > + > jpi =3D make_jsonpath_item(jpiIndexArray); > jpi->value.array.nelems =3D 1; > jpi->value.array.elems =3D > palloc(sizeof(jpi->value.array.elems[0])); > @@ -303,12 +337,12 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, > List **indirection, Subscripti > } > else > { > - JsonPathParseItem *jpi_from =3D NULL; > + //JsonPathParseItem *jpi_from =3D NULL; > > - Assert(ai->uidx && !ai->lidx); > - jpi_from =3D make_jsonpath_item_expr(psta= te, > ai->uidx, &sbsref->refupperindexpr, true); > - if (jpi_from =3D=3D NULL) > - { > + //Assert(ai->uidx && !ai->lidx); > + //jpi_from =3D > make_jsonpath_item_expr(pstate, ai->uidx, &sbsref->refupperindexpr, true)= ; > + //if (jpi_from =3D=3D NULL) > + //{ > /* > * Break out of the loop if the > subscript is not a > * non-null integer constant, so > that we can fall back to > @@ -331,10 +365,10 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, > List **indirection, Subscripti > * jsonb_subscript_transform() > handle this indirection as > * a PostgreSQL jsonb subscript. > */ > - pfree(jpi->value.array.elems); > - pfree(jpi); > - break; > - } > + // pfree(jpi->value.array.elems); > + // pfree(jpi); > + // break; > + //} > > jpi->value.array.elems[0].from =3D jpi_fr= om; > jpi->value.array.elems[0].to =3D NULL; > ``` > > With this change, =E2=80=9Cmake check=E2=80=9D also passed. > Fixed. Thank you! On Mon, Sep 1, 2025 at 8:32=E2=80=AFPM Chao Li wr= ote: > The third comment is about test cases. I only see tests for indices > following dot notation, like data.a[=E2=80=98b=E2=80=99], but not the rev= erse. Let=E2=80=99s add a > few test cases for dot notation following indices, like data[=E2=80=98a= =E2=80=99].b. > I believe both cases are already covered. Please take a look at src/test/regress/jsonb.sql; there's a "-- mixed syntax" section. Best, Alex --00000000000037cbd4063ddc4315 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Chao,

Continue from = my previous=C2=A0email.

On Mon, Sep 1, 2025 at 8:3= 2=E2=80=AFPM Chao Li <li.evan.= chao@gmail.com> wrote:


On Sep 2, 2025, at 10:59, Chao Li <li.evan.chao@gmail.com= > wrote:

= jsonbsubs doesn=E2=80=99t need to check is_slice flag as well, I will expla= in that in my next email tougher with my new comments.


Now jsonb_check_json_needed()= loops over all indirections:

static bool
jsonb_check_jsonpath_needed(List *= indirection)
{
ListCell *lc;

= foreach(lc, indirection)
= {
Node = *accessor =3D lf= irst(lc);

if (IsA(accessor, String))
= return true;
else<= /div>
{
Assert(IsA(accessor, A_Indices));

if (castNode(A_Indices, accessor)->is_slice)
return true;
}
}

return false;
}

Let=E2=80=99s co= nsider this statement: select data[=E2=80=98a=E2=80=99][=E2=80=98b=E2=80=99= ].c from jsonb_table.

For this indirection list, f= irst two elements are non-slice indices, and the third is a string accessor= . So json_check_jsonpath_needed() will return true.

Then:

static void
jsonb= _subscript_transform(S= ubscriptingRef *sbsref,
List **indirect= ion,
= ParseState *pstate,
bool isSlice,
= bool= isAssignment)
{
List *upperIndexpr =3D = NIL;
ListCell = *idx;

= /* Determine the result type of the subscripting operation; always json= b */
sbsref->refrestype = =3D JSONBOID;
sbsref->re= ftypmod =3D -1;

= if (isSlice || jsonb_check_jsonpath_needed(*indirection))
{
jsonb_subscript_make_jsonpath(pstate, indirectio= n, sbsref);
if (sbsref->refjsonbpath)
<= span style=3D"color:rgb(216,160,223)">return;
}

It will fall into the call = to json_subscript_make_jsonpath(), and it cannot process [=E2=80=98a=E2=80= =99], so sbsref->refjsonbpath will be NULL, then it will fall through do= wn to the logic of processing [=E2=80=98a=E2=80=99].

That=E2=80=99s actually a waste of looping over the entire indirection l= ist and making an unnecessary call to jsonb_subscript_make_jsonpath().

In json_check_jsonpath_needed(), we can only check the= first item. Also, as jsonb_check_jsonpath_needed() already has the logic o= f checking is_slice, here in jsonb_subscript_transform(), we don=E2=80=99t = need to check =E2=80=9CisSlice=E2=80=9D in the =E2=80=9Cif=E2=80=9D conditi= on at all.

I made the change locally, and =E2=80= =9Cmake check=E2=80=9D passed.

My dirty change is = like:
```
@@ -118,11 +118,11 @@ coerce_jsonpath_su= bscript_to_int4_or_text(ParseState *pstate, Node *subExpr)
=C2=A0= static bool
=C2=A0jsonb_check_jsonpath_needed(List *indirection)<= /div>
=C2=A0{
- =C2=A0 =C2=A0 =C2=A0 ListCell =C2=A0 *lc;
+ =C2=A0 =C2=A0 =C2=A0 //ListCell =C2=A0 *lc;

- =C2=A0 =C2=A0 =C2=A0 foreach(lc, indirection)
- =C2=A0 =C2= =A0 =C2=A0 {
- =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 N= ode =C2=A0 =C2=A0 =C2=A0 *accessor =3D lfirst(lc);
+ =C2=A0 =C2= =A0 =C2=A0 //foreach(lc, indirection)
+ =C2=A0 =C2=A0 =C2=A0 //{<= /div>
+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Node =C2=A0 = =C2=A0 =C2=A0 *accessor =3D lfirst(list_head(indirection));

<= /div>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 if (IsA(a= ccessor, String) || IsA(accessor, A_Star))
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 return true;=
@@ -133,7 +133,8 @@ jsonb_check_jsonpath_needed(List *indirectio= n)
=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 (castNode(A_Indices, accessor)->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 return 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 =C2=A0 =C2=A0 //bre= ak;
+ =C2=A0 =C2=A0 =C2=A0 //}

=C2=A0 = =C2=A0 =C2=A0 =C2=A0 return false;
=C2=A0}

@@ -401,7 +435,7 @@ jsonb_subscript_transform(SubscriptingRef= *sbsref,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 sbsref->refrestype =3D J= SONBOID;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 sbsref->reftypmod =3D -1;=

- =C2=A0 =C2=A0 =C2=A0 if (isSlice || jsonb_check= _jsonpath_needed(*indirection))
+ =C2=A0 =C2=A0 =C2=A0 if (jsonb_= check_jsonpath_needed(*indirection))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = {
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 jsonb_s= ubscript_make_jsonpath(pstate, indirection, sbsref);
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 if (sbsref->refjsonbpath)<= /div>
```

This change= would give an incorrect result for an accessor like
"[0].a" w= hen the jsonb column data is a jsonb object instead of a
jsonb array.=C2= =A0 I've added two new test cases to cover this scenario:

In jsonb.sql, the newly added tests are:
select (jb)[0= ].a from test_jsonb_dot_notation; -- returns same result as (jb).a
selec= t (jb)[1].a from test_jsonb_dot_notation; -- returns NULL

In v5, I included isSlice as an argument of
jsonb= _check_jsonpath_needed(), so we no longer need to check the
is_slice fie= ld of each indirection element. That should make the check
more efficien= t. In jsonb_subscript_make_jsonpath(), I also moved the
check for cases = like ['a'] earlier, as you suggested. These changes
should elimi= nate some unnecessary comparisons and allocate then
immediately free.

I also want to point out that we sh= ould not recommend users mix the
standard simplified accessor syntax wit= h the pre-standard jsonb
subscripting, because:

1. It is confusing. I would assume that a user who uses d= ot-notation
understands its definition. As we discussed earlier, (jb).a = and
(jb)['a'] can return different results: the former is in &qu= ot;lax" mode
and with a conditional array wrapper, while the latter= has neither. If
a user queries something like (jb)['a'].b['= c'], I think most likely
she wants either (jb)['a']['b&#= 39;]['c'] or (jb).a.b.c.

= 2. As you said, it hurts performance. When a pre-standard non-integer
su= bscript appears before a dot-notation, we bail out of creating a
jsonpat= h. Alternatively, if a user really wants =C2=A0(jb)['a'].b['c&#= 39;], it
could be explicitly written as (((jb)['a']).b)['c&#= 39;]. This avoids
unnecessary looping and bailout.

In my earlier revisions (up to v12), I actually emit= ted warnings when
users mixed syntaxes. Starting from v13, I removed the= warning based
on Jian=E2=80=99s feedback. Here's the context:

On Wed, Aug 20, 2025 at 9:53=E2=80=AFPM Alexandra Wang <alexandra.wang.oss@gmail.com> wrot= e:
On Thu, Jul 10, 2025 at 1:54=E2=80=AFAM jian he <jian.univers= ality@gmail.com> wrote:
after applied v12-0001 to v12-0006+ /* emit warning conditionally to minimize duplicate warnings */
+ if = (list_length(*indirection) > 0)
+ ereport(WARNING,
+ errcode(ERRCO= DE_WARNING),
+ errmsg("mixed usage of jsonb simplified accessor syn= tax and jsonb
subscripting."),
+ errhint("use dot-notation = for member access, or use non-null integer
constants subscripting for ar= ray access."),
+ parser_errposition(pstate, warning_location));
=
src7=3D# select ('{"a": 1, "b": "c", = "d": [1, 2, 3]}'::jsonb).d['1'::int8];
WARNING:=C2= =A0 mixed usage of jsonb simplified accessor syntax and jsonb
subscripti= ng.
LINE 1: ...t ('{"a": 1, "b": "c", = "d": [1, 2, 3]}'::jsonb).d['1'::int8]...
=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 =C2=A0 =C2=A0 =C2=A0 =C2=A0^
HINT:=C2= =A0 use dot-notation for member access, or use non-null integer
constant= s subscripting for array access.
ERROR:=C2=A0 subscript type bigint is n= ot supported
LINE 1: ...t ('{"a": 1, "b": "= c", "d": [1, 2, 3]}'::jsonb).d['1'::int8]...
= =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 =C2=A0 =C2=A0 =C2=A0 =C2=A0^
H= INT:=C2=A0 jsonb subscript must be coercible to either integer or text.
=
The above example looks very bad. location printed twice, hint message<= br>is different.
two messages level (ERROR, WARNING).
... omitting so= me irelavent comments...
=C2=A0
I see your confusion about the WARNING/ERROR messages. My intent was=
to encourage users to use consistent syntax flavors rather than mixing<= br>the SQL standard JSON simplified accessor with the pre-standard
Postg= reSQL jsonb subscripting. In the meantime, I want to support
mixed synta= x flavors as much as possible. However, your feedback makes
it clear tha= t users don=E2=80=99t need that level of details. So, in v13 I=E2=80=99veremoved the WARNING message and instead added a comment explaining howwe support mixed syntaxes.

Here's the rel= evant diff between v12 and v13, hope it helps:

---= a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsu= bs.c
@@ -247,7 +247,6 @@ jsonb_subscript_make_jsonpath(ParseState *pstat= e, 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 jsp;
=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 int =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 warning_location =3D -1;

=C2=A0 =C2=A0 =C2=A0 =C2=A0 s= bsref->refupperindexpr =3D NIL;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 sbsref-&g= t;reflowerindexpr =3D NIL;
@@ -311,10 +310,27 @@ jsonb_subscript_make_js= onpath(ParseState *pstate, List **indirection, Subscripti
=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 if (jpi_from =3D=3D NULL)
=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 =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 = =C2=A0 =C2=A0* postpone emitting the warning until the end of this
- =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* function+ =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* Brea= k out of the loop if the subscript is not a
+ =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* non-null integer constant, so th= at we can fall back to
+ =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* jsonb subscripting logic.
+ =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 =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* This is needed to handle cas= es with mixed usage of SQL
+ =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* standard json simplified accessor syntax and Post= greSQL
+ =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* jsonb subscripting syntax, e.g:
+ =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 =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* select (jb).a['b'].c from jsonb_ta= ble;
+ =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 =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* where dot-notation (.a and .c) is the SQL standard json
+ =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* simplified acc= essor syntax, and the ['b'] subscript is
+ =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* the PostgreSQL jsonb subscri= pting syntax, because 'b'
+ =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* is not a non-null constant integer and can= not be used
+ =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* for json array access.
+ =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 =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* In this case, we cannot create a JsonPath = item, so we
+ =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* break out of the loop and let
+ =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* jsonb_subscript_transform() handle = this indirection as
+ =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* a PostgreSQL jsonb subscript.
=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 =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 warning_location =3D exprLoca= tion(ai->uidx);
=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 pfree(jpi->value.array.elems);
=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 pfree(jpi);
=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 break;
@@ -360,14 +376,= 6 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirection, = Subscripti

=C2=A0 =C2=A0 =C2=A0 =C2=A0 *indirection =3D list_delete_= first_n(*indirection, pathlen);

- =C2=A0 =C2=A0 =C2=A0 /* emit warni= ng conditionally to minimize duplicate warnings */
- =C2=A0 =C2=A0 =C2= =A0 if (list_length(*indirection) > 0)
- =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 ereport(WARNING,
- =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 e= rrcode(ERRCODE_WARNING),
- =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 errmsg("mi= xed usage of jsonb simplified accessor syntax and jsonb subscripting."= ),
- =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 errhint("use dot-notation for m= ember access, or use non-null integer constants subscripting for array acce= ss."),
- =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 parser_errposition(pstate,= warning_location));
-
=C2=A0 =C2=A0 =C2=A0 =C2=A0 jsp =3D jsonPathFr= omParseResult(&jpres, 0, NULL);

I think w= e should definitely add detailed documentation about mixed
syntax. Howev= er, I'm not convinced it's worth putting more effort into
optimi= zing performance for those cases. Alternatively, we could fail
outright = in those cases, but I don't think that would be the best user
experi= ence. Thoughts?

=C2=A0On Mon, Sep 1, 2025 at 8= :32=E2=80=AFPM Chao Li <li.eva= n.chao@gmail.com> wrote:
The other comment is that jsonb_subscript_make_jsonpa= th() can be optimized a little bit. When indices and dot notations are mixe= d, it will always hit the clause =E2=80=9Cif (api_from =3D=3D NULL)=E2=80= =9D. In this case, memory of jpi has been allocated, and with the =E2=80=9C= if=E2=80=9D we need to free the memory. So, we can pull up calculating jpi_= from, if it is NULL, then we don=E2=80=99t need to allocate and free memory= . My dirty change is like:

```
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 else if (IsA(accessor,= A_Indices))
=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 JsonPathParseItem *jpi_from =3D NULL;
=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 A_Indices =C2=A0*ai =3D castNode(A_Indices, accessor);

+ =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 Assert(ai->uidx && !ai->lidx);
+ =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 jpi_from =3D make_jsonpath_item_expr(pst= ate, ai->uidx, &sbsref->refupperindexpr, 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 if (jpi_from =3D=3D NULL)
+ =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 =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* Break out of the loop if the subscript is = not a
+ =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* non-null integer constant, so that we can fall back to
+ =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* json= b subscripting logic.
+ =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 =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* This is needed to handle cases with mixed = usage of SQL
+ =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* standard json simplified accessor syntax and PostgreSQL<= /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 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0* jsonb subscripting syntax, e.g:
+ =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 =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* select (jb).a['b'].c= from jsonb_table;
+ =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 =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* where dot-notation (.a and .c) is the SQL = standard json
+ =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* simplified accessor syntax, and the ['b'] subscr= ipt is
+ =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* the PostgreSQL jsonb subscripting syntax, because 'b'
+ =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* is not a non-null constant integer and cannot be used
+ =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* for json ar= ray access.
+ =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 =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* In this case, we cannot create a JsonPath item, s= o we
+ =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* break out of the loop and let
+ =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* jsonb_subscript_transform() = handle this indirection as
+ =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* a PostgreSQL jsonb subscript.
+ = =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*/
<= 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 =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 =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 =C2=A0 = =C2=A0 =C2=A0 jpi =3D make_jsonpath_item(jpiIndexArray);
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 jpi->value.array.nelems =3D 1;
=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 jpi->value.arra= y.elems =3D palloc(sizeof(jpi->value.array.elems[0]));
@@ -303= ,12 +337,12 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, List **ind= irection, Subscripti
=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 else
=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 JsonPathParseIt= em *jpi_from =3D NULL;
+ =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 //JsonPa= thParseItem *jpi_from =3D NULL;

- =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 Assert(ai->uidx && !ai->lidx);
- = =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 jpi_from =3D make_jsonpath_item_expr(pstate= , ai->uidx, &sbsref->refupperindexpr, 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 if (jpi_from =3D=3D NULL)
- =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 =C2=A0 =C2=A0 =C2=A0 //Assert= (ai->uidx && !ai->lidx);
+ =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 //jpi_from =3D make_jsonpath_item_expr(pstate, ai->uidx, &sbs= ref->refupperindexpr, 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 /= /if (jpi_from =3D=3D NULL)
+ =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 =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 =C2= =A0 =C2=A0* Break out of the loop if the subscript is not a
=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* non-= null integer constant, so that we can fall back to
@@ -331,10 +36= 5,10 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirectio= n, Subscripti
=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* jsonb_subscript_transform() handle this indirecti= on as
=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* a PostgreSQL jsonb subscript.
=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 =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 pfree(jpi->value.ar= ray.elems);
- =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 pfree(jpi);
- =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 break;
- =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 =C2=A0 =C2=A0 =C2=A0 // =C2=A0 =C2=A0 =C2=A0pfree(jpi->= ;value.array.elems);
+ =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=A0pfree(jpi);
+ =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=A0break;
+ =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 =C2=A0 =C2=A0 =C2=A0 =C2=A0 jpi->value.a= rray.elems[0].from =3D jpi_from;
=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 jpi->value.array.elems[0].to =3D NULL;
```
<= div>
With this change, =E2=80=9Cmake check=E2=80=9D also pass= ed.

Fixed. Thank you!
=C2=A0
=C2=A0On Mon, Sep 1, 2025 at 8:32=E2=80=AFPM Chao Li <= li.evan.chao@gmail.com> wr= ote:
The t= hird comment is about test cases. I only see tests for indices following do= t notation, like data.a[=E2=80=98b=E2=80=99], but not the reverse. Let=E2= =80=99s add a few test cases for dot notation following indices, like data[= =E2=80=98a=E2=80=99].b.

I belie= ve both cases are already covered. Please take a look at
src/test/regres= s/jsonb.sql; there's a "-- mixed syntax" section.

Best,
Alex
--00000000000037cbd4063ddc4315--