[
https://issues.apache.org/jira/browse/SPARK-58685?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ganesha S updated SPARK-58685:
------------------------------
Description:
h2. What
Add the ANSI SQL:2016 JSON_VALUE scalar function (feature T821), which extracts
a single scalar value located by a SQL/JSON path from a JSON string:
{code:java}
JSON_VALUE(jsonExpr, path
[ RETURNING <type> ]
[ (NULL | ERROR | DEFAULT expr) ON EMPTY ]
[ (NULL | ERROR | DEFAULT expr) ON ERROR ])
{code}
*Semantics:*
* Returns the scalar at {{{}path{}}}, cast to the RETURNING type (default
STRING).
* A missing path fires ON EMPTY; a structural error (malformed JSON, a
non-scalar object/array match, or a failed cast to the RETURNING type) fires ON
ERROR.
* Both clauses default to NULL ON EMPTY / NULL ON ERROR, per the standard.
* A path matching an explicit JSON null returns SQL NULL; a SQL NULL input
propagates to SQL NULL (not ON EMPTY / ON ERROR).
*Examples:*
{code:java}
JSON_VALUE('{"id":7}', '$.id' RETURNING INT) – 7
JSON_VALUE('{"a":{}}', '$.a') -- NULL
(non-scalar -> NULL ON ERROR)
JSON_VALUE('{"id":7}', '$.missing' DEFAULT '?' ON EMPTY) – '?'
JSON_VALUE('{"n":"x"}', '$.n' RETURNING INT ERROR ON ERROR) -- raises
(failed cast)
{code}
h2. Why
Spark implements none of the ANSI SQL:2016 scalar JSON functions. JSON_VALUE is
near-universal across Oracle, SQL Server, PostgreSQL, MySQL, Trino, Flink,
BigQuery, and DuckDB, so its absence forces every migrated query to be
rewritten to CAST(get_json_object(...)), which always returns STRING, cannot
express RETURNING, and silently returns NULL on both "missing" and "error",
losing the null/error handling the source query intended. JSON_VALUE lets
migrated workloads run unchanged with their intended semantics.
h2. How
* Grammar: add non-reserved keywords JSON_VALUE, RETURNING, EMPTY and a
dedicated JSON_VALUE production (RETURNING / ON EMPTY / ON ERROR / DEFAULT),
mirroring how JSON_TABLE (SPARK-58366) added a non-standard production.
Keywords are non-reserved in both ANSI and default modes, so existing
identifiers named json_value/returning/empty keep working.
* Evaluation: reuse the token-aware path evaluator from JSON_TABLE
(SPARK-58366) via a new JsonTableEvaluator.lookup entry point that preserves
the missing / JSON-null / found distinction; the new JsonValue Catalyst
expression applies the RETURNING cast and ON EMPTY / ON ERROR behavior.
* The extracted-scalar cast is always an ANSI (throwing) cast so the ON ERROR
clause governs a failed conversion regardless of the session's
spark.sql.ansi.enabled setting.
h2. Scope
v1 supports STRING JSON input and scalar (string / numeric / boolean /
datetime) RETURNING types, per ANSI 9075-2 6.28. VARIANT RETURNING and PASSING
arguments are deferred as follow-ups.
was:
h2. What
Add the ANSI SQL:2016 JSON_VALUE scalar function (feature T821), which extracts
a single scalar value located by a SQL/JSON path from a JSON string:
JSON_VALUE(jsonExpr, path
[ RETURNING <type> ]
[ (NULL | ERROR | DEFAULT expr) ON EMPTY ]
[ (NULL | ERROR | DEFAULT expr) ON ERROR ])
Semantics:
* Returns the scalar at \{{path}}, cast to the RETURNING type (default STRING).
* A missing path fires ON EMPTY; a structural error (malformed JSON, a
non-scalar
object/array match, or a failed cast to the RETURNING type) fires ON ERROR.
* Both clauses default to NULL ON EMPTY / NULL ON ERROR, per the standard.
* A path matching an explicit JSON null returns SQL NULL; a SQL NULL input
propagates to SQL NULL (not ON EMPTY / ON ERROR).
Examples:
JSON_VALUE('\{"id":7}', '$.id' RETURNING INT) -- 7
JSON_VALUE('\{"a":{}}', '$.a') -- NULL
(non-scalar -> NULL ON ERROR)
JSON_VALUE('\{"id":7}', '$.missing' DEFAULT '?' ON EMPTY) -- '?'
JSON_VALUE('\{"n":"x"}', '$.n' RETURNING INT ERROR ON ERROR) -- raises
(failed cast)
h2. Why
Spark implements none of the ANSI SQL:2016 scalar JSON functions. JSON_VALUE is
near-universal across Oracle, SQL Server, PostgreSQL, MySQL, Trino, Flink,
BigQuery, and DuckDB, so its absence forces every migrated query to be
rewritten to CAST(get_json_object(...)), which always returns STRING, cannot
express RETURNING, and silently returns NULL on both "missing" and "error" --
losing the null/error handling the source query intended. JSON_VALUE lets
migrated workloads run unchanged with their intended semantics.
h2. How
* Grammar: add non-reserved keywords JSON_VALUE, RETURNING, EMPTY and a
dedicated JSON_VALUE production (RETURNING / ON EMPTY / ON ERROR / DEFAULT),
mirroring how JSON_TABLE (SPARK-58366) added a non-standard production.
Keywords are non-reserved in both ANSI and default modes, so existing
identifiers named json_value/returning/empty keep working.
* Evaluation: reuse the token-aware path evaluator from JSON_TABLE
(SPARK-58366) via a new JsonTableEvaluator.lookup entry point that preserves
the missing / JSON-null / found distinction; the new JsonValue Catalyst
expression applies the RETURNING cast and ON EMPTY / ON ERROR behavior.
* The extracted-scalar cast is always an ANSI (throwing) cast so the ON ERROR
clause governs a failed conversion regardless of the session's
spark.sql.ansi.enabled setting.
h2. Scope
v1 supports STRING JSON input and scalar (string / numeric / boolean /
datetime) RETURNING types, per ANSI 9075-2 6.28. VARIANT RETURNING and PASSING
arguments
are deferred as follow-ups.
> Support the ANSI SQL/JSON JSON_VALUE scalar function
> ----------------------------------------------------
>
> Key: SPARK-58685
> URL: https://issues.apache.org/jira/browse/SPARK-58685
> Project: Spark
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 4.3.0
> Reporter: Ganesha S
> Priority: Major
>
> h2. What
> Add the ANSI SQL:2016 JSON_VALUE scalar function (feature T821), which
> extracts a single scalar value located by a SQL/JSON path from a JSON string:
>
> {code:java}
> JSON_VALUE(jsonExpr, path
> [ RETURNING <type> ]
> [ (NULL | ERROR | DEFAULT expr) ON EMPTY ]
> [ (NULL | ERROR | DEFAULT expr) ON ERROR ])
>
> {code}
>
> *Semantics:*
> * Returns the scalar at {{{}path{}}}, cast to the RETURNING type (default
> STRING).
> * A missing path fires ON EMPTY; a structural error (malformed JSON, a
> non-scalar object/array match, or a failed cast to the RETURNING type) fires
> ON ERROR.
> * Both clauses default to NULL ON EMPTY / NULL ON ERROR, per the standard.
> * A path matching an explicit JSON null returns SQL NULL; a SQL NULL input
> propagates to SQL NULL (not ON EMPTY / ON ERROR).
> *Examples:*
> {code:java}
> JSON_VALUE('{"id":7}', '$.id' RETURNING INT) – 7
> JSON_VALUE('{"a":{}}', '$.a') -- NULL
> (non-scalar -> NULL ON ERROR)
> JSON_VALUE('{"id":7}', '$.missing' DEFAULT '?' ON EMPTY) – '?'
> JSON_VALUE('{"n":"x"}', '$.n' RETURNING INT ERROR ON ERROR) -- raises
> (failed cast)
> {code}
>
> h2. Why
> Spark implements none of the ANSI SQL:2016 scalar JSON functions. JSON_VALUE
> is near-universal across Oracle, SQL Server, PostgreSQL, MySQL, Trino, Flink,
> BigQuery, and DuckDB, so its absence forces every migrated query to be
> rewritten to CAST(get_json_object(...)), which always returns STRING, cannot
> express RETURNING, and silently returns NULL on both "missing" and "error",
> losing the null/error handling the source query intended. JSON_VALUE lets
> migrated workloads run unchanged with their intended semantics.
> h2. How
> * Grammar: add non-reserved keywords JSON_VALUE, RETURNING, EMPTY and a
> dedicated JSON_VALUE production (RETURNING / ON EMPTY / ON ERROR / DEFAULT),
> mirroring how JSON_TABLE (SPARK-58366) added a non-standard production.
> Keywords are non-reserved in both ANSI and default modes, so existing
> identifiers named json_value/returning/empty keep working.
> * Evaluation: reuse the token-aware path evaluator from JSON_TABLE
> (SPARK-58366) via a new JsonTableEvaluator.lookup entry point that preserves
> the missing / JSON-null / found distinction; the new JsonValue Catalyst
> expression applies the RETURNING cast and ON EMPTY / ON ERROR behavior.
> * The extracted-scalar cast is always an ANSI (throwing) cast so the ON
> ERROR clause governs a failed conversion regardless of the session's
> spark.sql.ansi.enabled setting.
> h2. Scope
> v1 supports STRING JSON input and scalar (string / numeric / boolean /
> datetime) RETURNING types, per ANSI 9075-2 6.28. VARIANT RETURNING and
> PASSING arguments are deferred as follow-ups.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
