Re: SQL:2023 JSON simplified accessor support

Hi Peter,

Thanks for the review!

I've attached v23.

0001: Implement read-only dot notation for hstore.
0002: a small refactor that can be squashed with 0003.
0003: Implement read-only dot notation for jsonb.

On Thu, Jan 8, 2026 at 2:03 AM Peter Eisentraut <peter(at)eisentraut(dot)org>
wrote:
> Another problem is that the existing jsonb array subscripting is
> different in some detail from the jsonpath semantics. Some examples
> taken from the tests in the patch:

In v23, I addressed (or attempted to address) all of your comments
except for this one. I don't know what to do with the semantic
differences between the existing jsonb array subscripting and
jsonpath. I intended not to change existing behavior, but I agree with
you that if we keep the existing behavior, then the new feature would
not fully comply with the standard. I will investigate further and
continue this discussion.

Below are detailed replies for things that I think I've resolved.

On Thu, Jan 8, 2026 at 2:03 AM Peter Eisentraut <peter(at)eisentraut(dot)org>
wrote:
> I think we need to find a way to take smaller incremental steps on
> this whole feature set. For example, earlier in the thread, a patch
> was proposed to make the hstore type use this. The hstore type is a
> much simpler situation, and it's an extension, so this would be a good
> way to build out the API and work out issues related to ambiguities
> about field names vs function names and so on. And then build the
> jsonb functionality on top of that, while also having worked out the
> jsonpath semantics issues.

I added the dot-notation implementation for hstore in patch 0001. It
serves as an example that uses the SubscriptTransformPartial()
function we added in the subscripting API. I've made single-argument
function calls take precedence over dot-notation key access. However,
since hstore does not have a nested structure which requires chained
access, we have to leave related API changes to patch 0003, where we
implement jsonb dot notation.

On Thu, Jan 8, 2026 at 2:03 AM Peter Eisentraut <peter(at)eisentraut(dot)org>
wrote:
> On 24.09.25 03:05, Alexandra Wang wrote:
> > Thanks for reviewing. I'm glad you like the new approach of
> > introducing "transform_partial". I've attached v22, which addresses
> > some of your feedback, and I ran pgindent again.
>
>
> This feature introduces some incompatible changes. These need to be
> called out more explicitly and documented. Or we should discuss them
> first.
>
> The problem is that
>
> (foo).bar
>
> could be a field reference or a function call. In case of ambiguity,
> the field reference interpretation is preferred. This is documented
> at
>
> https://www.postgresql.org/docs/devel/rowtypes.html#ROWTYPES-USAGE
>
> With the new feature, data types can define their own field reference
> helper functions. But the problem is that this is not resolvable at
> parse time. With row types, you can check at parse time what field
> names exist, and you can resolve between field names and function
> names. With the new feature, a type such as jsonb or hstore, because
> of their dynamic nature, would effectively claim that all names are
> possible field names, and so the function name interpretation would
> never be applicable. This would effectively kill the function call
> syntax for those types.
>
> I think we need to think this through a bit more. I don't think we
> can get away with just breaking this as is done in this patch. One
> possibility would be that the function name interpretation would be
> preferred for those types. I don't know.

For now, I made function name interpretation preferred for hstore and jsonb.

On Thu, Jan 8, 2026 at 2:03 AM Peter Eisentraut <peter(at)eisentraut(dot)org>
wrote:
> Btw., I noticed a small problem in the SQL standard text. Your commit
> message says that the simplified accessor expressions are equivalent
> to
>
> JSON_QUERY (VEP, 'lax $.JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON
> EMPTY NULL ON ERROR)
>
> but I think it should be 'lax $JC' without the period.

Fixed.

On Thu, Jan 8, 2026 at 2:03 AM Peter Eisentraut <peter(at)eisentraut(dot)org>
wrote:
> About the patch itself, I don't understand the split between patch
> 0002 (Add an alternative transform function in SubscriptRoutines) and
> the rest. Patch 0002 introduces the .transform_partial callback
> function but doesn't explain why one might want to use it. There is
> no example or test case given. The patch changes the jsonb type to
> use that new callback, but this doesn't appear to result in any change
> of behavior. This needs to be clarified. Moreover, the comment in
> the patch says that the function can handle field references (String
> nodes), but AFAICT that functionality is actually introduced in patch
> 0005.
>
> I'm confused that patch 0005 contains executor changes specific to
> jsonpath. The point of having this callback API is that this
> functionality is generalized and handled by the callback functions
> provided by the types. If this is not sufficient to achieve the
> functionality, maybe the API needs to be enhanced further.

I've now reorganized the commits. Now the API changes are in the same
commits as the use cases. I still cannot avoid the executor changes;
they are still only applicable to the jsonb type, but I've renamed
some field names to make them look generic.

On Thu, Jan 8, 2026 at 2:03 AM Peter Eisentraut <peter(at)eisentraut(dot)org>
wrote:
> I don't see the need for the refactoring in patch 0004. The new
> function doesn't seem to be used anywhere else. Was this left over
> from a previous patch version?

Fixed. I put the function back inline.

On Thu, Jan 8, 2026 at 2:03 AM Peter Eisentraut <peter(at)eisentraut(dot)org>
wrote:
> A small code style note: We don't use // comments. pgindent will
> remove them, but not in ecpg pgc files. Please fix those in your
> patch.

Fixed.

--
Alexandra Wang
EDB: https://www.enterprisedb.com