[
https://issues.apache.org/jira/browse/CALCITE-2871?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16778526#comment-16778526
]
Julian Hyde commented on CALCITE-2871:
--------------------------------------
Thanks for the specification and example. They help a lot.
I see that it is like a table function, but goes a little beyond it in terms of
syntax (e.g. the COLUMNS clause). With that in mind, what design do you have in
mind to implement it? It would be nice if we could treat the syntax extensions
as just syntactic sugar, and map it to a new table function. Then we would need
only a few changes to SqlToRelConverter and no changes in RelNode land.
> 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 described 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)
