You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Ganesha S (Jira)" <ji...@apache.org> on 2026/08/10 05:42:00 UTC

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

     [ 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: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org