Re: SQL:2023 JSON simplified accessor support

Hi Peter,

Thank you so much for helping!

On Mon, Sep 16, 2024 at 12:44 PM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>
> On 29.08.24 18:33, Alexandra Wang wrote:
> > I’ve implemented the member and array accessors and attached two
> > alternative patches:
> >
> > 1. v1-0001-Add-JSON-JSONB-simplified-accessor.patch: This patch
> > enables dot access to JSON object fields and subscript access to
> > indexed JSON array elements by converting "." and "[]" indirection
> > into a JSON_QUERY JsonFuncExpr node.
> >
> > 2. v2-0001-Transform-JSON-dot-access-to-arrow-operator.txt: This
> > alternative patch implements dot access to JSON object fields by
> > transforming the "." indirection into a "->" operator.
> >
> > The upside of the v1 patch is that it strictly aligns with the SQL
> > standard, which specifies that the simplified access is equivalent to:
> >
> > JSON_QUERY (VEP, 'lax $.JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON
> > EMPTY NULL ON ERROR)
> >
> > However, the performance of JSON_QUERY might be suboptimal due to
> > function call overhead. Therefore, I implemented the v2 alternative
> > using the "->" operator.
> Using the operator approach would also allow taking advantage of
> optimizations such as
> <https://www.postgresql.org/message-id/flat/CAKU4AWoqAVya6PBhn%2BBCbFaBMt3z-2%3Di5fKO3bW%3D6HPhbid2Dw%40mail.gmail.com>.

OK, that makes sense.

> > There is some uncertainty about the semantics of conditional array
> > wrappers. Currently, there is at least one subtle difference between
> > the "->" operator and JSON_QUERY, as shown:
>
> That JSON_QUERY bug has been fixed.
>
> I suggest you rebase both of your patches over this, just to double
> check everything. But then I think you can drop the v1 patch and just
> submit a new version of v2.

Done. I rebased both patches and confirmed they have the same test
outputs. I attached v3, which also adds JSON subscript support on top
of v2.

> The patch should eventually contain some documentation. It might be
> good starting to look for a good spot where to put that documentation.
> It might be either near the json types documentation or near the general
> qualified identifier syntax, not sure.

Right, I’m not sure either. A third option, I think, would be to
include it in the JSON Functions and Operators section [1].

[1] https://www.postgresql.org/docs/devel/functions-json.html

Best,
Alex