Re: SQL:2023 JSON simplified accessor support

On Thu, Aug 29, 2024, 19:33 Alexandra Wang <alexandra(dot)wang(dot)oss(at)gmail(dot)com>
wrote:

> Hello Hackers,
>
> I’ve attached a patch to start adding SQL:2023 JSON simplified
> accessor support. This allows accessing JSON or JSONB fields using dot
> notation (e.g., colname.field.field...), similar to composite types.
>

Interesting, I'm too late, just want to notice that is not just JSON syntax.

It is a chance to give navigation a PostgreSQL carrier: type-directed path
steps, SQL semantics, planner visibility, index support and explainable
execution.

>
> Currently, PostgreSQL uses nonstandard syntax like colname->x->y for
> JSON and JSONB, and colname['blah'] for JSONB. These existing syntaxes
> predate the standard. Oracle already supports the standard dot
> notation syntax [1].
>
> The full specification for the JSON simplified accessor format is as
> follows:
>
> <JSON simplified accessor> ::=
> <value expression primary> <JSON simplified accessor op chain>
> <JSON simplified accessor op chain> ::=
> <JSON simplified accessor op>
> | <JSON simplified accessor op chain> <JSON simplified accessor op>
> <JSON simplified accessor op> ::=
> <JSON member accessor>
> | <JSON wildcard member accessor>
> | <JSON array accessor>
> | <JSON wildcard array accessor>
> | <JSON item method>
>
> 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.
>
> 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:
>
> postgres=# select '{"a": 42}'::json->'a';
> ?column?
> ----------
> 42
> (1 row)
>
> postgres=# select json_query('{"a": 42}'::json, 'lax $.a' with
> conditional array wrapper null on empty null on error);
> json_query
> ------------
> [42]
> (1 row)
>
> JSON_QUERY encloses the JSON value 42 in brackets, which may be a bug,
> as Peter noted [2]. If there are no other semantic differences, we
> could implement simple access without using JSON_QUERY to avoid
> function call overhead.
>
> I aim to first enable standard dot notation access to JSON object
> fields. Both patches implement this, and I’m also open to alternative
> approaches.
>
> For subscripting access to jsonb array elements, jsonb already
> supports this via the subscripting handler interface. In the v1 patch,
> I added json support using JSON_QUERY, but I can easily adapt this for
> the v2 patch using the -> operator. I did not leverage the
> subscripting handler interface for json because implementing the
> fetch/assign functions for json seems challenging for plain text. Let
> me know if you have a different approach in mind.
>
> Finally, I have not implemented wildcard or item method accessors yet
> and would appreciate input on their necessity.
>
> [1]
> https://docs.oracle.com/en/database/oracle/oracle-database/21/adjsn/simple-dot-notation-access-to-json-data.html#GUID-7249417B-A337-4854-8040-192D5CEFD576
> [2]
> https://www.postgresql.org/message-id/8022e067-818b-45d3-8fab-6e0d94d03626@eisentraut.org
>