hi
based on v10*.patch. questions/ideas about the doc.
> 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.
--------------------------------------------------------------------------------------------------
/* --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.
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 ...
--------------------------------------------------------------------------------
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.
------------------------------------------------------------------------------------
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 ?
-------------------------------------------------------------------------------------
> You can have only one ordinality column per table
but the regress test shows that you can have more than one ordinality column.
----------------------------------------------------------------------------
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.
