You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Hongze Zhang (JIRA)" <ji...@apache.org> on 2019/02/26 10:49:00 UTC
[jira] [Updated] (CALCITE-2871) Implement JSON_TABLE function
[ https://issues.apache.org/jira/browse/CALCITE-2871?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Hongze Zhang updated CALCITE-2871:
----------------------------------
Description:
Below is the syntax of JSON_TABLE published by ISO/IEC TR 19075-5[1]:
{code}
<JSON table> ::=
JSON_TABLE <left paren>
<JSON API common syntax>
<JSON table columns clause>
[ <JSON table plan clause> ]
[ <JSON table error behavior> ON ERROR ]
<right paren>
<JSON table columns clause> ::=
COLUMNS <left paren>
<JSON table column definition>
[ { <comma> <JSON table column definition> }... ]
<right paren>
<JSON table column definition> ::=
<JSON table ordinality column definition>
| <JSON table regular column definition>
| <JSON table formatted column definition>
| <JSON table nested columns>
<JSON table ordinality column definition> ::=
<column name> FOR ORDINALITY
<JSON table regular column definition> ::=
<column name> <data type>
[ PATH <JSON table column path specification> ]
[ <JSON table column empty behavior> ON EMPTY ]
[ <JSON table column error behavior> ON ERROR ]
<JSON table column empty behavior> ::=
ERROR
| NULL
| DEFAULT <value expression>
<JSON table column error behavior> ::=
ERROR
| NULL
| DEFAULT <value expression>
<JSON table column path specification> ::=
<JSON path specification>
<JSON table formatted column definition> ::=
<column name> <data type>
FORMAT <JSON representation>
[ PATH <JSON table column path specification> ]
[ <JSON table formatted column wrapper behavior> WRAPPER ]
[ <JSON table formatted column quotes behavior> QUOTES
[ ON SCALAR STRING ] ]
[ <JSON table formatted column empty behavior> ON EMPTY ]
[ <JSON table formatted column error behavior> ON ERROR ]
<JSON table formatted column wrapper behavior> ::=
WITHOUT [ ARRAY ]
| WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ]
<JSON table formatted column quotes behavior> ::=
KEEP
| OMIT
<JSON table formatted column empty behavior> ::=
ERROR
| NULL
| EMPTY ARRAY
| EMPTY OBJECT
<JSON table formatted column error behavior> ::=
ERROR
| NULL
| EMPTY ARRAY
| EMPTY OBJECT
<JSON table error behavior> ::=
ERROR
| EMPTY
{code}
As another reference, Oracle has a non-standard implementation[2] of this function.
[1] http://standards.iso.org/ittf/PubliclyAvailableStandards/c065143_ISO_IEC_TR_19075-5_2016.zip
[2] https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973
> Implement JSON_TABLE function
> -----------------------------
>
> Key: CALCITE-2871
> URL: https://issues.apache.org/jira/browse/CALCITE-2871
> Project: Calcite
> Issue Type: Sub-task
> Reporter: Hongze Zhang
> Priority: Major
>
> Below is the syntax of JSON_TABLE published by ISO/IEC TR 19075-5[1]:
> {code}
> <JSON table> ::=
> JSON_TABLE <left paren>
> <JSON API common syntax>
> <JSON table columns clause>
> [ <JSON table plan clause> ]
> [ <JSON table error behavior> ON ERROR ]
> <right paren>
>
> <JSON table columns clause> ::=
> COLUMNS <left paren>
> <JSON table column definition>
> [ { <comma> <JSON table column definition> }... ]
> <right paren>
>
> <JSON table column definition> ::=
> <JSON table ordinality column definition>
> | <JSON table regular column definition>
> | <JSON table formatted column definition>
> | <JSON table nested columns>
> <JSON table ordinality column definition> ::=
> <column name> FOR ORDINALITY
>
> <JSON table regular column definition> ::=
> <column name> <data type>
> [ PATH <JSON table column path specification> ]
> [ <JSON table column empty behavior> ON EMPTY ]
> [ <JSON table column error behavior> ON ERROR ]
>
> <JSON table column empty behavior> ::=
> ERROR
> | NULL
> | DEFAULT <value expression>
>
> <JSON table column error behavior> ::=
> ERROR
> | NULL
> | DEFAULT <value expression>
>
> <JSON table column path specification> ::=
> <JSON path specification>
> <JSON table formatted column definition> ::=
> <column name> <data type>
> FORMAT <JSON representation>
> [ PATH <JSON table column path specification> ]
> [ <JSON table formatted column wrapper behavior> WRAPPER ]
> [ <JSON table formatted column quotes behavior> QUOTES
> [ ON SCALAR STRING ] ]
> [ <JSON table formatted column empty behavior> ON EMPTY ]
> [ <JSON table formatted column error behavior> ON ERROR ]
> <JSON table formatted column wrapper behavior> ::=
> WITHOUT [ ARRAY ]
> | WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ]
>
> <JSON table formatted column quotes behavior> ::=
> KEEP
> | OMIT
>
> <JSON table formatted column empty behavior> ::=
> ERROR
> | NULL
> | EMPTY ARRAY
> | EMPTY OBJECT
>
> <JSON table formatted column error behavior> ::=
> ERROR
> | NULL
> | EMPTY ARRAY
> | EMPTY OBJECT
> <JSON table error behavior> ::=
> ERROR
> | EMPTY
> {code}
> As another reference, Oracle has a non-standard implementation[2] of this function.
> [1] http://standards.iso.org/ittf/PubliclyAvailableStandards/c065143_ISO_IEC_TR_19075-5_2016.zip
> [2] https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)