Horton

Re: SQL:2023 JSON simplified accessor support

Alexandra Wang <alexandra.wang.oss@gmail.com>

View thread
From: Alexandra Wang <alexandra.wang.oss@gmail.com>
To: Peter Eisentraut <peter@eisentraut.org>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Andrew Dunstan <andrew.dunstan@enterprisedb.com>
Date: 2024-09-23T19:22:20Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add test coverage for indirection transformation

  2. Fix typo in comment

  3. Implementation of subscripting for jsonb

Attachments

Hi Peter,

Thank you so much for helping!

On Mon, Sep 16, 2024 at 12:44 PM Peter Eisentraut <peter@eisentraut.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