Re: remaining sql/json patches

On Tue, Apr 2, 2024 at 9:57 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>
> Please let me know if you have further comments on 0001. I'd like to
> get that in before spending more energy on 0002.
>

hi. some issues with the doc.
i think, some of the "path expression" can be replaced by
"<replaceable>path_expression</replaceable>".
maybe not all of them.

+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal><replaceable>context_item</replaceable>,
<replaceable>path_expression</replaceable> <optional>
<literal>AS</literal> <replaceable>json_path_name</replaceable>
</optional> <optional> <literal>PASSING</literal> {
<replaceable>value</replaceable> <literal>AS</literal>
<replaceable>varname</replaceable> } <optional>,
...</optional></optional></literal>
+ </term>
+ <listitem>
+ <para>
+ The input data to query, the JSON path expression defining the query,
+ and an optional <literal>PASSING</literal> clause, which can provide data
+ values to the <replaceable>path_expression</replaceable>.
+ The result of the input data
+ evaluation is called the <firstterm>row pattern</firstterm>. The row
+ pattern is used as the source for row values in the constructed view.
+ </para>
+ </listitem>
+ </varlistentry>

maybe
change this part "The input data to query, the JSON path expression
defining the query,"
to
`
<replaceable>context_item</replaceable> is the input data to query,
<replaceable>path_expression</replaceable> is the JSON path expression
defining the query,
`

+ <para>
+ Specifying <literal>FORMAT JSON</literal> makes it explcit that you
+ expect that the value to be a valid <type>json</type> object.
+ </para>
"explcit" change to "explicit", or should it be "explicitly"?
also FORMAT JSON can be override by OMIT QUOTES.
SELECT sub.* FROM JSON_TABLE('{"a":{"z1": "a"}}', '$.a' COLUMNS(xx
TEXT format json path '$.z1' omit quotes))sub;
it return not double quoted literal 'a', which cannot be a valid json.

create or replace FUNCTION test_format_json() returns table (thetype
text, is_ok bool) AS $$
declare
part1_sql text := $sql$SELECT sub.* FROM JSON_TABLE('{"a":{"z1":
"a"}}', '$.a' COLUMNS(xx $sql$;
part2_sql text := $sql$ format json path '$.z1' omit quotes))sub $sql$;
run_status bool := true;
r record;
fin record;
BEGIN
for r in
select format_type(oid, -1) as aa
from pg_type where typtype = 'b' and typarray != 0 and
typnamespace = 11 and typnotnull is false
loop
begin
-- raise notice '%',CONCAT_WS(' ', part1_sql, r.aa, part2_sql);
-- raise notice 'r.aa %', r.aa;
run_status := true;
execute CONCAT_WS(' ', part1_sql, r.aa, part2_sql) into fin;
return query select r.aa, run_status;
exception when others then
begin
run_status := false;
return query select r.aa, run_status;
end;
end;
end loop;
END;
$$ language plpgsql;
create table sss_1 as select * from test_format_json();
select * from sss_1 where is_ok is true;

use the above query, I've figure out that FORMAT JSON can apply to the
following types:
bytea
name
text
json
bpchar
character varying
jsonb
and these type's customized domain type.

overall, the idea is that:
Specifying <literal>FORMAT JSON</literal> makes it explicitly that you
expect that the value to be a valid <type>json</type> object.
<literal>FORMAT JSON</literal> can be overridden by OMIT QUOTES
specification, which can make the return value not a valid
<type>json</type>.
<literal>FORMAT JSON</literal> can only work with certain kinds of
data types.
-----------------------------------------------------------------------------------------------
+ <para>
+ Optionally, you can add <literal>ON ERROR</literal> clause to define
+ error behavior.
+ </para>
I think "error behavior" may refer to "what kind of error message it will omit"
but here, it's about what to do when an error happens.
so I guess it's misleading.

maybe we can explain it similar to json_exist.
+ <para>
+ Optionally, you can add <literal>ON ERROR</literal> clause to define
+ the behavior if an error occurs.
+ </para>

+ <para>
+ The specified <parameter>type</parameter> should have a cast from the
+ <type>boolean</type>.
+ </para>
should be
+ <para>
+ The specified <replaceable>type</replaceable> should have a cast from the
+ <type>boolean</type>.
+ </para>

+ <para>
+ Inserts a SQL/JSON value into the output row.
+ </para>
maybe
+ <para>
+ Inserts a value that the data type is
<replaceable>type</replaceable> into the output row.
+ </para>

+ <para>
+ Inserts a boolean item into each output row.
+ </para>
maybe changed to:
+ <para>
+ Inserts a value that the data type is
<replaceable>type</replaceable> into the output row.
+ </para>

"name type EXISTS" branch mentioned: "The specified type should have a
cast from the boolean."
but "name type [FORMAT JSON [ENCODING UTF8]] [ PATH path_expression ]"
never mentioned the "type"parameter.
maybe add one para, something like:
"after apply path_expression, the yield value cannot be coerce to
<replaceable>type</replaceable> it will return null"