Re: SQL:2023 JSON simplified accessor support

Hi Chao,

On Thu, Aug 28, 2025 at 8:42 PM Chao Li <li.evan.chao@gmail.com> wrote:

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

Sure!

On Thu, Aug 28, 2025 at 8:42 PM Chao Li <li.evan.chao@gmail.com> wrote:

> I also have a suggestion.
>
> If I do:
>
> ```
> — s1
> select (t.data)['con']['a'][1]['b']['c']['d'] from test_jsonb_types t;
>
> —s2
> 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’s rewritten tree is much simpler than
> s2’s. However, their plan trees are the same.
>

The above two statements are NOT identical. Specifically, dot-notation
(e.g., .con) and pre-standard jsonb subscripting (e.g., ['con']) are
NOT semantically the same.

Here's an example:

-- setup
create table t (jb jsonb);
insert into t SELECT '{"con": 1}'::jsonb;
insert into t SELECT '[{"con": 1}, {"con": {"a": 2}}]'::jsonb;

-- queries
test=# select (t.jb).con from t;
      con
---------------
 1
 [1, {"a": 2}]
(2 rows)

test=# select (t.jb)['con'] from t;
 jb
----
 1

(2 rows)

As you can see, dot-notation returns different results from jsonb
subscripting.

As I mentioned in the previous reply:

    The SQL standard states that simplified access is equivalent to:
>     JSON_QUERY (VEP, 'lax $.JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON
> EMPTY NULL ON ERROR
> )


>     where:
>       VEP = <value expression primary>
>       JC = <JSON simplified accessor op chain>


And


> *In lax mode:*
> *— If an operation requires an SQL/JSON array but the operand is not an
> SQL/JSON array, then the operand is first “wrapped” in an SQL/JSON array
> prior to performing the operation.*
> *— If an operation requires something other than an SQL/JSON array, but
> the operand is an SQL/JSON array, then the operand is “unwrapped” by
> converting its elements into an SQL/JSON sequence prior to performing the
> operation.**— After applying the preceding resolutions to structural
> errors, if there is still a structural error , the result is an empty
> SQL/JSON sequence.*


The example query demonstrates the second point above. The
dot-notation attempts to access a member field (."con") of a JSON
object, while the operand is a JSON array ([{"con": 1}, {"con": {"a":
2}}]). In "lax" mode, the operand is "unwrapped" into a JSON sequence
(two elements: {"con": 1} and {"con": {"a": 2}}), and the member field
access is performed on each element. The multiple results are then
wrapped into a JSON array ([1, {"a": 2}]) due to WITH CONDITIONAL
ARRAY WRAPPER. I’ve already explained what "ARRAY WRAPPER" does in my
previous reply, so I won't repeat it here.

Best,
Alex