Ganesha S created SPARK-58685:
---------------------------------
Summary: 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
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.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
