Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2867 JSON support
  3. CALCITE-2871

Implement JSON_TABLE table function

    XMLWordPrintableJSON

    Details

    • Type: Sub-task
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      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

        Attachments

          Activity

            People

            • Assignee:
              x1q1j1 Forward Xu
              Reporter:
              zhztheplayer Hongze Zhang
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: