This is an automated email from the ASF dual-hosted git repository.
cloud-fan pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.x by this push:
new fa967817f73a [SPARK-58685][SQL] Support the ANSI SQL/JSON JSON_VALUE
scalar function
fa967817f73a is described below
commit fa967817f73a559aabfa05513fb2f1d1928bf7c8
Author: Ganesha S <[email protected]>
AuthorDate: Tue Aug 11 20:33:23 2026 +0800
[SPARK-58685][SQL] Support the ANSI SQL/JSON JSON_VALUE scalar function
### What changes were proposed in this pull request?
Add the ANSI SQL:2016 `JSON_VALUE` scalar function (feature T821):
```
JSON_VALUE(jsonExpr, path
[ RETURNING <type> ]
[ (NULL | ERROR | DEFAULT expr) ON EMPTY ]
[ (NULL | ERROR | DEFAULT expr) ON ERROR ])
```
It extracts the scalar at `path`, cast to the RETURNING type (default
STRING). A missing path fires ON EMPTY; malformed JSON, a non-scalar match, or
a failed cast fires ON ERROR; both default to NULL. An explicit JSON null
returns SQL NULL, and a SQL NULL input propagates to NULL.
Grammar adds non-reserved keywords `JSON_VALUE`, `RETURNING`, `EMPTY` and a
dedicated production, mirroring `JSON_TABLE` (SPARK-58366). Evaluation reuses
JSON_TABLE's token-aware path evaluator via a new `JsonTableEvaluator.lookup`
entry point that preserves the missing / JSON-null / found distinction. The
extracted-scalar cast is always ANSI (throwing) so ON ERROR governs a failed
conversion regardless of `spark.sql.ansi.enabled`.
v1 supports STRING input and scalar (string/numeric/boolean/datetime)
RETURNING types per ANSI 9075-2 6.28; VARIANT RETURNING and PASSING are
deferred.
### Why are the changes needed?
Spark implements none of the ANSI scalar JSON functions. `JSON_VALUE` is
near-universal (Oracle, SQL Server, PostgreSQL, Trino, BigQuery, ...), so its
absence forces migrated queries to rewrite to `CAST(get_json_object(...))`,
which is STRING-only, cannot express RETURNING, and conflates missing vs.
error.
### Does this PR introduce _any_ user-facing change?
Yes. It adds the new `JSON_VALUE` SQL function. The new keywords are
non-reserved in both ANSI and default modes, so existing identifiers named
`json_value` /`returning` / `empty` continue to work.
### How was this patch tested?
New `JsonValueSuite` (both ANSI modes), `json-functions.sql` golden files,
`ExpressionParserSuite`, `SQLKeywordSuite`, and `SparkThrowableSuite` for the
new error conditions.
### Was this patch authored or co-authored using generative AI tooling?
Yes, Claude Opus 4.8.
Closes #57888 from ganeshashree/SPARK-58685.
Lead-authored-by: Ganesha S <[email protected]>
Co-authored-by: Ganesha S <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
(cherry picked from commit f777036d64d0c0b7413907526f869bd7cb44e49b)
Signed-off-by: Wenchen Fan <[email protected]>
---
.../src/main/resources/error/error-conditions.json | 28 +++
docs/sql-ref-ansi-compliance.md | 3 +
docs/sql-ref-syntax-qry-select-json-value.md | 136 +++++++++++
docs/sql-ref-syntax-qry-select.md | 1 +
docs/sql-ref-syntax.md | 1 +
.../spark/sql/catalyst/parser/SqlBaseLexer.g4 | 3 +
.../spark/sql/catalyst/parser/SqlBaseParser.g4 | 18 ++
.../expressions/json/JsonExpressionEvalUtils.scala | 101 ++++++++
.../sql/catalyst/expressions/jsonExpressions.scala | 226 +++++++++++++++++-
.../spark/sql/catalyst/parser/AstBuilder.scala | 31 +++
.../spark/sql/errors/QueryExecutionErrors.scala | 14 ++
.../expressions/JsonExpressionsSuite.scala | 9 +-
.../catalyst/parser/ExpressionParserSuite.scala | 24 ++
.../jdbc/SparkConnectDatabaseMetaDataSuite.scala | 2 +-
.../analyzer-results/json-functions.sql.out | 213 +++++++++++++++++
.../resources/sql-tests/inputs/json-functions.sql | 38 +++
.../sql-tests/results/json-functions.sql.out | 264 +++++++++++++++++++++
.../sql-tests/results/keywords-enforced.sql.out | 3 +
.../resources/sql-tests/results/keywords.sql.out | 3 +
.../sql-tests/results/nonansi/keywords.sql.out | 3 +
.../org/apache/spark/sql/JsonValueSuite.scala | 263 ++++++++++++++++++++
.../ThriftServerWithSparkContextSuite.scala | 2 +-
22 files changed, 1381 insertions(+), 5 deletions(-)
diff --git a/common/utils/src/main/resources/error/error-conditions.json
b/common/utils/src/main/resources/error/error-conditions.json
index acd7ce8de8a2..b4c76d17464e 100644
--- a/common/utils/src/main/resources/error/error-conditions.json
+++ b/common/utils/src/main/resources/error/error-conditions.json
@@ -1963,6 +1963,16 @@
"Input schema <schema> can only contain STRING as a key type for a
MAP."
]
},
+ "INVALID_JSON_PATH" : {
+ "message" : [
+ "<functionName> has an invalid or unsupported JSON path <path>. Only
simple, wildcard-free paths are supported."
+ ]
+ },
+ "INVALID_JSON_SCALAR_RETURNING_TYPE" : {
+ "message" : [
+ "<functionName> cannot return a value of type <returningType>. The
RETURNING type must be a scalar (string, numeric, boolean, or datetime) type."
+ ]
+ },
"INVALID_JSON_SCHEMA" : {
"message" : [
"Input schema <schema> must be a struct, an array, a map or a
variant."
@@ -5579,6 +5589,24 @@
],
"sqlState" : "42K0E"
},
+ "JSON_VALUE_ON_ERROR" : {
+ "message" : [
+ "<functionName> could not extract a scalar value at path <path>."
+ ],
+ "subClass" : {
+ "EMPTY" : {
+ "message" : [
+ "The path matched no value. This error was requested by the ERROR ON
EMPTY clause."
+ ]
+ },
+ "ERROR" : {
+ "message" : [
+ "The input is not valid JSON, the matched value is not a scalar, or
it could not be cast to the RETURNING type. This error was requested by the
ERROR ON ERROR clause."
+ ]
+ }
+ },
+ "sqlState" : "2203G"
+ },
"KAFKA_DATA_SOURCE_NOT_ENABLED" : {
"message" : [
"Failed to find data source: <provider>. Please deploy the application
as per the deployment section of Structured Streaming + Kafka Integration
Guide."
diff --git a/docs/sql-ref-ansi-compliance.md b/docs/sql-ref-ansi-compliance.md
index 4eb886a16e60..77c4d96da1ea 100644
--- a/docs/sql-ref-ansi-compliance.md
+++ b/docs/sql-ref-ansi-compliance.md
@@ -537,6 +537,7 @@ Below is a list of all the keywords in Spark SQL.
|DROP|non-reserved|non-reserved|reserved|
|ELSE|reserved|non-reserved|reserved|
|ELSEIF|non-reserved|non-reserved|non-reserved|
+|EMPTY|non-reserved|non-reserved|reserved|
|END|reserved|non-reserved|reserved|
|ENFORCED|non-reserved|non-reserved|non-reserved|
|ERROR|non-reserved|non-reserved|non-reserved|
@@ -618,6 +619,7 @@ Below is a list of all the keywords in Spark SQL.
|JOIN|reserved|strict-non-reserved|reserved|
|JSON|non-reserved|non-reserved|non-reserved|
|JSON_TABLE|non-reserved|non-reserved|reserved|
+|JSON_VALUE|non-reserved|non-reserved|reserved|
|KEY|non-reserved|non-reserved|non-reserved|
|KEYS|non-reserved|non-reserved|non-reserved|
|LANGUAGE|non-reserved|non-reserved|reserved|
@@ -735,6 +737,7 @@ Below is a list of all the keywords in Spark SQL.
|RESPECT|non-reserved|non-reserved|non-reserved|
|RESTRICT|non-reserved|non-reserved|non-reserved|
|RETURN|non-reserved|non-reserved|reserved|
+|RETURNING|non-reserved|non-reserved|non-reserved|
|RETURNS|non-reserved|non-reserved|reserved|
|REVOKE|non-reserved|non-reserved|reserved|
|RIGHT|reserved|strict-non-reserved|reserved|
diff --git a/docs/sql-ref-syntax-qry-select-json-value.md
b/docs/sql-ref-syntax-qry-select-json-value.md
new file mode 100644
index 000000000000..deb0be82c2b5
--- /dev/null
+++ b/docs/sql-ref-syntax-qry-select-json-value.md
@@ -0,0 +1,136 @@
+---
+layout: global
+title: JSON_VALUE
+displayTitle: JSON_VALUE
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+### Description
+
+The `JSON_VALUE` scalar function extracts a single scalar value located by a
SQL/JSON path from a
+JSON document and returns it cast to the `RETURNING` type (`STRING` by
default). This is the
+SQL-standard way (SQL:2016) to pull an individual value out of JSON, and is
commonly used to
+migrate queries from other systems such as Oracle, DB2, and MySQL. Unlike
+[JSON_TABLE](sql-ref-syntax-qry-select-json-table.html), which produces rows
in a `FROM` clause,
+`JSON_VALUE` is an expression that can appear anywhere a scalar is allowed.
+
+The function returns a scalar only. A path that matches an object or array is
an *error* case (see
+`ON ERROR`), not a value. Use
[JSON_TABLE](sql-ref-syntax-qry-select-json-table.html) or the
+built-in `get_json_object` function to extract structured fragments.
+
+### Syntax
+
+```sql
+JSON_VALUE ( json_expr, path
+ [ RETURNING data_type ]
+ [ empty_behavior ON EMPTY ]
+ [ error_behavior ON ERROR ] )
+
+empty_behavior
+ { NULL | ERROR | DEFAULT default_expr }
+
+error_behavior
+ { NULL | ERROR | DEFAULT default_expr }
+```
+
+### Parameters
+
+* **json_expr**
+
+ An expression that evaluates to a `STRING` containing the JSON document. A
`NULL` input yields
+ `NULL` directly (it triggers neither the `ON EMPTY` nor the `ON ERROR`
behavior).
+
+* **path**
+
+ A SQL/JSON path literal that locates the value, for example `'$.a.b'` or
`'$.items[0]'`. The
+ path must be wildcard-free, since `JSON_VALUE` returns a single value; a
path containing `[*]`
+ is rejected at analysis time.
+
+* **RETURNING data_type**
+
+ The type the extracted value is cast to. It must be a scalar (atomic)
type: a string, numeric,
+ boolean, or datetime type. Non-atomic types (`STRUCT`, `ARRAY`, `MAP`) and
`VARIANT` / `BINARY`
+ are not supported. If `RETURNING` is omitted, the result type is `STRING`.
+
+* **empty_behavior ON EMPTY**
+
+ What to produce when `path` matches nothing:
+ * `NULL` (the default) returns SQL `NULL`.
+ * `ERROR` raises an error.
+ * `DEFAULT default_expr` returns `default_expr`, cast to the `RETURNING`
type.
+
+* **error_behavior ON ERROR**
+
+ What to produce when the extraction fails: the input is not well-formed
JSON, the path matches a
+ non-scalar (object or array) value, or casting the matched scalar to the
`RETURNING` type fails.
+ * `NULL` (the default) returns SQL `NULL`.
+ * `ERROR` raises an error.
+ * `DEFAULT default_expr` returns `default_expr`, cast to the `RETURNING`
type.
+
+ The cast of the matched scalar to the `RETURNING` type always follows ANSI
semantics (a failed
+ conversion routes to `ON ERROR`), independently of the session's
`spark.sql.ansi.enabled`
+ setting.
+
+A path that matches an explicit JSON `null` is a present scalar value and
returns SQL `NULL` (it is
+neither the `ON EMPTY` nor the `ON ERROR` case).
+
+### Examples
+
+```sql
+-- Extract a scalar as STRING (the default)
+SELECT json_value('{"id":7,"name":"Ada"}', '$.name');
++-------------------------------------------+
+|json_value({"id":7,"name":"Ada"}, $.name) |
++-------------------------------------------+
+|Ada |
++-------------------------------------------+
+
+-- Cast the extracted value with RETURNING
+SELECT json_value('{"id":7}', '$.id' RETURNING INT) + 1;
++---------------------------------------------+
+|(json_value({"id":7}, $.id) + 1) |
++---------------------------------------------+
+|8 |
++---------------------------------------------+
+
+-- A missing path defaults to NULL; supply a fallback with DEFAULT ... ON EMPTY
+-- (RETURNING, when present, comes before the ON EMPTY / ON ERROR clauses)
+SELECT json_value('{"id":7}', '$.missing' RETURNING INT DEFAULT -1 ON EMPTY);
++---------------------------------------------------------------+
+|json_value({"id":7}, $.missing RETURNING INT DEFAULT -1 ON EMPTY)|
++---------------------------------------------------------------+
+|-1 |
++---------------------------------------------------------------+
+
+-- A non-scalar match or malformed input is an ON ERROR case
+SELECT json_value('{"addr":{"city":"NYC"}}', '$.addr' DEFAULT 'n/a' ON ERROR);
++---------------------------------------------------------------+
+|json_value({"addr":{"city":"NYC"}}, $.addr DEFAULT n/a ON ERROR)|
++---------------------------------------------------------------+
+|n/a |
++---------------------------------------------------------------+
+
+-- ERROR ON ERROR raises instead of returning a value
+SELECT json_value('not json', '$.a' ERROR ON ERROR);
+[JSON_VALUE_ON_ERROR.ERROR] ...
+```
+
+### Related Statements
+
+* [SELECT](sql-ref-syntax-qry-select.html)
+* [JSON_TABLE](sql-ref-syntax-qry-select-json-table.html)
+* [Built-in Functions](sql-ref-functions-builtin.html)
diff --git a/docs/sql-ref-syntax-qry-select.md
b/docs/sql-ref-syntax-qry-select.md
index d07f95e6b7b6..b2afd160c5a3 100644
--- a/docs/sql-ref-syntax-qry-select.md
+++ b/docs/sql-ref-syntax-qry-select.md
@@ -213,6 +213,7 @@ SELECT [ hints , ... ] [ ALL | DISTINCT ] { [ [
named_expression | regex_column_
* [Set Operators](sql-ref-syntax-qry-select-setops.html)
* [TABLESAMPLE](sql-ref-syntax-qry-select-sampling.html)
* [Table-valued Function](sql-ref-syntax-qry-select-tvf.html)
+* [JSON_VALUE](sql-ref-syntax-qry-select-json-value.html)
* [Window Function](sql-ref-syntax-qry-select-window.html)
* [CASE Clause](sql-ref-syntax-qry-select-case.html)
* [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
diff --git a/docs/sql-ref-syntax.md b/docs/sql-ref-syntax.md
index 36cd297aaf82..c8ddecaf6bdb 100644
--- a/docs/sql-ref-syntax.md
+++ b/docs/sql-ref-syntax.md
@@ -84,6 +84,7 @@ ability to generate logical and physical plan for a given
query using
* [TABLESAMPLE](sql-ref-syntax-qry-select-sampling.html)
* [Table-valued Function](sql-ref-syntax-qry-select-tvf.html)
* [JSON_TABLE](sql-ref-syntax-qry-select-json-table.html)
+ * [JSON_VALUE](sql-ref-syntax-qry-select-json-value.html)
* [WHERE Clause](sql-ref-syntax-qry-select-where.html)
* [Aggregate Function](sql-ref-syntax-qry-select-aggregate.html)
* [Window Function](sql-ref-syntax-qry-select-window.html)
diff --git
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseLexer.g4
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseLexer.g4
index 5e15b72d6a30..c042bca5e8ce 100644
---
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseLexer.g4
+++
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseLexer.g4
@@ -254,6 +254,7 @@ DOUBLE: 'DOUBLE';
DROP: 'DROP';
ELSE: 'ELSE';
ELSEIF: 'ELSEIF';
+EMPTY: 'EMPTY';
END: 'END';
ENFORCED: 'ENFORCED';
ERROR: 'ERROR';
@@ -335,6 +336,7 @@ ITERATE: 'ITERATE';
JOIN: 'JOIN';
JSON: 'JSON';
JSON_TABLE: 'JSON_TABLE';
+JSON_VALUE: 'JSON_VALUE';
KEY: 'KEY';
KEYS: 'KEYS';
LANGUAGE: 'LANGUAGE';
@@ -450,6 +452,7 @@ RESET: 'RESET';
RESPECT: 'RESPECT';
RESTRICT: 'RESTRICT';
RETURN: 'RETURN';
+RETURNING: 'RETURNING';
RETURNS: 'RETURNS';
REVOKE: 'REVOKE';
RIGHT: 'RIGHT';
diff --git
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
index 98fb1cd5f29f..ee7de7eac2bf 100644
---
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
+++
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
@@ -1462,6 +1462,10 @@ primaryExpression
| ANY_VALUE LEFT_PAREN expression (IGNORE NULLS)? RIGHT_PAREN
#any_value
| LAST LEFT_PAREN expression (IGNORE NULLS)? RIGHT_PAREN
#last
| POSITION LEFT_PAREN substr=valueExpression IN str=valueExpression
RIGHT_PAREN #position
+ | JSON_VALUE LEFT_PAREN jsonExpr=valueExpression COMMA path=stringLit
+ (RETURNING returning=dataType)?
+ (emptyBehavior=jsonValueBehavior ON EMPTY)?
+ (errorBehavior=jsonValueBehavior ON ERROR)? RIGHT_PAREN
#jsonValue
| constant
#constantDefault
| ASTERISK exceptClause?
#star
| qualifiedName DOT ASTERISK exceptClause?
#star
@@ -1488,6 +1492,14 @@ primaryExpression
FROM position=valueExpression (FOR length=valueExpression)? RIGHT_PAREN
#overlay
;
+// The behavior selected by a JSON_VALUE `... ON EMPTY` / `... ON ERROR`
clause. NULL and ERROR are
+// keywords; DEFAULT carries an expression evaluated in place of the
missing/erroring value.
+jsonValueBehavior
+ : NULL
#jsonValueBehaviorNull
+ | ERROR
#jsonValueBehaviorError
+ | DEFAULT defaultExpr=expression
#jsonValueBehaviorDefault
+ ;
+
semiStructuredExtractionPath
: jsonPathFirstPart (jsonPathParts)*
;
@@ -2171,6 +2183,7 @@ ansiNonReserved
| DOUBLE
| DROP
| ELSEIF
+ | EMPTY
| ENFORCED
| ERROR
| ESCAPED
@@ -2232,6 +2245,7 @@ ansiNonReserved
| ITERATE
| JSON
| JSON_TABLE
+ | JSON_VALUE
| KEY
| KEYS
| LANGUAGE
@@ -2330,6 +2344,7 @@ ansiNonReserved
| RESPECT
| RESTRICT
| RETURN
+ | RETURNING
| RETURNS
| REVOKE
| RLIKE
@@ -2592,6 +2607,7 @@ nonReserved
| DROP
| ELSE
| ELSEIF
+ | EMPTY
| END
| ENFORCED
| ERROR
@@ -2668,6 +2684,7 @@ nonReserved
| ITERATE
| JSON
| JSON_TABLE
+ | JSON_VALUE
| KEY
| KEYS
| LANGUAGE
@@ -2780,6 +2797,7 @@ nonReserved
| RESPECT
| RESTRICT
| RETURN
+ | RETURNING
| RETURNS
| REVOKE
| RLIKE
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/json/JsonExpressionEvalUtils.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/json/JsonExpressionEvalUtils.scala
index fbb81bccaa80..bf0891ef7236 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/json/JsonExpressionEvalUtils.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/json/JsonExpressionEvalUtils.scala
@@ -373,6 +373,25 @@ object JsonPathResult {
case class Found(raw: UTF8String) extends JsonPathResult
}
+/**
+ * The result of a single-value [[JsonTableEvaluator.lookup]] for
`JSON_VALUE`. Unlike
+ * [[JsonPathResult]] -- whose `Found` carries verbatim JSON text for
`JSON_TABLE` to unquote later
+ * -- a `Scalar` here already holds the value's cast-ready text (a string's
unquoted/unescaped
+ * content, a number's or boolean's source text), and an object/array match is
reported as
+ * `NonScalar` without being serialized at all, since `JSON_VALUE` routes
non-scalars to ON ERROR.
+ */
+sealed trait JsonValueLookup
+object JsonValueLookup {
+ /** The path did not match (routes to ON EMPTY). */
+ case object Missing extends JsonValueLookup
+ /** The path matched a JSON `null` literal (yields SQL NULL). */
+ case object NullValue extends JsonValueLookup
+ /** The path matched an object or array, i.e. not a scalar (routes to ON
ERROR). */
+ case object NonScalar extends JsonValueLookup
+ /** The path matched a scalar; `text` is its cast-ready value (strings
already unquoted). */
+ case class Scalar(text: UTF8String) extends JsonValueLookup
+}
+
/**
* A prefix trie over the (wildcard-free) column paths of a single
`JSON_TABLE` invocation, built
* once via [[JsonTableEvaluator.buildPathTrie]] and reused for every row. It
lets
@@ -483,6 +502,88 @@ case class JsonTableEvaluator(containerPath:
Seq[PathInstruction], explodeRoot:
}
}
+ /**
+ * Resolves `containerPath` against a single JSON value for `JSON_VALUE`,
preserving the missing /
+ * JSON-null / found distinction that [[evaluate]] collapses. Returns:
+ *
+ * - `None` if the input is not a single well-formed JSON value (malformed
/ trailing garbage /
+ * empty);
+ * - `Some(Missing)` if the path matches nothing;
+ * - `Some(NullValue)` if the path matches an explicit JSON `null`;
+ * - `Some(NonScalar)` if the path matches an object or array;
+ * - `Some(Scalar(text))` if the path matches a scalar, where `text` is
its cast-ready value --
+ * a string's unquoted/unescaped content, a number's or boolean's source
text (see
+ * [[scalarAt]]).
+ *
+ * A `null` input is the caller's responsibility. `explodeRoot` is ignored:
this is a single-value
+ * lookup, so construct the evaluator with `explodeRoot = false`.
+ *
+ * A single parser both navigates the path and validates well-formedness:
after the matched value
+ * is captured (or the path is found missing), [[drainToRootEnd]] consumes
the rest of the root
+ * value and rejects any trailing content, so a valid prefix followed by
garbage (or a second
+ * root value) is rejected exactly as a fully malformed document is. This
avoids the extra
+ * O(document size) validation pass a separate `isSingleWellFormedValue`
scan would add per row.
+ */
+ final def lookup(json: UTF8String): Option[JsonValueLookup] = {
+ Utils.tryWithResource(CreateJacksonParser.utf8String(jsonFactory, json)) {
parser =>
+ try {
+ if (parser.nextToken() == null) {
+ None // empty or whitespace-only
+ } else {
+ val result = positionAt(parser, containerPath) match {
+ case PositionResult.Missing => JsonValueLookup.Missing
+ case PositionResult.NullValue => JsonValueLookup.NullValue
+ case PositionResult.AtValue => scalarAt(parser)
+ }
+ // Reject a valid prefix trailed by extra content, keeping
malformed-input semantics
+ // identical to the array row source (which validates the whole
document up front).
+ if (drainToRootEnd(parser)) Some(result) else None
+ }
+ } catch {
+ case _: JsonProcessingException => None
+ }
+ }
+ }
+
+ /**
+ * Classifies the value the parser is positioned at (the `AtValue` case of
[[positionAt]]) for a
+ * `JSON_VALUE` [[lookup]], avoiding the serialize-then-reparse round trip
that
+ * [[serializeCurrentValue]] followed by [[unquotedString]] would incur:
+ *
+ * - an object or array is consumed with `skipChildren` -- so
[[drainToRootEnd]] can still walk
+ * back out and validate the document -- and reported as `NonScalar`,
never serialized, since
+ * `JSON_VALUE` routes non-scalars to ON ERROR and so never needs the
value;
+ * - a scalar's cast-ready text is read straight from the parser via
`getText`, which returns a
+ * string's unquoted, unescaped content and a number's or boolean's
verbatim source characters
+ * (so a high-precision fraction reaches a DECIMAL/STRING cast intact,
as with
+ * `copyCurrentStructureExact`).
+ *
+ * A JSON `null` never reaches here -- [[positionAt]] reports it as
`NullValue`.
+ */
+ private def scalarAt(parser: JsonParser): JsonValueLookup =
parser.currentToken match {
+ case JsonToken.START_OBJECT | JsonToken.START_ARRAY =>
+ parser.skipChildren()
+ JsonValueLookup.NonScalar
+ case _ =>
+ JsonValueLookup.Scalar(UTF8String.fromString(parser.getText))
+ }
+
+ /**
+ * Finishes consuming the root JSON value the `parser` is partway through
and verifies nothing
+ * follows it, returning false if the input is truncated or has trailing
content. Callers navigate
+ * to (and serialize) a matched value with the same parser, which can leave
it positioned inside
+ * the enclosing containers; this walks back out to the root and confirms
the document held
+ * exactly one well-formed value.
+ */
+ private def drainToRootEnd(parser: JsonParser): Boolean = {
+ // Walk out of any still-open containers, consuming the remainder of the
root value.
+ while (!parser.getParsingContext.inRoot) {
+ if (parser.nextToken() == null) return false // truncated mid-value
+ }
+ // At the root now: exactly one value was present iff nothing remains.
+ parser.nextToken() == null
+ }
+
/**
* Navigates `path` and leaves the parser positioned at the first token of
the matched value
* (returning `AtValue`), or returns `Missing`/`NullValue`. Unlike the
column projection traversal
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/jsonExpressions.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/jsonExpressions.scala
index 577956bb15bd..c91221690091 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/jsonExpressions.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/jsonExpressions.scala
@@ -25,8 +25,8 @@ import
org.apache.spark.sql.catalyst.expressions.codegen.{CodegenContext, CodeGe
import org.apache.spark.sql.catalyst.expressions.codegen.Block.BlockHelper
import org.apache.spark.sql.catalyst.expressions.json.{GetJsonObjectEvaluator,
JsonExpressionUtils,
JsonPathParser, JsonPathResult, JsonTableEvaluator, JsonTablePathTrie,
JsonToStructsEvaluator,
- JsonTupleEvaluator, MultiGetJsonObjectEvaluator, PathInstruction,
SchemaOfJsonEvaluator,
- StructsToJsonEvaluator}
+ JsonTupleEvaluator, JsonValueLookup, MultiGetJsonObjectEvaluator,
PathInstruction,
+ SchemaOfJsonEvaluator, StructsToJsonEvaluator}
import org.apache.spark.sql.catalyst.expressions.objects.{Invoke, StaticInvoke}
import org.apache.spark.sql.catalyst.json._
import org.apache.spark.sql.catalyst.trees.TreePattern.{GET_JSON_OBJECT,
JSON_TO_STRUCT,
@@ -637,6 +637,228 @@ case class JsonTable(
copy(child = newChild)
}
+/**
+ * Behavior of `JSON_VALUE`'s `ON EMPTY` / `ON ERROR` clause: what to produce
when the path matches
+ * nothing, or when the input/extraction fails.
+ */
+sealed trait JsonValueBehavior
+object JsonValueBehavior {
+ /** Produce SQL NULL (the SQL-standard default for both ON EMPTY and ON
ERROR). */
+ case object Null extends JsonValueBehavior
+ /** Raise an error. */
+ case object Error extends JsonValueBehavior
+ /** Produce the value of a `DEFAULT` expression, cast to the RETURNING type.
*/
+ case object Default extends JsonValueBehavior
+}
+
+// scalastyle:off line.size.limit
+/**
+ * The SQL:2016 `JSON_VALUE` scalar function (feature T821): extracts a single
scalar located by a
+ * SQL/JSON `path` from a JSON input, casts it to the `RETURNING` type
(default STRING), and applies
+ * the `ON EMPTY` / `ON ERROR` behavior when the path matches nothing or the
extraction/cast fails:
+ *
+ * - missing path -> ON EMPTY behavior
+ * - explicit JSON `null` -> SQL NULL
+ * - non-scalar (object/array) match -> ON ERROR behavior
+ * - malformed / non-single-value input -> ON ERROR behavior
+ * - scalar match, cast fails -> ON ERROR behavior
+ * - scalar match, cast succeeds -> the cast value
+ *
+ * Both clauses default to NULL per the standard. A `null` JSON input yields
SQL NULL directly, not
+ * the ON EMPTY/ERROR path.
+ *
+ * `emptyDefault` / `errorDefault` hold the `DEFAULT <expr>` expressions,
present only for the
+ * corresponding `Default` behavior. The child list is variable (0-2
defaults), so this extends
+ * `Expression` directly rather than `UnaryExpression`.
+ *
+ * {{{
+ * JSON_VALUE('{"id":7}', '$.id' RETURNING INT) -- 7
+ * JSON_VALUE('{"id":7}', '$.missing' DEFAULT -1 ON EMPTY) -- -1
+ * JSON_VALUE('{"a":{}}', '$.a' ERROR ON ERROR) --
raises (non-scalar)
+ * }}}
+ */
+// scalastyle:on line.size.limit
+case class JsonValue(
+ child: Expression,
+ path: String,
+ returning: DataType,
+ onEmpty: JsonValueBehavior,
+ onError: JsonValueBehavior,
+ emptyDefault: Option[Expression],
+ errorDefault: Option[Expression],
+ timeZoneId: Option[String] = None,
+ ansiEnabled: Boolean = SQLConf.get.ansiEnabled)
+ extends Expression
+ with TimeZoneAwareExpression
+ with CodegenFallback
+ with ExpectsInputTypes
+ with QueryErrorsBase {
+
+ override def nullable: Boolean = true
+
+ // Reuses the mutable `castInput` row across rows (see `castScalar`), so it
holds evaluation
+ // state. Interpreted execution must fresh-copy the expression before use,
or a shared instance
+ // could cast another concurrent evaluation's value; matches the neighboring
JSON expressions.
+ override def stateful: Boolean = true
+
+ // Children: the JSON input first, then whichever DEFAULT expressions are
present. The two
+ // defaults are resolved/coerced through the normal child machinery; their
cast to `returning`
+ // happens at eval time via `emptyDefaultCast` / `errorDefaultCast`.
+ override def children: Seq[Expression] =
+ child +: (emptyDefault.toSeq ++ errorDefault.toSeq)
+
+ // One entry per child: the JSON input must be STRING; the DEFAULT children
accept anything (they
+ // are cast to `returning` explicitly at eval). One entry per child is
required because the
+ // coercion rule zips `children` against `inputTypes` and rebuilds via
`withNewChildren`; a
+ // shorter list would truncate the zip and pass the wrong child count.
+ override def inputTypes: Seq[AbstractDataType] =
+ StringTypeWithCollation(supportsTrimCollation = true) +:
+ children.tail.map(_ => AnyDataType)
+
+ override def dataType: DataType = returning
+
+ override def withTimeZone(timeZoneId: String): TimeZoneAwareExpression =
+ copy(timeZoneId = Option(timeZoneId))
+
+ override def checkInputDataTypes(): TypeCheckResult = {
+ val inputCheck = super.checkInputDataTypes()
+ if (inputCheck.isFailure) {
+ inputCheck
+ } else if (!JsonPathParser.hasWildcard(path).contains(false)) {
+ // The path must parse and be wildcard-free (JSON_VALUE returns a single
scalar).
+ DataTypeMismatch(
+ errorSubClass = "INVALID_JSON_PATH",
+ messageParameters = Map(
+ "functionName" -> toSQLId(prettyName), "path" -> toSQLValue(path)))
+ } else if (!JsonValue.isValidReturningType(returning)) {
+ // RETURNING is restricted to scalar (atomic) types per ANSI 9075-2 6.28.
+ DataTypeMismatch(
+ errorSubClass = "INVALID_JSON_SCALAR_RETURNING_TYPE",
+ messageParameters = Map(
+ "functionName" -> toSQLId(prettyName), "returningType" ->
toSQLType(returning)))
+ } else {
+ // Each DEFAULT expression is cast to the RETURNING type at eval time
(see
+ // `emptyDefaultCast` / `errorDefaultCast`). Those casts are not
analyzed children, so an
+ // uncastable default (e.g. `DEFAULT array(1)` with `RETURNING INT`)
would otherwise slip
+ // past analysis and fail late only when its branch is taken. Surface
the cast's own type
+ // check here so it is rejected up front with the standard CAST_*
message.
+ (emptyDefaultCast ++ errorDefaultCast)
+ .map(_.checkInputDataTypes())
+ .find(_.isFailure)
+ .getOrElse(TypeCheckResult.TypeCheckSuccess)
+ }
+ }
+
+ // Eval mode for the user-provided DEFAULT expression casts: follows the
session ANSI setting like
+ // any ordinary value cast. The extracted-scalar cast is separate (see
`valueCast`).
+ @transient private lazy val defaultEvalMode =
EvalMode.fromBoolean(ansiEnabled)
+
+ // Path parsed once (the grammar makes it a string literal).
`checkInputDataTypes` guarantees it
+ // parses and is wildcard-free, so the evaluator is only built for a valid
path.
+ @transient private lazy val evaluator: JsonTableEvaluator =
+ JsonTableEvaluator(JsonPathParser.parse(path).getOrElse(Nil), explodeRoot
= false)
+
+ // Cast from the extracted scalar's STRING form to the RETURNING type, built
once over a reused
+ // input slot to avoid per-row allocation. Always an ANSI (throwing) cast,
independent of the
+ // session's ANSI setting, so a failed conversion always routes to ON ERROR
(see `eval`) rather
+ // than being silently turned into NULL by a non-ANSI session.
+ @transient private lazy val valueCast: Expression =
+ Cast(BoundReference(0, StringType, nullable = true), returning,
timeZoneId, EvalMode.ANSI)
+ @transient private lazy val castInput: GenericInternalRow = new
GenericInternalRow(1)
+
+ // Casts for the DEFAULT expressions to the RETURNING type (only built when
present).
+ @transient private lazy val emptyDefaultCast: Option[Expression] =
+ emptyDefault.map(e => Cast(e, returning, timeZoneId, defaultEvalMode))
+ @transient private lazy val errorDefaultCast: Option[Expression] =
+ errorDefault.map(e => Cast(e, returning, timeZoneId, defaultEvalMode))
+
+ private def castScalar(text: UTF8String): Any = {
+ castInput.update(0, text)
+ valueCast.eval(castInput)
+ }
+
+ // Handle the ON EMPTY case per the configured behavior.
+ private def onEmptyResult(input: InternalRow): Any = onEmpty match {
+ case JsonValueBehavior.Null => null
+ case JsonValueBehavior.Default => emptyDefaultCast.get.eval(input)
+ case JsonValueBehavior.Error =>
+ throw QueryExecutionErrors.jsonValueOnEmptyError(prettyName, path, cause
= null)
+ }
+
+ // Handle the ON ERROR case per the configured behavior. `cause` (if any) is
attached for context.
+ private def onErrorResult(input: InternalRow, cause: Throwable): Any =
onError match {
+ case JsonValueBehavior.Null => null
+ case JsonValueBehavior.Default => errorDefaultCast.get.eval(input)
+ case JsonValueBehavior.Error =>
+ throw QueryExecutionErrors.jsonValueOnErrorError(prettyName, path, cause)
+ }
+
+ override def eval(input: InternalRow): Any = {
+ val json = child.eval(input).asInstanceOf[UTF8String]
+ // NULL input propagates to NULL (not ON EMPTY / ON ERROR), matching ANSI
and the other engines.
+ if (json == null) return null
+ evaluator.lookup(json) match {
+ // Malformed / non-single-value input.
+ case None => onErrorResult(input, cause = null)
+ // Path matched nothing.
+ case Some(JsonValueLookup.Missing) => onEmptyResult(input)
+ // Matched an explicit JSON null: a present, scalar null value -> SQL
NULL.
+ case Some(JsonValueLookup.NullValue) => null
+ // Matched an object or array: not a scalar -> ON ERROR.
+ case Some(JsonValueLookup.NonScalar) => onErrorResult(input, cause =
null)
+ case Some(JsonValueLookup.Scalar(text)) =>
+ // `valueCast` throws on a failed conversion, which routes to ON ERROR.
+ try castScalar(text) catch { case e: Exception => onErrorResult(input,
e) }
+ }
+ }
+
+ override def prettyName: String = "json_value"
+
+ override def sql: String = {
+ val returningSQL = if (returning == StringType) "" else s" RETURNING
${returning.sql}"
+ def behaviorSQL(b: JsonValueBehavior, default: Option[Expression]): String
= b match {
+ case JsonValueBehavior.Null => "NULL"
+ case JsonValueBehavior.Error => "ERROR"
+ case JsonValueBehavior.Default => s"DEFAULT ${default.get.sql}"
+ }
+ val emptySQL = if (onEmpty == JsonValueBehavior.Null) ""
+ else s" ${behaviorSQL(onEmpty, emptyDefault)} ON EMPTY"
+ val errorSQL = if (onError == JsonValueBehavior.Null) ""
+ else s" ${behaviorSQL(onError, errorDefault)} ON ERROR"
+ // Render the path as a properly escaped string literal so bracket-quoted
paths such as
+ // `$['a']` (and any path containing a quote or backslash) round-trip as
valid SQL.
+ val pathSQL = Literal(UTF8String.fromString(path), StringType).sql
+ s"JSON_VALUE(${child.sql}, $pathSQL$returningSQL$emptySQL$errorSQL)"
+ }
+
+ override protected def withNewChildrenInternal(
+ newChildren: IndexedSeq[Expression]): JsonValue = {
+ // Rebuild the child list in the same order `children` produced it: json,
then the present
+ // defaults. `copy(child = ...)` alone would drop coercion applied to the
DEFAULT children.
+ var i = 1
+ val newEmpty = emptyDefault.map { _ => val e = newChildren(i); i += 1; e }
+ val newError = errorDefault.map { _ => val e = newChildren(i); i += 1; e }
+ copy(child = newChildren(0), emptyDefault = newEmpty, errorDefault =
newError)
+ }
+}
+
+object JsonValue {
+ /**
+ * ANSI (9075-2 6.28) restricts JSON_VALUE RETURNING to predefined scalar
types: string, numeric,
+ * boolean, and datetime. We allow exactly those families. Note this
deliberately excludes VARIANT
+ * (a Spark extension, deferred per the design's open question) and BINARY,
even though both are
+ * `AtomicType`s -- so an `AtomicType` check is not sufficient.
STRUCT/ARRAY/MAP are excluded as
+ * non-atomic. CHAR/VARCHAR are normalized to STRING by the parser before
reaching here.
+ */
+ def isValidReturningType(dt: DataType): Boolean = dt match {
+ case _: StringType => true
+ case _: NumericType => true
+ case BooleanType => true
+ case _: DatetimeType => true
+ case _ => false
+ }
+}
+
/**
* Converts an json input string to a [[StructType]], [[ArrayType]] or
[[MapType]]
* with the specified schema.
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
index 534f87c1e7eb..5febb00e7cd9 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
@@ -4155,6 +4155,37 @@ class AstBuilder extends DataTypeAstBuilder
}
}
+ /**
+ * Resolve a `jsonValueBehavior` clause (`NULL` / `ERROR` / `DEFAULT
<expr>`) into a
+ * [[JsonValueBehavior]] and, for the `DEFAULT` case, its expression.
+ */
+ private def buildJsonValueBehavior(
+ ctx: JsonValueBehaviorContext): (JsonValueBehavior, Option[Expression])
= ctx match {
+ case _: JsonValueBehaviorNullContext => (JsonValueBehavior.Null, None)
+ case _: JsonValueBehaviorErrorContext => (JsonValueBehavior.Error, None)
+ case d: JsonValueBehaviorDefaultContext =>
+ (JsonValueBehavior.Default, Some(expression(d.defaultExpr)))
+ }
+
+ /**
+ * Create a [[JsonValue]] expression for the SQL:2016 `JSON_VALUE` scalar
function. The `ON EMPTY`
+ * / `ON ERROR` clauses default to `NULL` when absent, per the standard.
+ */
+ override def visitJsonValue(ctx: JsonValueContext): Expression =
withOrigin(ctx) {
+ val jsonExpr = expression(ctx.jsonExpr)
+ val path = string(visitStringLit(ctx.path))
+ // Default RETURNING type is STRING. Normalize CHAR/VARCHAR to STRING for
the cast, as the value
+ // is produced by a `Cast` to the declared type (a raw CHAR/VARCHAR target
has no encoder).
+ val returning = Option(ctx.returning)
+ .map(dt =>
CharVarcharUtils.replaceCharVarcharWithStringForCast(typedVisit[DataType](dt)))
+ .getOrElse(StringType)
+ val (onEmpty, emptyDefault) = Option(ctx.emptyBehavior)
+ .map(buildJsonValueBehavior).getOrElse((JsonValueBehavior.Null, None))
+ val (onError, errorDefault) = Option(ctx.errorBehavior)
+ .map(buildJsonValueBehavior).getOrElse((JsonValueBehavior.Null, None))
+ JsonValue(jsonExpr, path, returning, onEmpty, onError, emptyDefault,
errorDefault)
+ }
+
/**
* Create a (windowed) Function expression.
*/
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
index 9ffeaf3c2466..42a718e132ce 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
@@ -1625,6 +1625,20 @@ private[sql] object QueryExecutionErrors extends
QueryErrorsBase with ExecutionE
cause = e)
}
+ def jsonValueOnEmptyError(functionName: String, path: String, cause:
Throwable): Throwable = {
+ new SparkRuntimeException(
+ errorClass = "JSON_VALUE_ON_ERROR.EMPTY",
+ messageParameters = Map("functionName" -> toSQLId(functionName), "path"
-> toSQLValue(path)),
+ cause = cause)
+ }
+
+ def jsonValueOnErrorError(functionName: String, path: String, cause:
Throwable): Throwable = {
+ new SparkRuntimeException(
+ errorClass = "JSON_VALUE_ON_ERROR.ERROR",
+ messageParameters = Map("functionName" -> toSQLId(functionName), "path"
-> toSQLValue(path)),
+ cause = cause)
+ }
+
def invalidKerberosConfigForHiveServer2Error(): Throwable = {
new SparkException(
errorClass = "_LEGACY_ERROR_TEMP_2179",
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/JsonExpressionsSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/JsonExpressionsSuite.scala
index fb8641659a74..d8d68ef75c10 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/JsonExpressionsSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/JsonExpressionsSuite.scala
@@ -1068,7 +1068,7 @@ class JsonExpressionsSuite extends SparkFunSuite with
ExpressionEvalHelper {
input)
}
- test("JsonToStructs, GetJsonObject, JsonTuple, MultiGetJsonObject are
stateful " +
+ test("JsonToStructs, GetJsonObject, JsonTuple, MultiGetJsonObject, JsonValue
are stateful " +
"and produce fresh copies") {
val schema = StructType(StructField("a", IntegerType) :: Nil)
val jsonToStructs = JsonToStructs(schema, Map.empty, Literal("{}"),
UTC_OPT)
@@ -1086,6 +1086,13 @@ class JsonExpressionsSuite extends SparkFunSuite with
ExpressionEvalHelper {
val multiGetJsonObject = MultiGetJsonObject(Literal("{}"), Seq("$.a",
"$.b"))
assert(multiGetJsonObject.stateful)
assert(multiGetJsonObject.freshCopyIfContainsStatefulExpression() ne
multiGetJsonObject)
+
+ // JsonValue reuses a mutable row to cast the extracted scalar, so it must
be stateful.
+ val jsonValue = JsonValue(
+ Literal("{}"), "$.a", StringType,
+ JsonValueBehavior.Null, JsonValueBehavior.Null, None, None)
+ assert(jsonValue.stateful)
+ assert(jsonValue.freshCopyIfContainsStatefulExpression() ne jsonValue)
}
}
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/ExpressionParserSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/ExpressionParserSuite.scala
index 3812dd287837..18a92a6496d3 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/ExpressionParserSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/ExpressionParserSuite.scala
@@ -307,6 +307,30 @@ class ExpressionParserSuite extends AnalysisTest {
assertEqual("-+~~a", -( +(~(~$"a"))))
}
+ test("JSON_VALUE expressions") {
+ import org.apache.spark.sql.catalyst.expressions.JsonValueBehavior
+ // Bare form: default STRING RETURNING, NULL ON EMPTY / NULL ON ERROR.
+ assertEqual(
+ "json_value(a, '$.b')",
+ JsonValue($"a", "$.b", StringType, JsonValueBehavior.Null,
JsonValueBehavior.Null,
+ None, None))
+ // RETURNING.
+ assertEqual(
+ "json_value(a, '$.b' RETURNING INT)",
+ JsonValue($"a", "$.b", IntegerType, JsonValueBehavior.Null,
JsonValueBehavior.Null,
+ None, None))
+ // ERROR ON EMPTY / ERROR ON ERROR.
+ assertEqual(
+ "json_value(a, '$.b' ERROR ON EMPTY ERROR ON ERROR)",
+ JsonValue($"a", "$.b", StringType, JsonValueBehavior.Error,
JsonValueBehavior.Error,
+ None, None))
+ // DEFAULT ON EMPTY / DEFAULT ON ERROR carry expressions.
+ assertEqual(
+ "json_value(a, '$.b' DEFAULT 'x' ON EMPTY DEFAULT 'y' ON ERROR)",
+ JsonValue($"a", "$.b", StringType, JsonValueBehavior.Default,
JsonValueBehavior.Default,
+ Some(Literal("x")), Some(Literal("y"))))
+ }
+
test("cast expressions") {
// Note that DataType parsing is tested elsewhere.
assertEqual("cast(a as int)", $"a".cast(IntegerType))
diff --git
a/sql/connect/client/jdbc/src/test/scala/org/apache/spark/sql/connect/client/jdbc/SparkConnectDatabaseMetaDataSuite.scala
b/sql/connect/client/jdbc/src/test/scala/org/apache/spark/sql/connect/client/jdbc/SparkConnectDatabaseMetaDataSuite.scala
index b305541893bb..b07f9da119a4 100644
---
a/sql/connect/client/jdbc/src/test/scala/org/apache/spark/sql/connect/client/jdbc/SparkConnectDatabaseMetaDataSuite.scala
+++
b/sql/connect/client/jdbc/src/test/scala/org/apache/spark/sql/connect/client/jdbc/SparkConnectDatabaseMetaDataSuite.scala
@@ -210,7 +210,7 @@ class SparkConnectDatabaseMetaDataSuite extends
ConnectFunSuite with RemoteSpark
val metadata = conn.getMetaData
// scalastyle:off line.size.limit
// CURRENT_PATH and SYSTEM are excluded: getSQLKeywords drops SQL:2003
reserved words (see companion).
- assert(metadata.getSQLKeywords ===
"ADD,AFTER,AGGREGATE,ALIGN,ALWAYS,ANALYZE,ANTI,ANY_VALUE,APPLY,APPROX,ARCHIVE,ASC,ASOF,AUTO,BERNOULLI,BIN,BINDING,BIN_DISTRIBUTE_RATIO,BIN_END,BIN_START,BUCKET,BUCKETS,BYTE,CACHE,CASCADE,CATALOG,CATALOGS,CDC,CHANGE,CHANGES,CLEAR,CLUSTER,CLUSTERED,CODEGEN,COLLATION,COLLATIONS,COLLECTION,COLUMNS,COMMENT,COMPACT,COMPACTIONS,COMPENSATION,COMPUTE,CONCATENATE,CONTAINS,CONTINUE,COST,CURRENT_DATABASE,CURRENT_SCHEMA,DATA,DATABASE,DATABASES,DATEADD,DATEDIFF
[...]
+ assert(metadata.getSQLKeywords ===
"ADD,AFTER,AGGREGATE,ALIGN,ALWAYS,ANALYZE,ANTI,ANY_VALUE,APPLY,APPROX,ARCHIVE,ASC,ASOF,AUTO,BERNOULLI,BIN,BINDING,BIN_DISTRIBUTE_RATIO,BIN_END,BIN_START,BUCKET,BUCKETS,BYTE,CACHE,CASCADE,CATALOG,CATALOGS,CDC,CHANGE,CHANGES,CLEAR,CLUSTER,CLUSTERED,CODEGEN,COLLATION,COLLATIONS,COLLECTION,COLUMNS,COMMENT,COMPACT,COMPACTIONS,COMPENSATION,COMPUTE,CONCATENATE,CONTAINS,CONTINUE,COST,CURRENT_DATABASE,CURRENT_SCHEMA,DATA,DATABASE,DATABASES,DATEADD,DATEDIFF
[...]
// scalastyle:on line.size.limit
}
}
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/json-functions.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/json-functions.sql.out
index 0582c531d570..9b33afd10db8 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/json-functions.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/json-functions.sql.out
@@ -1145,3 +1145,216 @@ GlobalLimit 1
+- LocalLimit 1
+- Project [from_json(StructField(time,TimeType(6),true), {"time":
"14:30:45"}, Some(America/Los_Angeles), false) AS from_json({"time":
"14:30:45"})#x]
+- OneRowRelation
+
+
+-- !query
+select
json_value('{"id":7,"name":"Ada","tags":["x","y"],"addr":{"city":"NYC"},"score":null}',
'$.name')
+-- !query analysis
+Project
[json_value({"id":7,"name":"Ada","tags":["x","y"],"addr":{"city":"NYC"},"score":null},
$.name, StringType, Null, Null, None, None, Some(America/Los_Angeles), true)
AS
JSON_VALUE({"id":7,"name":"Ada","tags":["x","y"],"addr":{"city":"NYC"},"score":null},
'$.name')#x]
++- OneRowRelation
+
+
+-- !query
+select
json_value('{"id":7,"name":"Ada","tags":["x","y"],"addr":{"city":"NYC"},"score":null}',
'$.id' RETURNING INT)
+-- !query analysis
+Project
[json_value({"id":7,"name":"Ada","tags":["x","y"],"addr":{"city":"NYC"},"score":null},
$.id, IntegerType, Null, Null, None, None, Some(America/Los_Angeles), true) AS
JSON_VALUE({"id":7,"name":"Ada","tags":["x","y"],"addr":{"city":"NYC"},"score":null},
'$.id' RETURNING INT)#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"id":7,"name":"Ada"}', '$.id' RETURNING INT) + 1
+-- !query analysis
+Project [(json_value({"id":7,"name":"Ada"}, $.id, IntegerType, Null, Null,
None, None, Some(America/Los_Angeles), true) + 1) AS
(JSON_VALUE({"id":7,"name":"Ada"}, '$.id' RETURNING INT) + 1)#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"score":null}', '$.score')
+-- !query analysis
+Project [json_value({"score":null}, $.score, StringType, Null, Null, None,
None, Some(America/Los_Angeles), true) AS JSON_VALUE({"score":null},
'$.score')#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"addr":{"city":"NYC"}}', '$.addr')
+-- !query analysis
+Project [json_value({"addr":{"city":"NYC"}}, $.addr, StringType, Null, Null,
None, None, Some(America/Los_Angeles), true) AS
JSON_VALUE({"addr":{"city":"NYC"}}, '$.addr')#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"tags":["x","y"]}', '$.tags')
+-- !query analysis
+Project [json_value({"tags":["x","y"]}, $.tags, StringType, Null, Null, None,
None, Some(America/Los_Angeles), true) AS JSON_VALUE({"tags":["x","y"]},
'$.tags')#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"id":7}', '$.missing')
+-- !query analysis
+Project [json_value({"id":7}, $.missing, StringType, Null, Null, None, None,
Some(America/Los_Angeles), true) AS JSON_VALUE({"id":7}, '$.missing')#x]
++- OneRowRelation
+
+
+-- !query
+select json_value(cast(null as string), '$.a')
+-- !query analysis
+Project [json_value(cast(null as string), $.a, StringType, Null, Null, None,
None, Some(America/Los_Angeles), true) AS JSON_VALUE(CAST(NULL AS STRING),
'$.a')#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"id":7}', '$.missing' DEFAULT '?' ON EMPTY)
+-- !query analysis
+Project [json_value({"id":7}, $.missing, StringType, Default, Null, Some(?),
None, Some(America/Los_Angeles), true) AS JSON_VALUE({"id":7}, '$.missing'
DEFAULT ? ON EMPTY)#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"id":7}', '$.missing' RETURNING INT DEFAULT 42 ON EMPTY)
+-- !query analysis
+Project [json_value({"id":7}, $.missing, IntegerType, Default, Null, Some(42),
None, Some(America/Los_Angeles), true) AS JSON_VALUE({"id":7}, '$.missing'
RETURNING INT DEFAULT 42 ON EMPTY)#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"id":7}', '$.missing' ERROR ON EMPTY)
+-- !query analysis
+Project [json_value({"id":7}, $.missing, StringType, Error, Null, None, None,
Some(America/Los_Angeles), true) AS JSON_VALUE({"id":7}, '$.missing' ERROR ON
EMPTY)#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"addr":{"city":"NYC"}}', '$.addr' DEFAULT 'n/a' ON ERROR)
+-- !query analysis
+Project [json_value({"addr":{"city":"NYC"}}, $.addr, StringType, Null,
Default, None, Some(n/a), Some(America/Los_Angeles), true) AS
JSON_VALUE({"addr":{"city":"NYC"}}, '$.addr' DEFAULT n/a ON ERROR)#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('not json', '$.a' DEFAULT 'bad' ON ERROR)
+-- !query analysis
+Project [json_value(not json, $.a, StringType, Null, Default, None, Some(bad),
Some(America/Los_Angeles), true) AS JSON_VALUE(not json, '$.a' DEFAULT bad ON
ERROR)#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('not json', '$.a' ERROR ON ERROR)
+-- !query analysis
+Project [json_value(not json, $.a, StringType, Null, Error, None, None,
Some(America/Los_Angeles), true) AS JSON_VALUE(not json, '$.a' ERROR ON
ERROR)#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"name":"Ada"}', '$.name' RETURNING INT)
+-- !query analysis
+Project [json_value({"name":"Ada"}, $.name, IntegerType, Null, Null, None,
None, Some(America/Los_Angeles), true) AS JSON_VALUE({"name":"Ada"}, '$.name'
RETURNING INT)#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"name":"Ada"}', '$.name' RETURNING INT ERROR ON ERROR)
+-- !query analysis
+Project [json_value({"name":"Ada"}, $.name, IntegerType, Null, Error, None,
None, Some(America/Los_Angeles), true) AS JSON_VALUE({"name":"Ada"}, '$.name'
RETURNING INT ERROR ON ERROR)#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"name":"Ada"}', '$.name' RETURNING INT DEFAULT -1 ON ERROR)
+-- !query analysis
+Project [json_value({"name":"Ada"}, $.name, IntegerType, Null, Default, None,
Some(-1), Some(America/Los_Angeles), true) AS JSON_VALUE({"name":"Ada"},
'$.name' RETURNING INT DEFAULT -1 ON ERROR)#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"a":"x"}', '$.b' DEFAULT 'e' ON EMPTY DEFAULT 'r' ON ERROR)
+-- !query analysis
+Project [json_value({"a":"x"}, $.b, StringType, Default, Default, Some(e),
Some(r), Some(America/Los_Angeles), true) AS JSON_VALUE({"a":"x"}, '$.b'
DEFAULT e ON EMPTY DEFAULT r ON ERROR)#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"v":"3.14"}', '$.v' RETURNING DOUBLE)
+-- !query analysis
+Project [json_value({"v":"3.14"}, $.v, DoubleType, Null, Null, None, None,
Some(America/Los_Angeles), true) AS JSON_VALUE({"v":"3.14"}, '$.v' RETURNING
DOUBLE)#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"v":"true"}', '$.v' RETURNING BOOLEAN)
+-- !query analysis
+Project [json_value({"v":"true"}, $.v, BooleanType, Null, Null, None, None,
Some(America/Los_Angeles), true) AS JSON_VALUE({"v":"true"}, '$.v' RETURNING
BOOLEAN)#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"v":"2020-01-02"}', '$.v' RETURNING DATE)
+-- !query analysis
+Project [json_value({"v":"2020-01-02"}, $.v, DateType, Null, Null, None, None,
Some(America/Los_Angeles), true) AS JSON_VALUE({"v":"2020-01-02"}, '$.v'
RETURNING DATE)#x]
++- OneRowRelation
+
+
+-- !query
+select json_value('{"a":[1,2]}', '$.a[*]')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.INVALID_JSON_PATH",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "functionName" : "`json_value`",
+ "path" : "'$.a[*]'",
+ "sqlExpr" : "\"JSON_VALUE({\"a\":[1,2]}, '$.a[*]')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 42,
+ "fragment" : "json_value('{\"a\":[1,2]}', '$.a[*]')"
+ } ]
+}
+
+
+-- !query
+select json_value('{"a":1}', '$.a' RETURNING STRUCT<x:INT>)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.INVALID_JSON_SCALAR_RETURNING_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "functionName" : "`json_value`",
+ "returningType" : "\"STRUCT<x: INT>\"",
+ "sqlExpr" : "\"JSON_VALUE({\"a\":1}, '$.a' RETURNING STRUCT<x: INT>)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 59,
+ "fragment" : "json_value('{\"a\":1}', '$.a' RETURNING STRUCT<x:INT>)"
+ } ]
+}
+
+
+-- !query
+select json_value('{}', '$.x' RETURNING INT DEFAULT array(1) ON EMPTY)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.CAST_WITHOUT_SUGGESTION",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "sqlExpr" : "\"JSON_VALUE({}, '$.x' RETURNING INT DEFAULT array(1) ON
EMPTY)\"",
+ "srcType" : "\"ARRAY<INT>\"",
+ "targetType" : "\"INT\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 70,
+ "fragment" : "json_value('{}', '$.x' RETURNING INT DEFAULT array(1) ON
EMPTY)"
+ } ]
+}
diff --git a/sql/core/src/test/resources/sql-tests/inputs/json-functions.sql
b/sql/core/src/test/resources/sql-tests/inputs/json-functions.sql
index 2a1fae4a6b45..fa1cb6628ec9 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/json-functions.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/json-functions.sql
@@ -173,3 +173,41 @@ select to_json(from_json('{"time":"23:59:59.999999"}',
'time TIME(6)'));
select schema_of_json('{"time": "14:30:45"}');
select schema_of_json('{"time": "14:30:45.123456"}');
select from_json('{"time": "14:30:45"}', 'time TIME') LIMIT 1;
+
+-- JSON_VALUE: extract a scalar value (ANSI SQL:2016)
+select
json_value('{"id":7,"name":"Ada","tags":["x","y"],"addr":{"city":"NYC"},"score":null}',
'$.name');
+select
json_value('{"id":7,"name":"Ada","tags":["x","y"],"addr":{"city":"NYC"},"score":null}',
'$.id' RETURNING INT);
+select json_value('{"id":7,"name":"Ada"}', '$.id' RETURNING INT) + 1;
+-- present but JSON null -> SQL NULL
+select json_value('{"score":null}', '$.score');
+-- non-scalar (object / array) -> NULL ON ERROR (default)
+select json_value('{"addr":{"city":"NYC"}}', '$.addr');
+select json_value('{"tags":["x","y"]}', '$.tags');
+-- missing path -> NULL ON EMPTY (default)
+select json_value('{"id":7}', '$.missing');
+-- NULL input propagates to NULL
+select json_value(cast(null as string), '$.a');
+-- ON EMPTY behaviors
+select json_value('{"id":7}', '$.missing' DEFAULT '?' ON EMPTY);
+select json_value('{"id":7}', '$.missing' RETURNING INT DEFAULT 42 ON EMPTY);
+select json_value('{"id":7}', '$.missing' ERROR ON EMPTY);
+-- ON ERROR behaviors
+select json_value('{"addr":{"city":"NYC"}}', '$.addr' DEFAULT 'n/a' ON ERROR);
+select json_value('not json', '$.a' DEFAULT 'bad' ON ERROR);
+select json_value('not json', '$.a' ERROR ON ERROR);
+-- failed cast -> ON ERROR
+select json_value('{"name":"Ada"}', '$.name' RETURNING INT);
+select json_value('{"name":"Ada"}', '$.name' RETURNING INT ERROR ON ERROR);
+select json_value('{"name":"Ada"}', '$.name' RETURNING INT DEFAULT -1 ON
ERROR);
+-- combined ON EMPTY + ON ERROR
+select json_value('{"a":"x"}', '$.b' DEFAULT 'e' ON EMPTY DEFAULT 'r' ON
ERROR);
+-- RETURNING types
+select json_value('{"v":"3.14"}', '$.v' RETURNING DOUBLE);
+select json_value('{"v":"true"}', '$.v' RETURNING BOOLEAN);
+select json_value('{"v":"2020-01-02"}', '$.v' RETURNING DATE);
+-- invalid: wildcard path
+select json_value('{"a":[1,2]}', '$.a[*]');
+-- invalid: non-scalar RETURNING type
+select json_value('{"a":1}', '$.a' RETURNING STRUCT<x:INT>);
+-- invalid: a DEFAULT that cannot cast to the RETURNING type
+select json_value('{}', '$.x' RETURNING INT DEFAULT array(1) ON EMPTY);
diff --git
a/sql/core/src/test/resources/sql-tests/results/json-functions.sql.out
b/sql/core/src/test/resources/sql-tests/results/json-functions.sql.out
index adfe51d05bca..6656a7723cd1 100644
--- a/sql/core/src/test/resources/sql-tests/results/json-functions.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/json-functions.sql.out
@@ -1282,3 +1282,267 @@ select from_json('{"time": "14:30:45"}', 'time TIME')
LIMIT 1
struct<from_json({"time": "14:30:45"}):struct<time:time(6)>>
-- !query output
{"time":14:30:45}
+
+
+-- !query
+select
json_value('{"id":7,"name":"Ada","tags":["x","y"],"addr":{"city":"NYC"},"score":null}',
'$.name')
+-- !query schema
+struct<JSON_VALUE({"id":7,"name":"Ada","tags":["x","y"],"addr":{"city":"NYC"},"score":null},
'$.name'):string>
+-- !query output
+Ada
+
+
+-- !query
+select
json_value('{"id":7,"name":"Ada","tags":["x","y"],"addr":{"city":"NYC"},"score":null}',
'$.id' RETURNING INT)
+-- !query schema
+struct<JSON_VALUE({"id":7,"name":"Ada","tags":["x","y"],"addr":{"city":"NYC"},"score":null},
'$.id' RETURNING INT):int>
+-- !query output
+7
+
+
+-- !query
+select json_value('{"id":7,"name":"Ada"}', '$.id' RETURNING INT) + 1
+-- !query schema
+struct<(JSON_VALUE({"id":7,"name":"Ada"}, '$.id' RETURNING INT) + 1):int>
+-- !query output
+8
+
+
+-- !query
+select json_value('{"score":null}', '$.score')
+-- !query schema
+struct<JSON_VALUE({"score":null}, '$.score'):string>
+-- !query output
+NULL
+
+
+-- !query
+select json_value('{"addr":{"city":"NYC"}}', '$.addr')
+-- !query schema
+struct<JSON_VALUE({"addr":{"city":"NYC"}}, '$.addr'):string>
+-- !query output
+NULL
+
+
+-- !query
+select json_value('{"tags":["x","y"]}', '$.tags')
+-- !query schema
+struct<JSON_VALUE({"tags":["x","y"]}, '$.tags'):string>
+-- !query output
+NULL
+
+
+-- !query
+select json_value('{"id":7}', '$.missing')
+-- !query schema
+struct<JSON_VALUE({"id":7}, '$.missing'):string>
+-- !query output
+NULL
+
+
+-- !query
+select json_value(cast(null as string), '$.a')
+-- !query schema
+struct<JSON_VALUE(CAST(NULL AS STRING), '$.a'):string>
+-- !query output
+NULL
+
+
+-- !query
+select json_value('{"id":7}', '$.missing' DEFAULT '?' ON EMPTY)
+-- !query schema
+struct<JSON_VALUE({"id":7}, '$.missing' DEFAULT ? ON EMPTY):string>
+-- !query output
+?
+
+
+-- !query
+select json_value('{"id":7}', '$.missing' RETURNING INT DEFAULT 42 ON EMPTY)
+-- !query schema
+struct<JSON_VALUE({"id":7}, '$.missing' RETURNING INT DEFAULT 42 ON EMPTY):int>
+-- !query output
+42
+
+
+-- !query
+select json_value('{"id":7}', '$.missing' ERROR ON EMPTY)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkRuntimeException
+{
+ "errorClass" : "JSON_VALUE_ON_ERROR.EMPTY",
+ "sqlState" : "2203G",
+ "messageParameters" : {
+ "functionName" : "`json_value`",
+ "path" : "'$.missing'"
+ }
+}
+
+
+-- !query
+select json_value('{"addr":{"city":"NYC"}}', '$.addr' DEFAULT 'n/a' ON ERROR)
+-- !query schema
+struct<JSON_VALUE({"addr":{"city":"NYC"}}, '$.addr' DEFAULT n/a ON
ERROR):string>
+-- !query output
+n/a
+
+
+-- !query
+select json_value('not json', '$.a' DEFAULT 'bad' ON ERROR)
+-- !query schema
+struct<JSON_VALUE(not json, '$.a' DEFAULT bad ON ERROR):string>
+-- !query output
+bad
+
+
+-- !query
+select json_value('not json', '$.a' ERROR ON ERROR)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkRuntimeException
+{
+ "errorClass" : "JSON_VALUE_ON_ERROR.ERROR",
+ "sqlState" : "2203G",
+ "messageParameters" : {
+ "functionName" : "`json_value`",
+ "path" : "'$.a'"
+ }
+}
+
+
+-- !query
+select json_value('{"name":"Ada"}', '$.name' RETURNING INT)
+-- !query schema
+struct<JSON_VALUE({"name":"Ada"}, '$.name' RETURNING INT):int>
+-- !query output
+NULL
+
+
+-- !query
+select json_value('{"name":"Ada"}', '$.name' RETURNING INT ERROR ON ERROR)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkRuntimeException
+{
+ "errorClass" : "JSON_VALUE_ON_ERROR.ERROR",
+ "sqlState" : "2203G",
+ "messageParameters" : {
+ "functionName" : "`json_value`",
+ "path" : "'$.name'"
+ }
+}
+
+
+-- !query
+select json_value('{"name":"Ada"}', '$.name' RETURNING INT DEFAULT -1 ON ERROR)
+-- !query schema
+struct<JSON_VALUE({"name":"Ada"}, '$.name' RETURNING INT DEFAULT -1 ON
ERROR):int>
+-- !query output
+-1
+
+
+-- !query
+select json_value('{"a":"x"}', '$.b' DEFAULT 'e' ON EMPTY DEFAULT 'r' ON ERROR)
+-- !query schema
+struct<JSON_VALUE({"a":"x"}, '$.b' DEFAULT e ON EMPTY DEFAULT r ON
ERROR):string>
+-- !query output
+e
+
+
+-- !query
+select json_value('{"v":"3.14"}', '$.v' RETURNING DOUBLE)
+-- !query schema
+struct<JSON_VALUE({"v":"3.14"}, '$.v' RETURNING DOUBLE):double>
+-- !query output
+3.14
+
+
+-- !query
+select json_value('{"v":"true"}', '$.v' RETURNING BOOLEAN)
+-- !query schema
+struct<JSON_VALUE({"v":"true"}, '$.v' RETURNING BOOLEAN):boolean>
+-- !query output
+true
+
+
+-- !query
+select json_value('{"v":"2020-01-02"}', '$.v' RETURNING DATE)
+-- !query schema
+struct<JSON_VALUE({"v":"2020-01-02"}, '$.v' RETURNING DATE):date>
+-- !query output
+2020-01-02
+
+
+-- !query
+select json_value('{"a":[1,2]}', '$.a[*]')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.INVALID_JSON_PATH",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "functionName" : "`json_value`",
+ "path" : "'$.a[*]'",
+ "sqlExpr" : "\"JSON_VALUE({\"a\":[1,2]}, '$.a[*]')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 42,
+ "fragment" : "json_value('{\"a\":[1,2]}', '$.a[*]')"
+ } ]
+}
+
+
+-- !query
+select json_value('{"a":1}', '$.a' RETURNING STRUCT<x:INT>)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.INVALID_JSON_SCALAR_RETURNING_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "functionName" : "`json_value`",
+ "returningType" : "\"STRUCT<x: INT>\"",
+ "sqlExpr" : "\"JSON_VALUE({\"a\":1}, '$.a' RETURNING STRUCT<x: INT>)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 59,
+ "fragment" : "json_value('{\"a\":1}', '$.a' RETURNING STRUCT<x:INT>)"
+ } ]
+}
+
+
+-- !query
+select json_value('{}', '$.x' RETURNING INT DEFAULT array(1) ON EMPTY)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.CAST_WITHOUT_SUGGESTION",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "sqlExpr" : "\"JSON_VALUE({}, '$.x' RETURNING INT DEFAULT array(1) ON
EMPTY)\"",
+ "srcType" : "\"ARRAY<INT>\"",
+ "targetType" : "\"INT\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 70,
+ "fragment" : "json_value('{}', '$.x' RETURNING INT DEFAULT array(1) ON
EMPTY)"
+ } ]
+}
diff --git
a/sql/core/src/test/resources/sql-tests/results/keywords-enforced.sql.out
b/sql/core/src/test/resources/sql-tests/results/keywords-enforced.sql.out
index fe26fef775ca..37f174acfe53 100644
--- a/sql/core/src/test/resources/sql-tests/results/keywords-enforced.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/keywords-enforced.sql.out
@@ -130,6 +130,7 @@ DOUBLE false
DROP false
ELSE true
ELSEIF false
+EMPTY false
END true
ENFORCED false
ERROR false
@@ -212,6 +213,7 @@ ITERATE false
JOIN true
JSON false
JSON_TABLE false
+JSON_VALUE false
KEY false
KEYS false
LANGUAGE false
@@ -327,6 +329,7 @@ RESET false
RESPECT false
RESTRICT false
RETURN false
+RETURNING false
RETURNS false
REVOKE false
RIGHT true
diff --git a/sql/core/src/test/resources/sql-tests/results/keywords.sql.out
b/sql/core/src/test/resources/sql-tests/results/keywords.sql.out
index d23be26a60dd..f6b910a54c3a 100644
--- a/sql/core/src/test/resources/sql-tests/results/keywords.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/keywords.sql.out
@@ -130,6 +130,7 @@ DOUBLE false
DROP false
ELSE false
ELSEIF false
+EMPTY false
END false
ENFORCED false
ERROR false
@@ -212,6 +213,7 @@ ITERATE false
JOIN false
JSON false
JSON_TABLE false
+JSON_VALUE false
KEY false
KEYS false
LANGUAGE false
@@ -327,6 +329,7 @@ RESET false
RESPECT false
RESTRICT false
RETURN false
+RETURNING false
RETURNS false
REVOKE false
RIGHT false
diff --git
a/sql/core/src/test/resources/sql-tests/results/nonansi/keywords.sql.out
b/sql/core/src/test/resources/sql-tests/results/nonansi/keywords.sql.out
index d23be26a60dd..f6b910a54c3a 100644
--- a/sql/core/src/test/resources/sql-tests/results/nonansi/keywords.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/nonansi/keywords.sql.out
@@ -130,6 +130,7 @@ DOUBLE false
DROP false
ELSE false
ELSEIF false
+EMPTY false
END false
ENFORCED false
ERROR false
@@ -212,6 +213,7 @@ ITERATE false
JOIN false
JSON false
JSON_TABLE false
+JSON_VALUE false
KEY false
KEYS false
LANGUAGE false
@@ -327,6 +329,7 @@ RESET false
RESPECT false
RESTRICT false
RETURN false
+RETURNING false
RETURNS false
REVOKE false
RIGHT false
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/JsonValueSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/JsonValueSuite.scala
new file mode 100644
index 000000000000..d3106ac33ddb
--- /dev/null
+++ b/sql/core/src/test/scala/org/apache/spark/sql/JsonValueSuite.scala
@@ -0,0 +1,263 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.spark.sql
+
+import org.apache.spark.SparkRuntimeException
+import org.apache.spark.sql.catalyst.expressions.JsonValue
+import org.apache.spark.sql.internal.SQLConf
+import org.apache.spark.sql.test.SharedSparkSession
+import org.apache.spark.sql.types.{IntegerType, StringType}
+
+/**
+ * End-to-end tests for the SQL:2016 `JSON_VALUE` scalar function.
+ */
+class JsonValueSuite extends QueryTest with SharedSparkSession {
+ import testImplicits._
+
+ private val doc =
+
"""{"id":7,"name":"Ada","tags":["x","y"],"addr":{"city":"NYC"},"score":null,"f":"3.14"}"""
+
+ test("extract a scalar value as STRING by default") {
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.name')"), Row("Ada"))
+ // Numbers and booleans come back as their JSON text under the default
STRING RETURNING.
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.id')"), Row("7"))
+ }
+
+ test("RETURNING casts the scalar to the requested type") {
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.id' RETURNING INT)"),
Row(7))
+ assert(sql(s"SELECT json_value('$doc', '$$.id' RETURNING
INT)").schema.head.dataType
+ === IntegerType)
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.f' RETURNING DOUBLE)"),
Row(3.14d))
+ checkAnswer(sql("SELECT json_value('{\"v\":\"true\"}', '$.v' RETURNING
BOOLEAN)"), Row(true))
+ checkAnswer(
+ sql("SELECT json_value('{\"v\":\"2020-01-02\"}', '$.v' RETURNING DATE)"),
+ Row(java.sql.Date.valueOf("2020-01-02")))
+ }
+
+ test("default RETURNING type is STRING") {
+ assert(sql(s"SELECT json_value('$doc', '$$.name')").schema.head.dataType
=== StringType)
+ }
+
+ test("a raw JSON number keeps its exact source digits (no double rounding)")
{
+ // The matched scalar is read straight from the parser, so a fraction with
more digits than a
+ // double can represent reaches the DECIMAL cast (and the default STRING
form) intact.
+ val big = """{"v":0.123456789012345678}"""
+ checkAnswer(
+ sql(s"SELECT json_value('$big', '$$.v' RETURNING DECIMAL(38,18))"),
+ Row(new java.math.BigDecimal("0.123456789012345678")))
+ checkAnswer(sql(s"SELECT json_value('$big', '$$.v')"),
Row("0.123456789012345678"))
+ }
+
+ test("a present JSON null yields SQL NULL") {
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.score')"), Row(null))
+ }
+
+ test("a non-scalar (object/array) match is an ON ERROR case, NULL by
default") {
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.addr')"), Row(null))
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.tags')"), Row(null))
+ }
+
+ test("a missing path is an ON EMPTY case, NULL by default") {
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.missing')"), Row(null))
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.addr.zip')"), Row(null))
+ }
+
+ test("NULL JSON input propagates to NULL (not ON EMPTY / ON ERROR)") {
+ checkAnswer(sql("SELECT json_value(CAST(NULL AS STRING), '$.a' ERROR ON
EMPTY ERROR ON ERROR)"),
+ Row(null))
+ }
+
+ test("DEFAULT ON EMPTY") {
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.missing' DEFAULT '?' ON
EMPTY)"), Row("?"))
+ checkAnswer(
+ sql(s"SELECT json_value('$doc', '$$.missing' RETURNING INT DEFAULT 42 ON
EMPTY)"), Row(42))
+ }
+
+ test("ERROR ON EMPTY raises for a missing path") {
+ val e = intercept[SparkRuntimeException] {
+ sql(s"SELECT json_value('$doc', '$$.missing' ERROR ON EMPTY)").collect()
+ }
+ assert(e.getCondition == "JSON_VALUE_ON_ERROR.EMPTY")
+ }
+
+ test("DEFAULT ON ERROR for a non-scalar match and for malformed input") {
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.addr' DEFAULT 'n/a' ON
ERROR)"), Row("n/a"))
+ checkAnswer(sql("SELECT json_value('not json', '$.a' DEFAULT 'bad' ON
ERROR)"), Row("bad"))
+ }
+
+ test("ERROR ON ERROR raises for malformed input") {
+ val e = intercept[SparkRuntimeException] {
+ sql("SELECT json_value('not json', '$.a' ERROR ON ERROR)").collect()
+ }
+ assert(e.getCondition == "JSON_VALUE_ON_ERROR.ERROR")
+ }
+
+ test("a valid JSON prefix followed by trailing content is an ON ERROR case")
{
+ // The whole input must be a single well-formed JSON value: a value that
parses but is trailed
+ // by garbage (or a second root value) is malformed, even when the path
matches within the
+ // prefix. NULL ON ERROR by default; ERROR ON ERROR raises.
+ checkAnswer(sql("""SELECT json_value('{"a":1} trailing', '$.a')"""),
Row(null))
+ checkAnswer(sql("""SELECT json_value('{"a":1}{"a":2}', '$.a')"""),
Row(null))
+ val e = intercept[SparkRuntimeException] {
+ sql("""SELECT json_value('{"a":1} trailing', '$.a' ERROR ON
ERROR)""").collect()
+ }
+ assert(e.getCondition == "JSON_VALUE_ON_ERROR.ERROR")
+ }
+
+ test("ERROR ON ERROR raises for a non-scalar match") {
+ val e = intercept[SparkRuntimeException] {
+ sql(s"SELECT json_value('$doc', '$$.addr' ERROR ON ERROR)").collect()
+ }
+ assert(e.getCondition == "JSON_VALUE_ON_ERROR.ERROR")
+ }
+
+ test("a failed cast is an ON ERROR case") {
+ // NULL ON ERROR default.
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.name' RETURNING INT)"),
Row(null))
+ // DEFAULT ON ERROR.
+ checkAnswer(
+ sql(s"SELECT json_value('$doc', '$$.name' RETURNING INT DEFAULT -1 ON
ERROR)"), Row(-1))
+ // ERROR ON ERROR.
+ val e = intercept[SparkRuntimeException] {
+ sql(s"SELECT json_value('$doc', '$$.name' RETURNING INT ERROR ON
ERROR)").collect()
+ }
+ assert(e.getCondition == "JSON_VALUE_ON_ERROR.ERROR")
+ }
+
+ test("independent ON EMPTY and ON ERROR behaviors") {
+ // Missing path -> ON EMPTY branch; malformed input / non-scalar value ->
ON ERROR branch.
+ checkAnswer(
+ sql(s"SELECT json_value('$doc', '$$.missing' DEFAULT 'e' ON EMPTY
DEFAULT 'r' ON ERROR)"),
+ Row("e"))
+ checkAnswer(
+ sql(s"SELECT json_value('$doc', '$$.addr' DEFAULT 'e' ON EMPTY DEFAULT
'r' ON ERROR)"),
+ Row("r"))
+ }
+
+ test("ON ERROR governs a failed cast in BOTH ANSI and non-ANSI mode") {
+ // The extracted-scalar cast is always an ANSI (throwing) cast, so a bad
conversion routes to ON
+ // ERROR identically regardless of the session's ANSI setting -- a
non-ANSI session must not
+ // silently return NULL and bypass DEFAULT / ERROR ON ERROR. (A
NULL-ON-ERROR-only check would
+ // pass vacuously in non-ANSI mode, where a lenient cast already yields
NULL.)
+ Seq("true", "false").foreach { ansi =>
+ withSQLConf(SQLConf.ANSI_ENABLED.key -> ansi) {
+ withClue(s"ansi=$ansi ") {
+ // A valid conversion still succeeds in both modes (the ANSI cast is
not stricter here).
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.id' RETURNING
INT)"), Row(7))
+ // NULL ON ERROR (default).
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.name' RETURNING
INT)"), Row(null))
+ // DEFAULT ON ERROR.
+ checkAnswer(
+ sql(s"SELECT json_value('$doc', '$$.name' RETURNING INT DEFAULT -1
ON ERROR)"),
+ Row(-1))
+ // ERROR ON ERROR.
+ val e = intercept[SparkRuntimeException] {
+ sql(s"SELECT json_value('$doc', '$$.name' RETURNING INT ERROR ON
ERROR)").collect()
+ }
+ assert(e.getCondition == "JSON_VALUE_ON_ERROR.ERROR")
+ }
+ }
+ }
+ }
+
+ test("works over a column of JSON documents") {
+ withTempView("docs") {
+ Seq(
+ (1, """{"a":10}"""),
+ (2, """{"a":20}"""),
+ (3, """{"b":30}"""), // missing -> NULL ON EMPTY
+ (4, "not json")) // malformed -> NULL ON ERROR
+ .toDF("k", "j").createOrReplaceTempView("docs")
+ checkAnswer(
+ sql("SELECT k, json_value(j, '$.a' RETURNING INT) AS a FROM docs ORDER
BY k"),
+ Seq(Row(1, 10), Row(2, 20), Row(3, null), Row(4, null)))
+ }
+ }
+
+ test("invalid: wildcard path is rejected at analysis") {
+ val e = intercept[AnalysisException] {
+ sql(s"SELECT json_value('$doc', '$$.tags[*]')").collect()
+ }
+ assert(e.getCondition == "DATATYPE_MISMATCH.INVALID_JSON_PATH")
+ }
+
+ test("invalid: non-scalar RETURNING type is rejected at analysis") {
+ val e = intercept[AnalysisException] {
+ sql(s"SELECT json_value('$doc', '$$.addr' RETURNING
STRUCT<x:INT>)").collect()
+ }
+ assert(e.getCondition ==
"DATATYPE_MISMATCH.INVALID_JSON_SCALAR_RETURNING_TYPE")
+ }
+
+ test("invalid: a DEFAULT that cannot cast to the RETURNING type is rejected
at analysis") {
+ // The DEFAULT-to-RETURNING cast is validated up front, so an uncastable
default (here an
+ // ARRAY DEFAULT with RETURNING INT) fails at analysis rather than late in
the fallback branch.
+ Seq("ON EMPTY", "ON ERROR").foreach { clause =>
+ val e = intercept[AnalysisException] {
+ sql(s"SELECT json_value('{}', '$$.x' RETURNING INT DEFAULT array(1)
$clause)").collect()
+ }
+ assert(e.getCondition == "DATATYPE_MISMATCH.CAST_WITHOUT_SUGGESTION",
+ s"unexpected condition for $clause: ${e.getCondition}")
+ }
+ }
+
+ test("bracket path syntax and array index") {
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.tags[0]')"), Row("x"))
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$[\\'name\\']')"),
Row("Ada"))
+ }
+
+ test("sql renders a bracket-quoted path as a valid, re-parseable string
literal") {
+ // A path containing single quotes (e.g. `$['name']`) must be escaped when
rendered back to
+ // SQL, or the round-tripped statement is malformed. Extract the JsonValue
expression, render
+ // its `.sql`, and confirm the rendered form both parses and evaluates to
the same result.
+ val df = sql(s"SELECT json_value('$doc', '$$[\\'name\\']')")
+ val jsonValue = df.queryExecution.analyzed.expressions
+ .flatMap(_.collect { case jv: JsonValue => jv }).head
+ val rendered = jsonValue.sql
+ assert(rendered.contains("\\'name\\'"), s"path was not escaped in:
$rendered")
+ checkAnswer(sql(s"SELECT $rendered"), Row("Ada"))
+ }
+
+ test("nested path into an object") {
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.addr.city')"), Row("NYC"))
+ }
+
+ test("DEFAULT NULL behaves like NULL ON ERROR/EMPTY") {
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.missing' DEFAULT NULL ON
EMPTY)"), Row(null))
+ checkAnswer(sql(s"SELECT json_value('$doc', '$$.addr' DEFAULT NULL ON
ERROR)"), Row(null))
+ }
+
+ test("DEFAULT is a real child expression, not a constant") {
+ // A DEFAULT that references a column proves the DEFAULT clause is
resolved as a child, and that
+ // the value flows per row.
+ withTempView("t") {
+ Seq((1, "a"), (2, "b")).toDF("k",
"fallback").createOrReplaceTempView("t")
+ checkAnswer(
+ sql("SELECT json_value('{}', '$.x' DEFAULT fallback ON EMPTY) FROM t
ORDER BY k"),
+ Seq(Row("a"), Row("b")))
+ }
+ }
+
+ test("a non-string JSON input is rejected at analysis") {
+ // JSON_VALUE takes a STRING JSON input; an INT input is a type mismatch
(matching the existing
+ // JSON functions' ExpectsInputTypes behavior), not a silent coercion.
+ val e = intercept[AnalysisException] {
+ sql("SELECT json_value(123, '$.a')").collect()
+ }
+ assert(e.getCondition == "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE")
+ }
+}
diff --git
a/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/ThriftServerWithSparkContextSuite.scala
b/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/ThriftServerWithSparkContextSuite.scala
index c285023f82b0..e6aa18256c90 100644
---
a/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/ThriftServerWithSparkContextSuite.scala
+++
b/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/ThriftServerWithSparkContextSuite.scala
@@ -214,7 +214,7 @@ trait ThriftServerWithSparkContextSuite extends
SharedThriftServer {
val sessionHandle = client.openSession(user, "")
val infoValue = client.getInfo(sessionHandle,
GetInfoType.CLI_ODBC_KEYWORDS)
// scalastyle:off line.size.limit
- assert(infoValue.getStringValue ==
"ADD,AFTER,AGGREGATE,ALIGN,ALL,ALTER,ALWAYS,ANALYZE,AND,ANTI,ANY,ANY_VALUE,APPLY,APPROX,ARCHIVE,ARRAY,AS,ASC,ASENSITIVE,ASOF,AT,ATOMIC,AUTHORIZATION,AUTO,BEGIN,BERNOULLI,BETWEEN,BIGINT,BIN,BINARY,BINDING,BIN_DISTRIBUTE_RATIO,BIN_END,BIN_START,BOOLEAN,BOTH,BUCKET,BUCKETS,BY,BYTE,CACHE,CALL,CALLED,CASCADE,CASE,CAST,CATALOG,CATALOGS,CDC,CHANGE,CHANGES,CHAR,CHARACTER,CHECK,CLEAR,CLOSE,CLUSTER,CLUSTERED,CODEGEN,COLLATE,COLLATION,COLLATIONS,COLLECTION,C
[...]
+ assert(infoValue.getStringValue ==
"ADD,AFTER,AGGREGATE,ALIGN,ALL,ALTER,ALWAYS,ANALYZE,AND,ANTI,ANY,ANY_VALUE,APPLY,APPROX,ARCHIVE,ARRAY,AS,ASC,ASENSITIVE,ASOF,AT,ATOMIC,AUTHORIZATION,AUTO,BEGIN,BERNOULLI,BETWEEN,BIGINT,BIN,BINARY,BINDING,BIN_DISTRIBUTE_RATIO,BIN_END,BIN_START,BOOLEAN,BOTH,BUCKET,BUCKETS,BY,BYTE,CACHE,CALL,CALLED,CASCADE,CASE,CAST,CATALOG,CATALOGS,CDC,CHANGE,CHANGES,CHAR,CHARACTER,CHECK,CLEAR,CLOSE,CLUSTER,CLUSTERED,CODEGEN,COLLATE,COLLATION,COLLATIONS,COLLECTION,C
[...]
// scalastyle:on line.size.limit
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
