[jira] [Updated] (CALCITE-2871) Implement JSON_TABLE table function

Hongze Zhang (JIRA) Tue, 26 Feb 2019 03:03:33 -0800


     [ 
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

<JSON table nested columns> ::=
        NESTED [ PATH ] <JSON table nested path specification>
        [ AS <JSON table nested path name> ]
        <JSON table columns clause>

<JSON table nested path specification> ::=
        <JSON path specification>

<JSON table nested path name> ::=
        <JSON table path name>

<JSON table path name> ::=
        <identifier>
        
<JSON table plan clause> ::=
        <JSON table specific plan>
        | <JSON table default plan>

<JSON table specific plan> ::=
        PLAN <left paren> <JSON table plan> <right paren>

<JSON table plan> ::=
        <JSON table path name>
        | <JSON table plan parent/child>
        | <JSON table plan sibling>

<JSON table plan parent/child> ::=
        <JSON table plan outer>
        | <JSON table plan inner>
        
<JSON table plan outer> ::=
        <JSON table path name> OUTER <JSON table plan primary>
        
<JSON table plan inner> ::=
        <JSON table path name> INNER <JSON table plan primary>
        
<JSON table plan sibling> ::=
        <JSON table plan union>
        | <JSON table plan cross>
        
<JSON table plan union> ::=
        <JSON table plan primary> UNION <JSON table plan primary>
        [ { UNION <JSON table plan primary> }... ]

<JSON table plan cross> ::=
        <JSON table plan primary> CROSS <JSON table plan primary>
        [ { CROSS <JSON table plan primary> }... ]
        
<JSON table plan primary> ::=
        <JSON table path name>
        | <left paren> <JSON table plan> <right paren>
        
<JSON table default plan> ::=
        PLAN DEFAULT <left paren> <JSON table default plan choices> <right 
paren>

<JSON table default plan choices> ::=
        <JSON table default plan inner/outer>
        [ <comma> <JSON table default plan union/cross> ]
        | <JSON table default plan union/cross>
        [ <comma> <JSON table default plan inner/outer> ]
        
<JSON table default plan inner/outer> ::=
        INNER
        | OUTER
        
<JSON table default plan union/cross> ::=
        UNION
        | CROSS
{code}

A usage example:
{code:sql}
SELECT bookclub.id, jt.name, jt.type, jt.number
    FROM bookclub,
        JSON_TABLE ( bookclub.jcol, 'lax $'
                     COLUMNS ( name VARCHAR(30) PATH 'lax $.Name',
                               NESTED PATH 'lax $.phoneNumber[*]'
                               COLUMNS ( type VARCHAR(10) PATH 'lax $.type',
                                           number CHAR(12) PATH 'lax $.number' )
                                ) AS jt;
{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

  was:
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}

A usage example:
{code:sql}
SELECT bookclub.id, jt.name, jt.type, jt.number
    FROM bookclub,
        JSON_TABLE ( bookclub.jcol, 'lax $'
                     COLUMNS ( name VARCHAR(30) PATH 'lax $.Name',
                               NESTED PATH 'lax $.phoneNumber[*]'
                               COLUMNS ( type VARCHAR(10) PATH 'lax $.type',
                                           number CHAR(12) PATH 'lax $.number' )
                                ) AS jt;
{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 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
> <JSON table nested columns> ::=
>       NESTED [ PATH ] <JSON table nested path specification>
>       [ AS <JSON table nested path name> ]
>       <JSON table columns clause>
> <JSON table nested path specification> ::=
>       <JSON path specification>
> <JSON table nested path name> ::=
>       <JSON table path name>
> <JSON table path name> ::=
>       <identifier>
>       
> <JSON table plan clause> ::=
>       <JSON table specific plan>
>       | <JSON table default plan>
> <JSON table specific plan> ::=
>       PLAN <left paren> <JSON table plan> <right paren>
> <JSON table plan> ::=
>       <JSON table path name>
>       | <JSON table plan parent/child>
>       | <JSON table plan sibling>
> <JSON table plan parent/child> ::=
>       <JSON table plan outer>
>       | <JSON table plan inner>
>       
> <JSON table plan outer> ::=
>       <JSON table path name> OUTER <JSON table plan primary>
>       
> <JSON table plan inner> ::=
>       <JSON table path name> INNER <JSON table plan primary>
>       
> <JSON table plan sibling> ::=
>       <JSON table plan union>
>       | <JSON table plan cross>
>       
> <JSON table plan union> ::=
>       <JSON table plan primary> UNION <JSON table plan primary>
>       [ { UNION <JSON table plan primary> }... ]
> <JSON table plan cross> ::=
>       <JSON table plan primary> CROSS <JSON table plan primary>
>       [ { CROSS <JSON table plan primary> }... ]
>       
> <JSON table plan primary> ::=
>       <JSON table path name>
>       | <left paren> <JSON table plan> <right paren>
>       
> <JSON table default plan> ::=
>       PLAN DEFAULT <left paren> <JSON table default plan choices> <right 
> paren>
> <JSON table default plan choices> ::=
>       <JSON table default plan inner/outer>
>       [ <comma> <JSON table default plan union/cross> ]
>       | <JSON table default plan union/cross>
>       [ <comma> <JSON table default plan inner/outer> ]
>       
> <JSON table default plan inner/outer> ::=
>       INNER
>       | OUTER
>       
> <JSON table default plan union/cross> ::=
>       UNION
>       | CROSS
> {code}
> A usage example:
> {code:sql}
> SELECT bookclub.id, jt.name, jt.type, jt.number
>     FROM bookclub,
>         JSON_TABLE ( bookclub.jcol, 'lax $'
>                      COLUMNS ( name VARCHAR(30) PATH 'lax $.Name',
>                                NESTED PATH 'lax $.phoneNumber[*]'
>                                COLUMNS ( type VARCHAR(10) PATH 'lax $.type',
>                                            number CHAR(12) PATH 'lax 
> $.number' )
>                                 ) AS jt;
> {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)

[jira] [Updated] (CALCITE-2871) Implement JSON_TABLE table function

Reply via email to