Re: remaining sql/json patches

On Fri, Mar 29, 2024 at 11:20 AM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
>
> +<synopsis>
> +JSON_TABLE (
> + <replaceable>context_item</replaceable>,
> <replaceable>path_expression</replaceable> <optional> AS
> <replaceable>json_path_name</replaceable> </optional> <optional>
> PASSING { <replaceable>value</replaceable> AS
> <replaceable>varname</replaceable> } <optional>, ...</optional>
> </optional>
> + COLUMNS ( <replaceable
> class="parameter">json_table_column</replaceable> <optional>,
> ...</optional> )
> + <optional> { <literal>ERROR</literal> | <literal>EMPTY</literal>
> } <literal>ON ERROR</literal> </optional>
> +)
> top level (not in the COLUMN clause) also allows
> <literal>NULL</literal> <literal>ON ERROR</literal>.
>
we can also specify <literal>DEFAULT expression</literal> <literal>ON
ERROR</literal>.
like:
SELECT * FROM JSON_TABLE(jsonb'"1.23"', 'strict $.a' COLUMNS (js2 int
PATH '$') default '1' on error);

+ <varlistentry>
+ <term>
+ <replaceable>name</replaceable> <replaceable>type</replaceable>
<literal>FORMAT JSON</literal> <optional>ENCODING
<literal>UTF8</literal></optional>
+ <optional> <literal>PATH</literal>
<replaceable>json_path_specification</replaceable> </optional>
+ </term>
+ <listitem>
+ <para>
+ Inserts a composite SQL/JSON item into the output row.
+ </para>
+ <para>
+ The provided <literal>PATH</literal> expression is evaluated and
+ the column is filled with the produced SQL/JSON item. If the
+ <literal>PATH</literal> expression is omitted, path expression
+ <literal>$.<replaceable>name</replaceable></literal> is used,
+ where <replaceable>name</replaceable> is the provided column name.
+ In this case, the column name must correspond to one of the
+ keys within the SQL/JSON item produced by the row pattern.
+ </para>
+ <para>
+ Optionally, you can specify <literal>WRAPPER</literal>,
+ <literal>QUOTES</literal> clauses to format the output and
+ <literal>ON EMPTY</literal> and <literal>ON ERROR</literal> to handle
+ those scenarios appropriately.
+ </para>

Similarly, I am not sure of the description of "composite SQL/JSON item".
by observing the following 3 examples:
SELECT * FROM JSON_TABLE(jsonb'{"a": "z"}', '$.a' COLUMNS (js2 text
format json PATH '$' omit quotes));
SELECT * FROM JSON_TABLE(jsonb'{"a": "z"}', '$.a' COLUMNS (js2 text
format json PATH '$'));
SELECT * FROM JSON_TABLE(jsonb'{"a": "z"}', '$.a' COLUMNS (js2 text PATH '$'));

i think, FORMAT JSON specification means that,
if your specified type is text or varchar related AND didn't specify
quotes behavior
then FORMAT JSON produced output can be casted to json data type.
so FORMAT JSON seems not related to array and records data type.

also the last para can be:
+ <para>
+ Optionally, you can specify <literal>WRAPPER</literal>,
+ <literal>QUOTES</literal> clauses to format the output and
+ <literal>ON EMPTY</literal> and <literal>ON ERROR</literal> to handle
+ those missing values and structural errors, respectively.
+ </para>

+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("only string constants are supported in JSON_TABLE"
+ " path specification"),
should be:

+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("only string constants are supported in JSON_TABLE path
specification"),

+ <varlistentry>
+ <term>
+ <literal>AS</literal> <replaceable>json_path_name</replaceable>
+ </term>
+ <listitem>
+
+ <para>
+ The optional <replaceable>json_path_name</replaceable> serves as an
+ identifier of the provided
<replaceable>json_path_specification</replaceable>.
+ The path name must be unique and distinct from the column names.
+ When using the <literal>PLAN</literal> clause, you must specify the names
+ for all the paths, including the row pattern. Each path name can appear in
+ the <literal>PLAN</literal> clause only once.
+ </para>
+ </listitem>
+ </varlistentry>
as of v46, we don't have PLAN clause.
also "must be unique and distinct from the column names." seems incorrect.
for example:
SELECT * FROM JSON_TABLE(jsonb'"1.23"', '$.a' as js2 COLUMNS (js2 int
PATH '$'));