public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: Alexandra Wang <[email protected]>
Cc: Chao Li <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Nikita Glukhov <[email protected]>
Cc: Nikita Malakhov <[email protected]>
Cc: Vik Fearing <[email protected]>
Cc: Mark Dilger <[email protected]>
Cc: Matheus Alcantara <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: David E. Wheeler <[email protected]>
Subject: Re: SQL:2023 JSON simplified accessor support
Date: Wed, 10 Dec 2025 23:11:33 +0800
Message-ID: <CACJufxGqZ6NvyY5CL2zK07ebDFyADZrAi2G_P3V=9Aau5ifS0g@mail.gmail.com> (raw)
In-Reply-To: <CAK98qZ0R1ufQ-uQq8DxOPnmfacrzxBKy1phbUt8TA6+EDj9+PA@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CAK98qZ2QGcyJrJAFv9wjY6S8yP9dUVnmG9Gb4OXuzuMMuM1Z5Q@mail.gmail.com>
<[email protected]>
<[email protected]>
<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>
<[email protected]>
<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>
<[email protected]>
<[email protected]>
<[email protected]>
<CAK98qZ0SUBjETvUT31U01pSKYCuQPznCtb2kf3-jB5+Dh2V03w@mail.gmail.com>
<[email protected]>
<CAK98qZ1GwW_dCHSi7wMV_H+eQ9qdWajUNXmntufHF4Md=xRSsw@mail.gmail.com>
<[email protected]>
<CAK98qZ0v5dkgMZNK-ogC8Cio-6OPSnNVM-4BWa=Jh=JnvZpB-Q@mail.gmail.com>
<CAK98qZ2nezKeQFqPV9GX80mDiLqS2Fh6MRBGz6ox_HkQF86asA@mail.gmail.com>
<CAK98qZ2EhKC=Z23jNbMX=aGPGi9+n42a8Eiz1rKYmF388UCHUQ@mail.gmail.com>
<[email protected]>
<CAK98qZ3fATHtD8n471AjniZ2KHFE59MfJzjo+P0SR3_oUT8Jbw@mail.gmail.com>
<[email protected]>
<CAK98qZ0R1ufQ-uQq8DxOPnmfacrzxBKy1phbUt8TA6+EDj9+PA@mail.gmail.com>
On Wed, Sep 24, 2025 at 9:06 AM Alexandra Wang
<[email protected]> wrote:
>
> The rest of your feedback I've made changes accordingly as you suggested.
>
> Best,
> Alex
>
hi.
+ <para>
+ PostgreSQL implements the JSON simplified accessor as specified in SQL:2023.
not sure we need to decorated SQL:2023 as <acronym>SQL:2023</acronym>,
but PostgreSQL should be decorated as <productname>PostgreSQL</productname>.
I believe
SELECT * FROM users WHERE profile.preferences.theme = '"dark"';
should be
SELECT * FROM users WHERE (profile).preferences.theme = '"dark"';
+INSERT INTO test_table VALUES
+ ('{"brightness": 80}'), -- Object case
+ ('[{"brightness": 45}, {"brightness": 90}]'); -- Array case
comments no need, i think.
+ <sect3 id="jsonb-access-method-comparison">
+ <title>Comparison of JSON Access Methods</title>
+ <para>
I am worried that the wording "Access Methods" would be confused with "Table
Access Methods".
+-- Comparison with other access methods (NOT equivalent - different semantics):
+SELECT json_col['address']['city']; -- Subscripting
+SELECT json_col->'address'->'city'; -- Operator
+SELECT json_col.address.city; -- Simplified accessor
(different behavior)
+</programlisting>
"access methods" would be confusing as mentioned above.
also these SQL query with SELECT is wrong? since no FROM clause.
again, I think the last one should be
SELECT (json_col).address.city;
we generally expect </programlisting> content can be passed to psql.
+-- Different behaviors:
+SELECT data.brightness FROM test_table; -- Simplified accessor
+-- Results: 80, [45, 90] (array elements unwrapped, results wrapped)
Again, here I believe, it should be
SELECT (data).brightness FROM test_table;
also the Results should be two rows, so this needs to change.
+<programlisting>
+-- Setup data
+INSERT INTO test_table VALUES ('{"weather": "sunny", "temperature": "72F"}');
+
+-- Different behaviors when accessing [0] on a non-array value:
+SELECT data[0] FROM test_table; -- Simplified
accessor (lax mode, if dots present elsewhere)
+-- Result: {"weather": "sunny", "temperature": "72F"} (object
wrapped as array, [0] returns entire object)
+
there is no GUC about json lex mode or not, we can only specify it via jsonpath.
but in the HEAD
select (jsonb '{"weather": "sunny", "temperature": "72F"}')[0];
return NULL.
so I am confused with the above comment.
+<programlisting>
+-- All parts use simplified accessor (standard behavior)
+SELECT data.location.coordinates.latitude FROM table; -- Good
+SELECT data.repertoire[0].title FROM table; -- Good
+SELECT data.users[1].profile.email FROM table; -- Good
+</programlisting>
+ </para>
TABLE is a reserved word, ``SELECT FROM table;`` will result in syntax error.
That means most of the examples in
<sect3 id="jsonb-accessor-best-practices"> needs more polishing.
+ <sect3 id="jsonb-accessor-best-practices">
+ <title>Best Practices: Avoid Mixing Access Methods</title>
+ <para>
+ <emphasis>Important:</emphasis> Do not mix SQL:2023 simplified
accessor syntax
+ with pre-standard subscripting syntax in the same accessor chain. These
+ methods have subtly different semantics and are not
interchangeable aliases.
+ Mixing them can lead to confusion and code that is difficult to understand.
+ </para>
If we want users not to confuse SQL:2023 simplified accessor with pre-standard
subscripting syntax, we can wrap this important information in a <note> tag.
some changes are reflected on the attached file, but some I don't know
how to change,
so I didn't do it.
some sgml lines are way too line, I have split them into separate lines.
--
jian
https://www.enterprisedb.com
Attachments:
[application/octet-stream] v22-doc_change.nocfbot (7.3K, ../CACJufxGqZ6NvyY5CL2zK07ebDFyADZrAi2G_P3V=9Aau5ifS0g@mail.gmail.com/2-v22-doc_change.nocfbot)
download
view thread (68+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL:2023 JSON simplified accessor support
In-Reply-To: <CACJufxGqZ6NvyY5CL2zK07ebDFyADZrAi2G_P3V=9Aau5ifS0g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox