Re: remaining sql/json patches

Hi,

On Sun, Jul 23, 2023 at 5:17 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
> hi
> based on v10*.patch. questions/ideas about the doc.

Thanks for taking a look.

> > json_exists ( context_item, path_expression [ PASSING { value AS varname } [, ...]] [ RETURNING data_type ] [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ])
> > Returns true if the SQL/JSON path_expression applied to the context_item using the values yields any items. The ON ERROR clause specifies what is returned if an error occurs. Note that if the path_expression is strict, an error is generated if it yields no items. The default value is UNKNOWN which causes a NULL result.
>
> only SELECT JSON_EXISTS(NULL::jsonb, '$'); will cause a null result.
> In lex mode, if yield no items return false, no error will return,
> even error on error.
> Only case error will happen, strict mode error on error. (select
> json_exists(jsonb '{"a": [1,2,3]}', 'strict $.b' error on error)
>
> so I came up with the following:
> Returns true if the SQL/JSON path_expression applied to the
> context_item using the values yields any items. The ON ERROR clause
> specifies what is returned if an error occurs, if not specified, the
> default value is false when it yields no items.
> Note that if the path_expression is strict, ERROR ON ERROR specified,
> an error is generated if it yields no items.

OK, will change the text to say that the default ON ERROR behavior is
to return false.

> --------------------------------------------------------------------------------------------------
> /* --first branch of json_table_column spec.
>
> name type [ PATH json_path_specification ]
> [ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY
> ] WRAPPER ]
> [ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
> [ { ERROR | NULL | DEFAULT expression } ON EMPTY ]
> [ { ERROR | NULL | DEFAULT expression } ON ERROR ]
> */
> I am not sure what " [ ON SCALAR STRING ]" means. There is no test on this.

ON SCALAR STRING is just syntactic sugar. KEEP/OMIT QUOTES specifies
the behavior when the result of JSON_QUERY() is a JSON scalar value.

> i wonder how to achieve the following query with json_table:
> select json_query(jsonb '"world"', '$' returning text keep quotes) ;
>
> the following case will fail.
> SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH
> '$' keep quotes ON SCALAR STRING ));
> ERROR: cannot use OMIT QUOTES clause with scalar columns
> LINE 1: ...T * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text ...
> ^
> error should be ERROR: cannot use KEEP QUOTES clause with scalar columns?
> LINE1 should be: SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS
> (item text ...

Hmm, yes, I think the code that produces the error is not trying hard
enough to figure out the actually specified QUOTES clause. Fixed and
added new tests.

> --------------------------------------------------------------------------------
> quote from json_query:
> > This function must return a JSON string, so if the path expression returns multiple SQL/JSON items, you must wrap the result using the
> > WITH WRAPPER clause.
>
> I think the final result will be: if the RETURNING clause is not
> specified, then the returned data type is jsonb. if multiple SQL/JSON
> items returned, if not specified WITH WRAPPER, null will be returned.

I suppose you mean the following case:

SELECT JSON_QUERY(jsonb '[1,2]', '$[*]');
json_query
------------

(1 row)

which with ERROR ON ERROR gives:

SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
ERROR: JSON path expression in JSON_QUERY should return singleton
item without wrapper
HINT: Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.

The default return value for JSON_QUERY when an error occurs during
path expression evaluation is NULL. I don't think that it needs to be
mentioned separately.

> ------------------------------------------------------------------------------------
> quote from json_query:
> > The ON ERROR and ON EMPTY clauses have similar semantics to those clauses for json_value.
> quote from json_table:
> > These clauses have the same syntax and semantics as for json_value and json_query.
>
> it would be better in json_value syntax explicit mention: if not
> explicitly mentioned, what will happen when on error, on empty
> happened ?

OK, I've improved the text here.

> -------------------------------------------------------------------------------------
> > You can have only one ordinality column per table
> but the regress test shows that you can have more than one ordinality column.

Hmm, I am not sure why the code's allowing that. Anyway, for the lack
any historical notes on why it should be allowed, I've fixed the code
to allow only one ordinality columns and modified the tests.

> ----------------------------------------------------------------------------
> similar to here
> https://git.postgresql.org/cgit/postgresql.git/tree/src/test/regress/expected/sqljson.out#n804
> Maybe in file src/test/regress/sql/jsonb_sqljson.sql line 349, you can
> also create a table first. insert corner case data.
> then split the very wide select query (more than 26 columns) into 4
> small queries, better to view the expected result on the web.

OK, done.

I'm still finding things to fix here and there, but here's what I have
got so far.

--
Thanks, Amit Langote
EDB: http://www.enterprisedb.com