SQL:2023 JSON simplified accessor support

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.

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