Re: SQL/JSON: functions

Attached 41th version of the patches.

Changes since previous version:
  * Enabled DEFAULT clause for ON ERROR/ON EMPTY behaviors in JSON_QUERY()
  * Added RETURNING clause to JSON_EXISTS() ("side effect" of implementation
    EXISTS PATH columns in JSON_TABLE)
  * ARRAY in EMPTY ARRAY ON ERROR clause is optional now for better Oracle
    compatibility


On 17.01.2020 9:54, Pavel Stehule wrote:

> On 14. 11. 2019 v 17:46 Nikita Glukhov <n.gluhov@postgrespro.ru 
> <mailto:n.gluhov@postgrespro.ru>> wrote:
>
>     Attached 40th version of the patches.
>
>     I have added some documentation which has been simply copied from [1].
>
>     Also, I have split patches more correctly, and extracted patch #2 with common
>     SQL/JSON clauses, that are used by both constructors and query functions.
>     Patches #3 and #4 are necessary only for constructors (patch #5).
>     Patches #5 and #6 are almost independent on patch #1, which is needed only for
>     query functions.
>
>     So, patches #5, #6, #7 are independent now, but the code changes in them are
>     still conflicting.  I can rebase #6 and #7 on top of #2, if it is necessary
>     for separate review/commit.
>
>
>     [1]https://www.postgresql.org/message-id/732208d3-56c3-25a4-8f08-3be1d54ad51b%40postgrespro.ru
>
> I tested cumulative patch - sent in json_table patch.
>
> I almost satisfied by quality of this patch. There is very good 
> conformance with standard and with Oracle. Unfortunately MySQL in this 
> part of JSON support is not compatible.
>
> I found one issue, when I tested some examples from Oracle.
>
> SELECT JSON_VALUE('{a:100}', '$.a' RETURNING int) AS value;
>
> then the result was null.
>
> But it is wrong, because it should to raise a exception, because this 
> json is broken on Postgres (Postgres requires quoted attribute names)
>
> json_query has same problem
>
> postgres=# SELECT JSON_QUERY('{a:100, b:200, c:300}', '$') AS value;
> ┌───────┐
> │ value │
> ╞═══════╡
> │ ∅     │
> └───────┘
> (1 row)
>
> It should to check if input is correct json

By the standard, it is implementation-defined whether JSON parsing errors
should be caught by ON ERROR clause.

SQL/JSON query functions use "JSON API common syntax" which is a combination
of JSON context item and JSON path.  It passes context item to JSON path
engine with ALREADY PARSED flag set to False.  ALREADY PARSED flag can enable
special parsing rules.

Corresponding quotes from the standard:

10.14 <JSON API common syntax>
   <JSON API common syntax> (
      Parameter: "JSON API COMMON SYNTAX"
   ) Returns: "STATUS" and "SQL/JSON SEQUENCE"

General Rules:
...
3) General Rules of Subclause 9.39, "SQL/JSON path language: syntax and
    semantics", are applied with P as PATH SPECIFICATION, C as CONTEXT ITEM,
    False as ALREADY PARSED, and PC as PASSING CLAUSE; let ST be the STATUS
    and let SEQ be the SQL/JSON SEQUENCE returned from the application of
    those General Rules.

9.39 SQL/JSON path language: syntax and semantics

"SQL/JSON path language: syntax and semantics" [General Rules] (
   Parameter: "PATH SPECIFICATION",
   Parameter: "CONTEXT ITEM",
   Parameter: "ALREADY PARSED",
   Parameter: "PASSING CLAUSE"
) Returns: "STATUS" and "SQL/JSON SEQUENCE"

General Rules:
...

4) If ALREADY PARSED is False, then it is implementation-defined whether the
    following rules are applied:
   a) The General Rules of Subclause 9.36, "Parsing JSON text", are applied with
      JT as JSON TEXT, an implementation-defined <JSON key uniqueness constraint>
      as UNIQUENESS CONSTRAINT, and FO as FORMAT OPTION; let ST be the STATUS and
      let CISJI be the SQL/JSON ITEM returned from the application of those
      General Rules.
   b) If ST is not successful completion, then ST is returned as the STATUS of
      this application of these General Rules, and no further General Rules of
      this Subclause are applied.


I decided to apply this rules, so the parsing errors are caught now by ON ERROR
(NULL ON ERROR is by default).

postgres=# SELECT JSON_VALUE('error', '$' ERROR ON ERROR);
ERROR:  invalid input syntax for type json
DETAIL:  Token "error" is invalid.
CONTEXT:  JSON data, line 1: error

I'm not sure if it would be better to add an implicit cast to json type that
will be executed before so that parsing errors can no longer be caught.
But implicit casting can simplify a bit execution of SQL/JSON query functions.


I have checked error handling in JSON parsing in Oracle 18c/19c, and it behaves
like our current implementation.  But Oracle seems to do JSON parsing on demand:

Oracle19c> SELECT JSON_VALUE('{a:1 error, b:2}', '$.a' ERROR ON ERROR) FROM dual;
1

Oracle19c> SELECT JSON_VALUE('{a:1 error, b:2}', '$.b' ERROR ON ERROR) FROM dual;
ORA-40441: JSON syntax error

Oracle19c> SELECT JSON_VALUE('{a:1 error, b:2}', '$.b') FROM dual;
NULL

-- 
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company