From: Chao Li <li.evan.chao@gmail.com>
Message-Id: <3EF2F248-1881-4764-96D7-CC0957AC68F7@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_E499A277-B31F-4FBB-A29B-55313104CB31"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\))
Subject: Re: SQL:2023 JSON simplified accessor support
Date: Fri, 29 Aug 2025 11:42:31 +0800
In-Reply-To: 
 <CAK98qZ35eF+9MZuqR4HNrmebyBFdNiNLiLZHpQPB7S7OUk-DDQ@mail.gmail.com>
Cc: jian he <jian.universality@gmail.com>,
 Nikita Malakhov <hukutoc@gmail.com>,
 Vik Fearing <vik@postgresfriends.org>,
 Mark Dilger <mark.dilger@enterprisedb.com>,
 Matheus Alcantara <matheusssilv97@gmail.com>,
 Peter Eisentraut <peter@eisentraut.org>,
 Andrew Dunstan <andrew@dunslane.net>,
 Nikita Glukhov <glukhov.n.a@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 "David E. Wheeler" <david@justatheory.com>
To: Alexandra Wang <alexandra.wang.oss@gmail.com>
References: <d2d872c3-84f9-48f6-9963-f4537f02bc91@eisentraut.org>
 <adb15d59-2e6b-4d2e-ac5b-7aaea41f738f@gmail.com>
 <CAK98qZ2QGcyJrJAFv9wjY6S8yP9dUVnmG9Gb4OXuzuMMuM1Z5Q@mail.gmail.com>
 <b24dc997-d78f-47bd-b323-14e68232d127@dunslane.net>
 <21fa36b4-6d56-4556-b524-9e3955b922c8@eisentraut.org>
 <CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com>
 <CAK98qZ1za8XgOLY+2hQMPGoxVYFyh=dDkM2dZPVeJK4J6poyvA@mail.gmail.com>
 <CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@mail.gmail.com>
 <CAK98qZ1rZaVNy6ViangQom4iinZVH7=ebqhTsPxMQbN5ZtE9XQ@mail.gmail.com>
 <CAFY6G8cY5SUG5L-0ryVpom6HynE49p8-XQ59qkaEgnZZJ-c4Rg@mail.gmail.com>
 <CAFY6G8eUGcU3A4AHprgYbSOAOj7+WGhGxS_YP0wd2+aCpZTiNg@mail.gmail.com>
 <CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@mail.gmail.com>
 <CAHgHdKtL9nNaKXGCLt9gWugVzYWKhoBDQ7NESUwdCBty8kFK-A@mail.gmail.com>
 <CAK98qZ1nz6ZZhQqTOCNwRguZE5GsBLW5BQT_k=s7AA6gc2CN_g@mail.gmail.com>
 <c7dc6735-57cb-4287-871f-4c92a7205357@postgresfriends.org>
 <CAN-LCVM6A9z6AzxsEX-vx9=3XgEUU6+zjUqejQw8LfjaRY5P1A@mail.gmail.com>
 <CAK98qZ2Pmf6ZSChLq+CEPKJ_8jSa0gFTNJWJTcWbCziDpqa=CA@mail.gmail.com>
 <CACJufxEt5bL-xYojenA7x1Fq=-DfhW3KGH=3Zz9TjJ1k95=uuQ@mail.gmail.com>
 <CAK98qZ0whQ=c+JGXbGSEBxCtLgy6sf-YGYqsKTAGsS-wt0wj+A@mail.gmail.com>
 <CACJufxHqiKbh1RN4-rquYdnS8qK9kEQq=bpt6ED_yo1+OkU8jg@mail.gmail.com>
 <CAK98qZ19bC=Qw9rWGOFKyX4B-fg1XQWEbV2OWAawqWC62fx79A@mail.gmail.com>
 <CACJufxGW0Uq2Xx2NLexYR410pvLx2+QKBvBjgM+5qHsqvT5BFQ@mail.gmail.com>
 <CAK98qZ3CvSfKS5yV3FAtOpWhbWHkB5aFWrngV_wYnwUbmHF4SQ@mail.gmail.com>
 <CACJufxG34m9BGnfD9RD5OEohkV3Oh-+7Xf=3epXyHfQj4DPiOw@mail.gmail.com>
 <CAK98qZ35eF+9MZuqR4HNrmebyBFdNiNLiLZHpQPB7S7OUk-DDQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/3EF2F248-1881-4764-96D7-CC0957AC68F7%40gmail.com>
Precedence: bulk


--Apple-Mail=_E499A277-B31F-4FBB-A29B-55313104CB31
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

>=20
> Best,
> Alex
> =
<v14-0002-Allow-Generic-Type-Subscripting-to-Accept-Dot-No.patch><v14-0003=
-Export-jsonPathFromParseResult.patch><v14-0001-Allow-transformation-of-on=
ly-a-sublist-of-subscr.patch><v14-0005-Implement-read-only-dot-notation-fo=
r-jsonb.patch><v14-0007-Implement-jsonb-wildcard-member-accessor.patch><v1=
4-0006-Implement-Jsonb-subscripting-with-slicing.patch><v14-0004-Extract-c=
oerce_jsonpath_subscript.patch>


I am trying to split different topics to different email to keep every =
issue to be focused.

I also have a suggestion.

If I do:

```
=E2=80=94 s1
select (t.data)['con']['a'][1]['b']['c']['d'] from test_jsonb_types t;

=E2=80=94s2
select (t.data).con.a[1].b['c'].d from test_jsonb_types t;
```

The two statements are actually identical. But they generate quite =
different rewritten query trees. S1=E2=80=99s rewritten tree is much =
simpler than s2=E2=80=99s. However, their plan trees are the same.

I think we can convert string indirections to indices indirection first =
before transform indirections, so that the two SQLs will generate the =
same rewritten query tree, which would simplify the code logic of =
rewriting query tree and the planner.


Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/


--Apple-Mail=_E499A277-B31F-4FBB-A29B-55313104CB31
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: =
after-white-space;"><div><blockquote type=3D"cite"><div><div =
dir=3D"ltr"><div class=3D"gmail_quote =
gmail_quote_container"><div><br></div><div>Best,</div><div>Alex</div></div=
></div>
<span =
id=3D"cid:f_mes0fgb51">&lt;v14-0002-Allow-Generic-Type-Subscripting-to-Acc=
ept-Dot-No.patch&gt;</span><span =
id=3D"cid:f_mes0fgb72">&lt;v14-0003-Export-jsonPathFromParseResult.patch&g=
t;</span><span =
id=3D"cid:f_mes0fgaz0">&lt;v14-0001-Allow-transformation-of-only-a-sublist=
-of-subscr.patch&gt;</span><span =
id=3D"cid:f_mes0fgbb4">&lt;v14-0005-Implement-read-only-dot-notation-for-j=
sonb.patch&gt;</span><span =
id=3D"cid:f_mes0fgbf6">&lt;v14-0007-Implement-jsonb-wildcard-member-access=
or.patch&gt;</span><span =
id=3D"cid:f_mes0fgbd5">&lt;v14-0006-Implement-Jsonb-subscripting-with-slic=
ing.patch&gt;</span><span =
id=3D"cid:f_mes0fgb93">&lt;v14-0004-Extract-coerce_jsonpath_subscript.patc=
h&gt;</span></div></blockquote></div><div><br></div><div>I am trying to =
split different topics to different email to keep every issue to be =
focused.</div><div><br></div><div>I also have a =
suggestion.</div><div><br></div><div>If I =
do:</div><div><br></div><div>```</div><div>=E2=80=94 s1</div><div>select =
(t.data)['con']['a'][1]['b']['c']['d'] from test_jsonb_types =
t;</div><div><br></div><div>=E2=80=94s2</div><div>select =
(t.data).con.a[1].b['c'].d from test_jsonb_types =
t;</div><div>```</div><div><br></div><div>The two statements are =
actually identical. But they generate quite different rewritten query =
trees. S1=E2=80=99s rewritten tree is much simpler than s2=E2=80=99s. =
However, their plan trees are the same.</div><div><br></div><div>I think =
we can convert string indirections to indices indirection first before =
transform indirections, so that the two SQLs will generate the same =
rewritten query tree, which would simplify the code logic of rewriting =
query tree and the planner.</div><div><br></div><div><br></div>Best =
regards,<br><div>
<div dir=3D"auto" style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, =
0); letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none; overflow-wrap: =
break-word; -webkit-nbsp-mode: space; line-break: =
after-white-space;"><div>--</div><div>Chao Li (Evan)<br>HighGo Software =
Co., Ltd.<br>https://www.highgo.com/</div><div><br></div></div><br =
class=3D"Apple-interchange-newline"><br =
class=3D"Apple-interchange-newline">

</div>
<br></body></html>=

--Apple-Mail=_E499A277-B31F-4FBB-A29B-55313104CB31--