[ https://issues.apache.org/jira/browse/CALCITE-2871?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16787794#comment-16787794 ]
Hongze Zhang commented on CALCITE-2871: --------------------------------------- Thank you for the suggestion [~julianhyde], and sorry for the late reply. I like the idea making JSON_TABLE syntactic sugar of some regular table functions, even I think maybe we can map it to the existing table constructor {{MAP[...]}} sometimes when the JSON_TABLE expression is simple enough. For instance, following two SQLs are actually equivalent : {code:sql} -- 1. JSON_TABLE call with tab(x) as (values '{"a":1,"b":2}', '{"a":2,"b":3}', '{"a":1,"b":2}') select r.* from tab, json_table(x, 'lax $' columns (a integer path 'lax $.a' b integer path 'lax $.b')) as r; {code} {code:sql} -- 2. MAP constructor call with tab(x) as (values '{"a":1,"b":2}', '{"a":2,"b":3}', '{"a":1,"b":2}') select r.* from tab, unnest(map[json_value(x, 'lax $.a'), json_value(x, 'lax $.b')]) as r(a, b); {code} But currently I'm not sure the {{MAP[...]}} constructor is good enough to deal with more complicated JSON_TABLE calls. Maybe we'll end up with creating new table functions. > 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)