[
https://issues.apache.org/jira/browse/SPARK-58366?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ganesha S updated SPARK-58366:
------------------------------
Description:
Add the ANSI SQL:2016 JSON_TABLE table-valued function, which shreds a JSON
document into a relational table. A row path selects a sequence of JSON items,
and a COLUMNS clause projects a typed value out of each item into a column.
*Syntax (flat, non-nested subset):*
{code:java}
JSON_TABLE(json_expr, row_path
COLUMNS (
col1 FOR ORDINALITY,
col2 <type> [PATH '<json_path>'],
col3 <type> EXISTS [PATH '<json_path>']
)
[ { NULL | ERROR } ON ERROR ]
) [AS] alias{code}
*Capabilities:*
- Row path with a trailing [*] expands a JSON array into one row per element;
a non-wildcard path yields a single row for the matched value.
- FOR ORDINALITY: a 1-based BIGINT row counter.
- Value columns: extracted and cast to the declared type; the path may be
explicit (PATH '...') or implicit ('$.<columnName>').
- EXISTS columns: presence test, cast to the declared type. A present-but-null
JSON value counts as existing; only an absent path is false.
- { NULL | ERROR } ON ERROR: NULL ON ERROR (the default) produces no rows on
null/malformed input; ERROR ON ERROR raises.
- Usable in a comma join and with LATERAL.
*Motivation:*
JSON_TABLE is the SQL-standard way to turn JSON into rows and columns and is
supported by Oracle, DB2, MySQL 8, PostgreSQL 17, Snowflake, and Trino. Spark
currently requires chaining from_json + explode/inline + get_json_object to
achieve the same result. JSON_TABLE folds that into one declarative, standard
construct and eases migration from those systems.
was:
Add the ANSI SQL:2016 JSON_TABLE table-valued function, which shreds a JSON
document into a relational table. A row path selects a sequence of JSON items,
and a COLUMNS clause projects a typed value out of each item into a column.
*Syntax (flat, non-nested subset):*
```
JSON_TABLE(json_expr, row_path
COLUMNS (
col1 FOR ORDINALITY,
col2 <type> [PATH '<json_path>'],
col3 <type> EXISTS [PATH '<json_path>']
)
[ \{ NULL | ERROR } ON ERROR ]
) [AS] alias
```
*Capabilities:*
- Row path with a trailing [*] expands a JSON array into one row per element;
a non-wildcard path yields a single row for the matched value.
- FOR ORDINALITY: a 1-based BIGINT row counter.
- Value columns: extracted and cast to the declared type; the path may be
explicit (PATH '...') or implicit ('$.<columnName>').
- EXISTS columns: presence test, cast to the declared type. A present-but-null
JSON value counts as existing; only an absent path is false.
- \{ NULL | ERROR } ON ERROR: NULL ON ERROR (the default) produces no rows on
null/malformed input; ERROR ON ERROR raises.
- Usable in a comma join and with LATERAL.
*Motivation:*
JSON_TABLE is the SQL-standard way to turn JSON into rows and columns and is
supported by Oracle, DB2, MySQL 8, PostgreSQL 17, Snowflake, and Trino. Spark
currently requires chaining from_json + explode/inline + get_json_object to
achieve the same result. JSON_TABLE folds that into one declarative, standard
construct and eases migration from those systems.
> [SQL] Support JSON_TABLE table-valued function
> ----------------------------------------------
>
> Key: SPARK-58366
> URL: https://issues.apache.org/jira/browse/SPARK-58366
> Project: Spark
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 4.2.0
> Reporter: Ganesha S
> Priority: Major
>
> Add the ANSI SQL:2016 JSON_TABLE table-valued function, which shreds a JSON
> document into a relational table. A row path selects a sequence of JSON items,
> and a COLUMNS clause projects a typed value out of each item into a column.
> *Syntax (flat, non-nested subset):*
> {code:java}
> JSON_TABLE(json_expr, row_path
> COLUMNS (
> col1 FOR ORDINALITY,
> col2 <type> [PATH '<json_path>'],
> col3 <type> EXISTS [PATH '<json_path>']
> )
> [ { NULL | ERROR } ON ERROR ]
> ) [AS] alias{code}
>
> *Capabilities:*
> - Row path with a trailing [*] expands a JSON array into one row per element;
> a non-wildcard path yields a single row for the matched value.
> - FOR ORDINALITY: a 1-based BIGINT row counter.
> - Value columns: extracted and cast to the declared type; the path may be
> explicit (PATH '...') or implicit ('$.<columnName>').
> - EXISTS columns: presence test, cast to the declared type. A
> present-but-null
> JSON value counts as existing; only an absent path is false.
> - { NULL | ERROR } ON ERROR: NULL ON ERROR (the default) produces no rows on
> null/malformed input; ERROR ON ERROR raises.
> - Usable in a comma join and with LATERAL.
> *Motivation:*
> JSON_TABLE is the SQL-standard way to turn JSON into rows and columns and is
> supported by Oracle, DB2, MySQL 8, PostgreSQL 17, Snowflake, and Trino. Spark
> currently requires chaining from_json + explode/inline + get_json_object to
> achieve the same result. JSON_TABLE folds that into one declarative, standard
> construct and eases migration from those systems.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
