Re: remaining sql/json patches

On Tue, Jul 18, 2023 at 5:11 PM Amit Langote <amitlangote09(at)gmail(dot)com>
wrote:
>
> Hi,
>
> On Mon, Jul 17, 2023 at 4:14 PM Erik Rijkers <er(at)xs4all(dot)nl> wrote:
> > Op 7/17/23 om 07:00 schreef jian he:
> > > hi.
> > > seems there is no explanation about, json_api_common_syntax in
> > > functions-json.html
> > >
> > > I can get json_query full synopsis from functions-json.html as
follows:
> > > json_query ( context_item, path_expression [ PASSING { value AS
> > > varname } [, ...]] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8
> > > ] ] ] [ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ]
> > > WRAPPER ] [ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ] [ { ERROR |
> > > NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT expression } ON EMPTY ]
> > > [ { ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT expression }
> > > ON ERROR ])
> > >
> > > seems doesn't have a full synopsis for json_table? only partial one
> > > by one explanation.
>
> I looked through the history of the docs portion of the patch and it
> looks like the synopsis for JSON_TABLE(...) used to be there but was
> taken out during one of the doc reworks [1].
>
> I've added it back in the patch as I agree that it would help to have
> it. Though, I am not totally sure where I've put it is the right
> place for it. JSON_TABLE() is a beast that won't fit into the table
> that JSON_QUERY() et al are in, so maybe that's how it will have to
> be? I have no better idea.
>
> >

attached screenshot render json_table syntax almost plain html. It looks
fine.
based on syntax, then I am kind of confused with following 2 cases:
--1
SELECT * FROM JSON_TABLE(jsonb '1', '$'
COLUMNS (a int PATH 'strict $.a' default 1 ON EMPTY default 2 on
error)
ERROR ON ERROR) jt;

--2
SELECT * FROM JSON_TABLE(jsonb '1', '$'
COLUMNS (a int PATH 'strict $.a' default 1 ON EMPTY default 2 on
error)) jt;

the first one should yield syntax error?
[image: json_table_v8.png]