Re: SQL:2023 JSON simplified accessor support - Mailing list pgsql-hackers

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