Re: SQL:2023 JSON simplified accessor support

On Wed, Sep 24, 2025 at 9:06 AM Alexandra Wang
<alexandra(dot)wang(dot)oss(at)gmail(dot)com> 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