[
https://issues.apache.org/jira/browse/SPARK-58685?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-58685:
-----------------------------------
Labels: pull-request-available (was: )
> 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
> Labels: pull-request-available
>
> 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]
