[jira] [Resolved] (SPARK-58685) Support the ANSI SQL/JSON JSON_VALUE scalar function

["Wenchen Fan (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/SPARK-58685?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Wenchen Fan resolved SPARK-58685.
---------------------------------
    Fix Version/s: 4.4.0
       Resolution: Fixed

Issue resolved by pull request 57888
[https://github.com/apache/spark/pull/57888]

> 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: Sub-task
>          Components: SQL
>    Affects Versions: 4.3.0
>            Reporter: Ganesha S
>            Assignee: Ganesha S
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 4.4.0
>
>
> 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: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org