[CALCITE-2871] Implement JSON_TABLE table function

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Forward Xu
• Reporter: Hongze Zhang
• Created: 25/Feb/19 15:22
• Updated: 04/Feb/20 06:03

Description

Below is the syntax of JSON_TABLE described by ISO/IEC TR 19075-5[1]:

<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

A usage example:

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;

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