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

["Forward Xu (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-2871?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16864601#comment-16864601 ] 

Forward Xu commented on CALCITE-2871:
-------------------------------------

hi [~zhztheplayer] [~julianhyde] I think the mysql jsontable syntax implementation is also very good, the following is the relevant link.

[jsontable|[https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html]]

> 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)