[
https://issues.apache.org/jira/browse/CALCITE-2871?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16787794#comment-16787794
]
Hongze Zhang edited comment on CALCITE-2871 at 3/8/19 11:19 AM:
----------------------------------------------------------------
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' returning integer),
json_value(x, 'lax $.b' returning integer)]) 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.
was (Author: zhztheplayer):
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)
