Re: SQL:2023 JSON simplified accessor support

Looks like patch files need rebase.

> On Sep 20, 2025, at 04:40, Alexandra Wang <alexandra(dot)wang(dot)oss(at)gmail(dot)com> wrote:
>
> Hi there,
>
> I've attached v20. It has the following changes:
>
> 1. New 0001: It adds test coverage for single-argument functions and
> casting for jsonb expressions. This ensures that the relevant behavior
> changes become visible in 0005 when field access via dot-notation is
> introduced.
>
> Specifically, once member access through dot-notation is enabled for
> jsonb, we can no longer write single-argument functions (including
> casts) in dot form for jsonb. For example:
>
> Before 0005:
>
> select ('{"a":1}'::jsonb).jsonb_typeof;
> jsonb_typeof
> --------------
> object
> (1 row)
>
> select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb).name;
> name
> --------------------------------------
> [{"name": "alice"}, {"name": "bob"}]
> (1 row)
>
> After 0005:
>
> select ('{"a":1}'::jsonb).jsonb_typeof;
> jsonb_typeof
> --------------
>
> (1 row)
>
> select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb).name;
> name
> ------------------
> ["alice", "bob"]
> (1 row)
>
> In the meanwhile, these functions still return correct results through
> standard syntax:
>
> test=# select jsonb_typeof(('{"a":1}'::jsonb));
> jsonb_typeof
> --------------
> object
> (1 row)
> test=# select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb)::name;
> name
> --------------------------------------
> [{"name": "alice"}, {"name": "bob"}]
> (1 row)
>
> I don't consider this behavior change a major issue, because the
> dot-form for single-argument functions is not standard SQL and seems
> to be PostgreSQL-specific. Still, it's worth highlighting here so
> users aren't surprised.
>
> 2. Refactored 0002: It combines and refactors v19-0001 and v19-0002.
> Instead of changing the existing transform() callback in
> SubscriptRoutines, it now introduces an additional callback,
> transform_partial(). This alternative transform method, used by jsonb,
> is more flexible: it accepts a wider range of indirection node types
> and can transform only a prefix of the indirection list. This avoids
> breaking compatibility for arrays, hstore, and external data types
> that supports subscripting.
>
> 3. 0003 and 0004 stay unchanged. They are both small and can be squashed
> into 0005. I leave them as-is for now for easier review.
>
> 4. Added two additional tests in 0005 for assignments using jsonb
> dot-notation, showing explicitly that assignment is not yet supported.
>
> 5. Removed 0006 (array slicing) and 0007 (wildcard) from the previous
> versions, as they need additional work. My immediate goal is to first
> reach consensus on the dot-notation implementation.
>
> Best,
> Alex
>
> <v20-0005-Implement-read-only-dot-notation-for-jsonb.patch><v20-0004-Extract-coerce_jsonpath_subscript.patch><v20-0001-Add-test-coverage-for-indirection-transformation.patch><v20-0002-Add-an-alternative-transform-function-in-Subscri.patch><v20-0003-Export-jsonPathFromParseResult.patch>

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