This is an automated email from the ASF dual-hosted git repository.
cloud-fan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new 918a0fae518a [SPARK-58687][SQL] Support the ANSI SQL/JSON JSON_EXISTS
function
918a0fae518a is described below
commit 918a0fae518af1a7bc6a0ac7d8320af8d6801014
Author: Ganesha S <[email protected]>
AuthorDate: Tue Aug 18 01:36:02 2026 +0800
[SPARK-58687][SQL] Support the ANSI SQL/JSON JSON_EXISTS function
**What changes were proposed in this pull request?**
Add the ANSI SQL:2016 JSON_EXISTS predicate (feature T821):
`JSON_EXISTS(jsonExpr, path [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR
])`
It returns whether a SQL/JSON path matches at least one item in a JSON
document (BOOLEAN):
- a match (including a match whose value is JSON null) → true; no match →
false;
- a SQL NULL input → NULL (Unknown, per 9075-2 8.23);
- input that is not a single well-formed JSON value → the ON ERROR clause
(default FALSE).
Unlike `get_json_object(...) IS NOT NULL`, this distinguishes "present but
null" from "absent". Paths are evaluated in lax mode(strict mode is not yet
supported), matching Oracle and PostgreSQL 17: wildcards ([*], .*, embedded)
are supported, arrays are auto-wrapped/unwrapped, and a structural mismatch is
a non-match rather than an error. JSON_EXISTS is added as a non-reserved
keyword with a dedicated grammar production (parser-only, like JSON_TABLE), and
evaluation reuses JSON_TABLE' [...]
**Why are the changes needed?**
Spark implements none of the ANSI scalar JSON functions. `JSON_EXISTS` is
broadly supported (Oracle, PostgreSQL, SQL Server, DB2), so its absence forces
migrated queries to approximate it with get_json_object(...) IS NOT NULL, which
is string-only and conflates a present-null value with an absent path.
**Does this PR introduce any user-facing change?**
Yes. It adds the new JSON_EXISTS SQL predicate and a SQL reference page.
The new JSON_EXISTS keyword is non-reserved in both ANSI and default modes, so
existing identifiers named json_exists continue to work.
**How was this patch tested?**
New JsonExistsSuite and json-functions.sql golden files (path
present/absent, present-null vs absent, lax wildcards with auto-wrap/unwrap,
out-of-range index, malformed → each ON ERROR behavior, trailing garbage, NULL
input, invalid-path rejection). Regenerated keyword golden files and updated
the getSQLKeywords assertions; existing ExpressionParserSuite, SQLKeywordSuite,
SparkThrowableSuite, SparkConnectDatabaseMetaDataSuite, and
ThriftServerWithSparkContextSuite pass.
**Was this patch authored or co-authored using generative AI tooling?**
Generated-by: Claude Code (Claude Opus 4.8)
Closes #57920 from ganeshashree/SPARK-58687.
Lead-authored-by: Ganesha S <[email protected]>
Co-authored-by: Ganesha S <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
---
.../src/main/resources/error/error-conditions.json | 8 +-
docs/sql-ref-ansi-compliance.md | 1 +
docs/sql-ref-syntax-qry-select-json-exists.md | 127 ++++++++++++++
docs/sql-ref-syntax-qry-select.md | 1 +
docs/sql-ref-syntax.md | 1 +
.../spark/sql/catalyst/parser/SqlBaseLexer.g4 | 1 +
.../spark/sql/catalyst/parser/SqlBaseParser.g4 | 13 ++
.../expressions/json/JsonExpressionEvalUtils.scala | 182 ++++++++++++++++++--
.../sql/catalyst/expressions/jsonExpressions.scala | 122 ++++++++++++-
.../spark/sql/catalyst/parser/AstBuilder.scala | 16 ++
.../spark/sql/errors/QueryExecutionErrors.scala | 6 +
.../catalyst/parser/ExpressionParserSuite.scala | 15 ++
.../jdbc/SparkConnectDatabaseMetaDataSuite.scala | 2 +-
.../analyzer-results/json-functions.sql.out | 148 ++++++++++++++++
.../resources/sql-tests/inputs/json-functions.sql | 34 ++++
.../sql-tests/results/json-functions.sql.out | 176 +++++++++++++++++++
.../sql-tests/results/keywords-enforced.sql.out | 1 +
.../resources/sql-tests/results/keywords.sql.out | 1 +
.../sql-tests/results/nonansi/keywords.sql.out | 1 +
.../org/apache/spark/sql/JsonExistsSuite.scala | 188 +++++++++++++++++++++
.../ThriftServerWithSparkContextSuite.scala | 2 +-
21 files changed, 1031 insertions(+), 15 deletions(-)
diff --git a/common/utils/src/main/resources/error/error-conditions.json
b/common/utils/src/main/resources/error/error-conditions.json
index f601c99c42d2..c4b1568986c2 100644
--- a/common/utils/src/main/resources/error/error-conditions.json
+++ b/common/utils/src/main/resources/error/error-conditions.json
@@ -1960,7 +1960,7 @@
},
"INVALID_JSON_PATH" : {
"message" : [
- "<functionName> has an invalid or unsupported JSON path <path>. Only
simple, wildcard-free paths are supported."
+ "<functionName> has an invalid or unsupported JSON path <path>."
]
},
"INVALID_JSON_SCALAR_RETURNING_TYPE" : {
@@ -5598,6 +5598,12 @@
],
"sqlState" : "42K0E"
},
+ "JSON_EXISTS_ON_ERROR" : {
+ "message" : [
+ "<functionName> could not evaluate the path <path>: the input is not
valid JSON. This error was requested by the ERROR ON ERROR clause."
+ ],
+ "sqlState" : "2203G"
+ },
"JSON_VALUE_ON_ERROR" : {
"message" : [
"<functionName> could not extract a scalar value at path <path>."
diff --git a/docs/sql-ref-ansi-compliance.md b/docs/sql-ref-ansi-compliance.md
index 77c4d96da1ea..c37aefc6473f 100644
--- a/docs/sql-ref-ansi-compliance.md
+++ b/docs/sql-ref-ansi-compliance.md
@@ -618,6 +618,7 @@ Below is a list of all the keywords in Spark SQL.
|ITERATE|non-reserved|non-reserved|non-reserved|
|JOIN|reserved|strict-non-reserved|reserved|
|JSON|non-reserved|non-reserved|non-reserved|
+|JSON_EXISTS|non-reserved|non-reserved|reserved|
|JSON_TABLE|non-reserved|non-reserved|reserved|
|JSON_VALUE|non-reserved|non-reserved|reserved|
|KEY|non-reserved|non-reserved|non-reserved|
diff --git a/docs/sql-ref-syntax-qry-select-json-exists.md
b/docs/sql-ref-syntax-qry-select-json-exists.md
new file mode 100644
index 000000000000..48d307d4fac3
--- /dev/null
+++ b/docs/sql-ref-syntax-qry-select-json-exists.md
@@ -0,0 +1,127 @@
+---
+layout: global
+title: JSON_EXISTS
+displayTitle: JSON_EXISTS
+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_EXISTS` predicate tests whether a SQL/JSON path matches at least one
item in a JSON
+document, returning a `BOOLEAN`. This is the SQL-standard (SQL:2016) way to
test for the presence
+of a JSON value, and is commonly used to migrate queries from other systems
such as Oracle, DB2,
+and PostgreSQL.
+
+Unlike `get_json_object(json_expr, path) IS NOT NULL`, `JSON_EXISTS`
distinguishes a path that is
+_present but whose value is JSON `null`_ (which is `true`) from a path that is
_absent_ (which is
+`false`).
+
+### Syntax
+
+```sql
+JSON_EXISTS ( json_expr, path [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ] )
+```
+
+### Parameters
+
+* **json_expr**
+
+ An expression that evaluates to a `STRING` containing the JSON document. A
`NULL` input yields
+ `NULL` (SQL Unknown), regardless of the `ON ERROR` clause.
+
+* **path**
+
+ A constant SQL/JSON path literal (for example `'$.a.b'`, `'$.tags[0]'`, or
`'$.a[*].b'`). Paths
+ are evaluated in **lax** mode, matching Oracle and PostgreSQL: array
wildcards (`[*]`) and member
+ wildcards (`.*` / `['*']`) are supported, and arrays are
auto-wrapped/unwrapped (a member, index,
+ or wildcard step applied to an array is applied to each element, and a
non-array value is treated
+ as a single-element array). A structural mismatch is a non-match, not an
error. A syntactically
+ invalid path is rejected during analysis.
+
+* **{ TRUE | FALSE | UNKNOWN | ERROR } ON ERROR**
+
+ Controls the result when `json_expr` is not a single well-formed JSON
value (malformed input,
+ or a valid value followed by trailing content). `TRUE`, `FALSE`, and
`UNKNOWN` produce that
+ value (`UNKNOWN` is a `BOOLEAN` `NULL`); `ERROR` raises an error. The
default is
+ `FALSE ON ERROR`.
+
+### Result
+
+* The path matches at least one item (including a match whose value is JSON
`null`) → `true`.
+* The path matches nothing → `false`.
+* `json_expr` is SQL `NULL` → `NULL`.
+* `json_expr` is not a single well-formed JSON value → the `ON ERROR`
behavior.
+
+A structural mismatch is treated as "no match" (`false`), not an error -- for
example reading an
+absent key, reading a key from a scalar, an out-of-range array index, or `[*]`
over an empty array.
+
+### Examples
+
+```sql
+SELECT json_exists('{"a":{"b":1}}', '$.a.b') AS matched;
++-------+
+|matched|
++-------+
+| true|
++-------+
+
+-- Present but JSON null -> true; absent -> false
+SELECT json_exists('{"a":null}', '$.a') AS present_null,
+ json_exists('{"a":1}', '$.b') AS absent;
++------------+------+
+|present_null|absent|
++------------+------+
+| true| false|
++------------+------+
+
+-- NULL input -> NULL (Unknown), regardless of the ON ERROR clause
+SELECT json_exists(CAST(NULL AS STRING), '$.a' TRUE ON ERROR) AS r;
++----+
+| r|
++----+
+|NULL|
++----+
+
+-- Malformed input follows the ON ERROR clause (default FALSE)
+SELECT json_exists('not json', '$.a') AS default_false,
+ json_exists('not json', '$.a' TRUE ON ERROR) AS true_on_error,
+ json_exists('not json', '$.a' UNKNOWN ON ERROR) AS unknown_on_error;
++-------------+-------------+----------------+
+|default_false|true_on_error|unknown_on_error|
++-------------+-------------+----------------+
+| false| true| NULL|
++-------------+-------------+----------------+
+
+-- Lax wildcards: [*] is true iff the array has elements; auto-unwrap applies
a step to each element
+SELECT json_exists('{"a":[1,2]}', '$.a[*]') AS has_elems,
+ json_exists('{"a":[]}', '$.a[*]') AS empty_array,
+ json_exists('{"a":[{"b":1},{"c":2}]}', '$.a[*].b') AS any_elem_has_b;
++---------+-----------+--------------+
+|has_elems|empty_array|any_elem_has_b|
++---------+-----------+--------------+
+| true| false| true|
++---------+-----------+--------------+
+
+-- Use as a predicate in WHERE
+SELECT id FROM docs WHERE json_exists(doc, '$.address.city');
+```
+
+### Related Statements
+
+* [SELECT](sql-ref-syntax-qry-select.html)
+* [WHERE Clause](sql-ref-syntax-qry-select-where.html)
+* [JSON_TABLE](sql-ref-syntax-qry-select-json-table.html)
diff --git a/docs/sql-ref-syntax-qry-select.md
b/docs/sql-ref-syntax-qry-select.md
index b2afd160c5a3..678fb8cf4de3 100644
--- a/docs/sql-ref-syntax-qry-select.md
+++ b/docs/sql-ref-syntax-qry-select.md
@@ -214,6 +214,7 @@ SELECT [ hints , ... ] [ ALL | DISTINCT ] { [ [
named_expression | regex_column_
* [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)
+* [JSON_EXISTS](sql-ref-syntax-qry-select-json-exists.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 c8ddecaf6bdb..9c5c4e3235d0 100644
--- a/docs/sql-ref-syntax.md
+++ b/docs/sql-ref-syntax.md
@@ -85,6 +85,7 @@ ability to generate logical and physical plan for a given
query using
* [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)
+ * [JSON_EXISTS](sql-ref-syntax-qry-select-json-exists.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 c042bca5e8ce..97e027b5e5c0 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
@@ -335,6 +335,7 @@ ITEMS: 'ITEMS';
ITERATE: 'ITERATE';
JOIN: 'JOIN';
JSON: 'JSON';
+JSON_EXISTS: 'JSON_EXISTS';
JSON_TABLE: 'JSON_TABLE';
JSON_VALUE: 'JSON_VALUE';
KEY: 'KEY';
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 ee7de7eac2bf..e702a3766fc9 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
@@ -1466,6 +1466,8 @@ primaryExpression
(RETURNING returning=dataType)?
(emptyBehavior=jsonValueBehavior ON EMPTY)?
(errorBehavior=jsonValueBehavior ON ERROR)? RIGHT_PAREN
#jsonValue
+ | JSON_EXISTS LEFT_PAREN jsonExpr=valueExpression COMMA path=stringLit
+ (errorBehavior=jsonExistsErrorBehavior ON ERROR)? RIGHT_PAREN
#jsonExists
| constant
#constantDefault
| ASTERISK exceptClause?
#star
| qualifiedName DOT ASTERISK exceptClause?
#star
@@ -1500,6 +1502,15 @@ jsonValueBehavior
| DEFAULT defaultExpr=expression
#jsonValueBehaviorDefault
;
+// The behavior selected by a JSON_EXISTS `... ON ERROR` clause: the boolean
(or UNKNOWN, i.e. a
+// BOOLEAN NULL) to produce when the input is not a single well-formed JSON
value.
+jsonExistsErrorBehavior
+ : TRUE
+ | FALSE
+ | UNKNOWN
+ | ERROR
+ ;
+
semiStructuredExtractionPath
: jsonPathFirstPart (jsonPathParts)*
;
@@ -2244,6 +2255,7 @@ ansiNonReserved
| ITEMS
| ITERATE
| JSON
+ | JSON_EXISTS
| JSON_TABLE
| JSON_VALUE
| KEY
@@ -2683,6 +2695,7 @@ nonReserved
| ITEMS
| ITERATE
| JSON
+ | JSON_EXISTS
| JSON_TABLE
| JSON_VALUE
| KEY
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 bf0891ef7236..0fca3b32f110 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
@@ -433,21 +433,26 @@ object PositionResult {
}
/**
- * Row-source and column extraction for the SQL `JSON_TABLE` function. Given
the JSON input, the
- * (wildcard-free) container path, and whether the row path ended in `[*]`, it
produces the
- * per-row JSON documents that the
[[org.apache.spark.sql.catalyst.expressions.JsonTable]]
- * generator then projects into columns via [[navigateColumns]].
+ * Token-aware navigation of a `containerPath` shared by the SQL/JSON
functions. Three entry
+ * points, each with its own path constraints -- so `containerPath` is NOT
wildcard-free in general:
*
- * - `$.items[*]` (containerPath `$.items`, `explodeRoot` = true): the
container must be an
- * array; each element becomes a row.
- * - `$` or `$.x` (`explodeRoot` = false): the matched value becomes exactly
one row.
+ * - [[evaluate]] -- `JSON_TABLE` row source: given the input, a
wildcard-free container path, and
+ * whether the row path ended in `[*]`, produces the per-row JSON
documents that the
+ * [[org.apache.spark.sql.catalyst.expressions.JsonTable]] generator
projects into columns via
+ * [[navigateColumns]]. `$.items[*]` (containerPath `$.items`,
`explodeRoot` = true) explodes an
+ * array into rows; `$` or `$.x` (`explodeRoot` = false) yields exactly
one row.
+ * - [[lookup]] -- `JSON_VALUE` single-scalar extraction, over a
wildcard-free path
+ * (`explodeRoot` = false).
+ * - [[pathExists]] -- `JSON_EXISTS` existence test. Here `containerPath`
MAY contain wildcards
+ * (`[*]`, `.*`, `['*']`) and is evaluated in SQL/JSON *lax* mode
(auto-wrap/unwrap, see
+ * [[anyMatch]]); `explodeRoot` is unused, so construct with `explodeRoot`
= false.
*
* Unlike `get_json_object`, navigation here is token-aware and distinguishes
missing keys from
- * JSON `null` values (see [[JsonPathResult]]).
+ * JSON `null` values (see [[JsonPathResult]] / [[PositionResult]]).
*
- * The input is required to be exactly one well-formed JSON value (no trailing
garbage, not
- * empty); anything else is treated as malformed, so the caller applies the ON
ERROR behavior
- * consistently in both modes.
+ * Every entry point requires the input to be exactly one well-formed JSON
value (no trailing
+ * garbage, not empty); anything else is treated as malformed, so the caller
applies the ON ERROR
+ * behavior consistently in both modes.
*/
case class JsonTableEvaluator(containerPath: Seq[PathInstruction],
explodeRoot: Boolean) {
import PathInstruction._
@@ -584,6 +589,161 @@ case class JsonTableEvaluator(containerPath:
Seq[PathInstruction], explodeRoot:
parser.nextToken() == null
}
+ /**
+ * Tests whether `containerPath` matches at least one item in a single JSON
document, evaluated in
+ * SQL/JSON *lax* mode (see [[anyMatch]]): wildcards are supported and a
structural mismatch is a
+ * non-match rather than an error. Returns `Some(true)` if the path matches
(including a match
+ * whose value is an explicit JSON `null`), `Some(false)` if it matches
nothing, and `None` if the
+ * input is not a single well-formed JSON value (malformed / trailing
garbage / empty). Does not
+ * serialize the matched value. A `null` input is the caller's
responsibility; `explodeRoot` is
+ * ignored (construct the evaluator with `explodeRoot = false`).
+ *
+ * A single parser both navigates the path and validates well-formedness:
after the match
+ * decision, [[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 malformed exactly as
+ * a fully bad document is. This avoids the extra O(document size) pass a
separate
+ * `isSingleWellFormedValue` scan would add per row.
+ */
+ final def pathExists(json: UTF8String): Option[Boolean] = {
+ Utils.tryWithResource(CreateJacksonParser.utf8String(jsonFactory, json)) {
parser =>
+ try {
+ if (parser.nextToken() == null) {
+ None // empty or whitespace-only
+ } else {
+ val exists = anyMatch(parser, containerPath)
+ if (drainToRootEnd(parser)) Some(exists) else None
+ }
+ } catch {
+ case _: JsonProcessingException => None
+ }
+ }
+ }
+
+ /**
+ * Returns whether `path` matches at least one item within the JSON value at
the parser's current
+ * token, evaluated in SQL/JSON *lax* mode (the `JSON_EXISTS` default,
matching Oracle and
+ * PostgreSQL):
+ * - wildcards are supported: `[*]` (any array element) and `.*` / `['*']`
(any object member);
+ * - arrays are auto-unwrapped, i.e. a member/index/wildcard step applied
to an array is applied
+ * to each element (so `$.a.b` matches when `a` is an array of objects
each having `b`);
+ * - a non-array is auto-wrapped as a single-element array, so `[*]` /
`[0]` match the value;
+ * - a structural mismatch (e.g. a member step on a scalar) is a
non-match, never an error.
+ *
+ * Invariant: the parser enters positioned on the first token of the current
value and leaves
+ * positioned on that value's last token -- the value is always fully
consumed, even after a match
+ * is found -- so wildcard branches compose and [[drainToRootEnd]] can
validate trailing content.
+ */
+ private def anyMatch(parser: JsonParser, path: Seq[PathInstruction]):
Boolean = {
+ path match {
+ case Nil =>
+ // End of path: the current value is present (including a JSON null)
-> a match. Consume it.
+ parser.skipChildren()
+ true
+
+ case Key :: Named(name) :: rest =>
+ parser.currentToken match {
+ case JsonToken.START_OBJECT =>
+ // First-match semantics for a named key: only the first member
with this name is
+ // followed, so a duplicate key later in the object is ignored.
This matches the
+ // first-match `positionAt` / `navigateAll` used by `JSON_VALUE` /
`JSON_TABLE`, so a
+ // path resolves consistently across the JSON functions. The whole
object is still
+ // drained (subsequent members skipped) to keep the
parser-position invariant.
+ var found = false
+ var matched = false
+ var token = parser.nextToken()
+ while (token != null && token != JsonToken.END_OBJECT) {
+ val matches = !matched && parser.currentName == name
+ parser.nextToken() // move onto the value; each branch consumes
it
+ if (matches) {
+ matched = true
+ if (anyMatch(parser, rest)) found = true
+ } else {
+ parser.skipChildren()
+ }
+ token = parser.nextToken()
+ }
+ found
+ case JsonToken.START_ARRAY =>
+ forEachElement(parser)(anyMatch(parser, path)) // lax auto-unwrap:
apply to each element
+ case _ =>
+ false // member accessor on a scalar: no match (the scalar is
already fully consumed)
+ }
+
+ case Subscript :: Index(index) :: rest =>
+ parser.currentToken match {
+ case JsonToken.START_ARRAY =>
+ var found = false
+ var i = 0L
+ var token = parser.nextToken()
+ while (token != null && token != JsonToken.END_ARRAY) {
+ if (i == index) {
+ if (anyMatch(parser, rest)) found = true
+ } else {
+ parser.skipChildren()
+ }
+ i += 1
+ token = parser.nextToken()
+ }
+ found
+ case _ =>
+ // lax auto-wrap: a non-array is a single-element array; [0]
matches, [i>0] does not.
+ if (index == 0) {
+ anyMatch(parser, rest)
+ } else {
+ parser.skipChildren() // consume the wrapped value to keep the
invariant
+ false
+ }
+ }
+
+ case Subscript :: Wildcard :: rest =>
+ parser.currentToken match {
+ case JsonToken.START_ARRAY =>
+ forEachElement(parser)(anyMatch(parser, rest))
+ case _ =>
+ anyMatch(parser, rest) // lax auto-wrap: a non-array is a
single-element array
+ }
+
+ case Wildcard :: rest =>
+ parser.currentToken match {
+ case JsonToken.START_OBJECT =>
+ var found = false
+ var token = parser.nextToken()
+ while (token != null && token != JsonToken.END_OBJECT) {
+ parser.nextToken() // move onto the member value
+ if (found) parser.skipChildren() else if (anyMatch(parser,
rest)) found = true
+ token = parser.nextToken()
+ }
+ found
+ case JsonToken.START_ARRAY =>
+ forEachElement(parser)(anyMatch(parser, path)) // lax auto-unwrap:
apply to each element
+ case _ =>
+ false // member wildcard on a scalar: no members
+ }
+
+ case _ =>
+ // Unreachable: JsonPathParser only produces the instruction pairs
handled above.
+ parser.skipChildren()
+ false
+ }
+ }
+
+ /**
+ * Iterates the array the parser is positioned on (its current token is
`START_ARRAY`), evaluating
+ * `matchElement` once per element with the parser positioned on that
element's first token; each
+ * call must fully consume its element. Returns whether any element matched,
and always drains the
+ * whole array, leaving the parser on the closing `END_ARRAY`. Once a match
is found the remaining
+ * elements are skipped, not matched (existence short-circuits, but the
array is still drained).
+ */
+ private def forEachElement(parser: JsonParser)(matchElement: => Boolean):
Boolean = {
+ var found = false
+ var token = parser.nextToken()
+ while (token != null && token != JsonToken.END_ARRAY) {
+ if (found) parser.skipChildren() else if (matchElement) found = true
+ token = parser.nextToken()
+ }
+ found
+ }
+
/**
* 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 c91221690091..f6239cd7f24f 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
@@ -725,7 +725,9 @@ case class JsonValue(
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).
+ // The path must parse and be wildcard-free (JSON_VALUE returns a single
scalar). The
+ // `INVALID_JSON_PATH` message is shared with `JSON_EXISTS`, which does
accept wildcards, so
+ // it must stay generic -- do not re-add wildcard-specific wording here.
DataTypeMismatch(
errorSubClass = "INVALID_JSON_PATH",
messageParameters = Map(
@@ -859,6 +861,124 @@ object JsonValue {
}
}
+/**
+ * Behavior of `JSON_EXISTS`'s `ON ERROR` clause: the value produced when the
input is not a single
+ * well-formed JSON value (malformed / trailing garbage). `Unknown` is a
BOOLEAN NULL.
+ */
+sealed trait JsonExistsBehavior
+object JsonExistsBehavior {
+ case object True extends JsonExistsBehavior
+ case object False extends JsonExistsBehavior
+ case object Unknown extends JsonExistsBehavior
+ case object Error extends JsonExistsBehavior
+}
+
+/**
+ * The SQL:2016 `JSON_EXISTS` predicate (feature T821): returns whether a
SQL/JSON `path` matches at
+ * least one item in a JSON input.
+ *
+ * - path matches (including an explicit JSON `null`) -> true
+ * - path matches nothing -> false
+ * - malformed / non-single-value input -> ON ERROR behavior
(default FALSE)
+ * - SQL NULL input -> SQL NULL
(Unknown, per 9075-2 8.23)
+ *
+ * This distinguishes "present but null" from "absent" (unlike
`get_json_object(...) IS NOT NULL`).
+ * The `ON ERROR` clause chooses TRUE / FALSE / UNKNOWN (a BOOLEAN NULL) /
ERROR; it defaults to
+ * FALSE ON ERROR.
+ *
+ * Paths are evaluated in SQL/JSON lax mode (matching Oracle / PostgreSQL):
wildcards are supported
+ * and arrays are auto-wrapped/unwrapped, while a structural mismatch is a
non-match, not an error.
+ *
+ * {{{
+ * JSON_EXISTS('{"a":{"b":1}}', '$.a.b') -- true
+ * JSON_EXISTS('{"a":null}', '$.a') -- true (present, value
is null)
+ * JSON_EXISTS('{"a":1}', '$.b') -- false (absent)
+ * JSON_EXISTS('{"a":[1,2]}', '$.a[*]') -- true (array has
elements)
+ * JSON_EXISTS('not json', '$.a' TRUE ON ERROR) -- true
+ * }}}
+ */
+case class JsonExists(
+ child: Expression,
+ path: String,
+ onError: JsonExistsBehavior)
+ extends UnaryExpression
+ with CodegenFallback
+ with ExpectsInputTypes
+ with QueryErrorsBase {
+
+ // The result is NULL only when the input is SQL NULL, or when `UNKNOWN ON
ERROR` turns malformed
+ // input into a BOOLEAN NULL. With a non-nullable input and any other ON
ERROR behavior the result
+ // is always a concrete boolean, which lets the optimizer treat e.g. a WHERE
predicate as such.
+ override def nullable: Boolean =
+ child.nullable || onError == JsonExistsBehavior.Unknown
+
+ override def inputTypes: Seq[AbstractDataType] =
+ Seq(StringTypeWithCollation(supportsTrimCollation = true))
+
+ override def dataType: DataType = BooleanType
+
+ // The path is a constant (the grammar makes it a string literal), so it is
parsed once and shared
+ // by `checkInputDataTypes` and `evaluator` rather than reparsed. `None`
means it did not parse.
+ @transient private lazy val parsedPath: Option[Seq[PathInstruction]] =
JsonPathParser.parse(path)
+
+ override def checkInputDataTypes(): TypeCheckResult = {
+ val inputCheck = super.checkInputDataTypes()
+ if (inputCheck.isFailure) {
+ inputCheck
+ } else if (parsedPath.isDefined) {
+ // A valid SQL/JSON path. Wildcards are allowed and evaluated in lax
mode at runtime.
+ TypeCheckResult.TypeCheckSuccess
+ } else {
+ // The path is not a valid SQL/JSON path. `JSON_EXISTS` reaches this
branch only for a
+ // syntactically malformed path -- wildcards parse and are accepted
above. The shared
+ // `INVALID_JSON_PATH` error is also raised by `JSON_VALUE` (which
additionally rejects
+ // wildcards, as it returns a single scalar); its message is worded
generically for both.
+ DataTypeMismatch(
+ errorSubClass = "INVALID_JSON_PATH",
+ messageParameters = Map(
+ "functionName" -> toSQLId(prettyName), "path" -> toSQLValue(path)))
+ }
+ }
+
+ // `checkInputDataTypes` guarantees the path parses before this is forced;
`Nil` is an unreachable
+ // fallback that would match the document root.
+ @transient private lazy val evaluator: JsonTableEvaluator =
+ JsonTableEvaluator(parsedPath.getOrElse(Nil), explodeRoot = false)
+
+ private def onErrorResult(): Any = onError match {
+ case JsonExistsBehavior.True => true
+ case JsonExistsBehavior.False => false
+ case JsonExistsBehavior.Unknown => null
+ case JsonExistsBehavior.Error =>
+ throw QueryExecutionErrors.jsonExistsOnError(prettyName, path)
+ }
+
+ override def eval(input: InternalRow): Any = {
+ val json = child.eval(input).asInstanceOf[UTF8String]
+ // SQL NULL input yields Unknown (BOOLEAN NULL), not the ON ERROR path,
per 9075-2 8.23.
+ if (json == null) return null
+ evaluator.pathExists(json) match {
+ case Some(exists) => exists
+ case None => onErrorResult() // malformed / non-single-value input
+ }
+ }
+
+ override def prettyName: String = "json_exists"
+
+ override def sql: String = {
+ val errorSQL = onError match {
+ case JsonExistsBehavior.False => "" // the default
+ case JsonExistsBehavior.True => " TRUE ON ERROR"
+ case JsonExistsBehavior.Unknown => " UNKNOWN ON ERROR"
+ case JsonExistsBehavior.Error => " ERROR ON ERROR"
+ }
+ s"JSON_EXISTS(${child.sql}, ${toSQLValue(path)}$errorSQL)"
+ }
+
+ override protected def withNewChildInternal(newChild: Expression):
JsonExists =
+ copy(child = newChild)
+}
+
/**
* 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 74c691699609..dd8e189c9752 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
@@ -4200,6 +4200,22 @@ class AstBuilder extends DataTypeAstBuilder
JsonValue(jsonExpr, path, returning, onEmpty, onError, emptyDefault,
errorDefault)
}
+ /**
+ * Create a [[JsonExists]] expression for the SQL:2016 `JSON_EXISTS`
predicate. The `ON ERROR`
+ * clause defaults to `FALSE` when absent, per the standard.
+ */
+ override def visitJsonExists(ctx: JsonExistsContext): Expression =
withOrigin(ctx) {
+ val jsonExpr = expression(ctx.jsonExpr)
+ val path = string(visitStringLit(ctx.path))
+ val onError = Option(ctx.errorBehavior).map { b =>
+ if (b.TRUE != null) JsonExistsBehavior.True
+ else if (b.FALSE != null) JsonExistsBehavior.False
+ else if (b.UNKNOWN != null) JsonExistsBehavior.Unknown
+ else JsonExistsBehavior.Error
+ }.getOrElse(JsonExistsBehavior.False)
+ JsonExists(jsonExpr, path, onError)
+ }
+
/**
* 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 91afa6b4dbf6..e08e078548f6 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
@@ -1639,6 +1639,12 @@ private[sql] object QueryExecutionErrors extends
QueryErrorsBase with ExecutionE
cause = cause)
}
+ def jsonExistsOnError(functionName: String, path: String): Throwable = {
+ new SparkRuntimeException(
+ errorClass = "JSON_EXISTS_ON_ERROR",
+ messageParameters = Map("functionName" -> toSQLId(functionName), "path"
-> toSQLValue(path)))
+ }
+
def invalidKerberosConfigForHiveServer2Error(): Throwable = {
new SparkException(
errorClass = "_LEGACY_ERROR_TEMP_2179",
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 18a92a6496d3..5187c256bed7 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
@@ -331,6 +331,21 @@ class ExpressionParserSuite extends AnalysisTest {
Some(Literal("x")), Some(Literal("y"))))
}
+ test("JSON_EXISTS expressions") {
+ import org.apache.spark.sql.catalyst.expressions.JsonExistsBehavior
+ // Bare form defaults to FALSE ON ERROR.
+ assertEqual("json_exists(a, '$.b')", JsonExists($"a", "$.b",
JsonExistsBehavior.False))
+ assertEqual(
+ "json_exists(a, '$.b' TRUE ON ERROR)", JsonExists($"a", "$.b",
JsonExistsBehavior.True))
+ assertEqual(
+ "json_exists(a, '$.b' FALSE ON ERROR)", JsonExists($"a", "$.b",
JsonExistsBehavior.False))
+ assertEqual(
+ "json_exists(a, '$.b' UNKNOWN ON ERROR)",
+ JsonExists($"a", "$.b", JsonExistsBehavior.Unknown))
+ assertEqual(
+ "json_exists(a, '$.b' ERROR ON ERROR)", JsonExists($"a", "$.b",
JsonExistsBehavior.Error))
+ }
+
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 b07f9da119a4..17f3d77b77c5 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 9b33afd10db8..74145b7f3d42 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
@@ -1358,3 +1358,151 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"fragment" : "json_value('{}', '$.x' RETURNING INT DEFAULT array(1) ON
EMPTY)"
} ]
}
+
+
+-- !query
+select
json_exists('{"id":7,"addr":{"city":"NYC"},"score":null,"tags":["x","y"]}',
'$.addr.city')
+-- !query analysis
+Project
[json_exists({"id":7,"addr":{"city":"NYC"},"score":null,"tags":["x","y"]},
$.addr.city, False) AS
JSON_EXISTS({"id":7,"addr":{"city":"NYC"},"score":null,"tags":["x","y"]},
'$.addr.city')#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('{"score":null}', '$.score')
+-- !query analysis
+Project [json_exists({"score":null}, $.score, False) AS
JSON_EXISTS({"score":null}, '$.score')#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('{"addr":{"city":"NYC"}}', '$.addr.zip')
+-- !query analysis
+Project [json_exists({"addr":{"city":"NYC"}}, $.addr.zip, False) AS
JSON_EXISTS({"addr":{"city":"NYC"}}, '$.addr.zip')#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('{"addr":{"city":"NYC"}}', '$.addr')
+-- !query analysis
+Project [json_exists({"addr":{"city":"NYC"}}, $.addr, False) AS
JSON_EXISTS({"addr":{"city":"NYC"}}, '$.addr')#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('{"tags":["x","y"]}', '$.tags[0]')
+-- !query analysis
+Project [json_exists({"tags":["x","y"]}, $.tags[0], False) AS
JSON_EXISTS({"tags":["x","y"]}, '$.tags[0]')#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists(cast(null as string), '$.a')
+-- !query analysis
+Project [json_exists(cast(null as string), $.a, False) AS
JSON_EXISTS(CAST(NULL AS STRING), '$.a')#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('not json', '$.a')
+-- !query analysis
+Project [json_exists(not json, $.a, False) AS JSON_EXISTS(not json, '$.a')#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('not json', '$.a' TRUE ON ERROR)
+-- !query analysis
+Project [json_exists(not json, $.a, True) AS JSON_EXISTS(not json, '$.a' TRUE
ON ERROR)#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('not json', '$.a' FALSE ON ERROR)
+-- !query analysis
+Project [json_exists(not json, $.a, False) AS JSON_EXISTS(not json, '$.a')#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('not json', '$.a' UNKNOWN ON ERROR)
+-- !query analysis
+Project [json_exists(not json, $.a, Unknown) AS JSON_EXISTS(not json, '$.a'
UNKNOWN ON ERROR)#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('not json', '$.a' ERROR ON ERROR)
+-- !query analysis
+Project [json_exists(not json, $.a, Error) AS JSON_EXISTS(not json, '$.a'
ERROR ON ERROR)#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('{"a":[1,2]}', '$.a[*]')
+-- !query analysis
+Project [json_exists({"a":[1,2]}, $.a[*], False) AS JSON_EXISTS({"a":[1,2]},
'$.a[*]')#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('{"a":[]}', '$.a[*]')
+-- !query analysis
+Project [json_exists({"a":[]}, $.a[*], False) AS JSON_EXISTS({"a":[]},
'$.a[*]')#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('{"a":5}', '$.a[*]')
+-- !query analysis
+Project [json_exists({"a":5}, $.a[*], False) AS JSON_EXISTS({"a":5},
'$.a[*]')#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('{"a":[{"b":1},{"c":2}]}', '$.a[*].b')
+-- !query analysis
+Project [json_exists({"a":[{"b":1},{"c":2}]}, $.a[*].b, False) AS
JSON_EXISTS({"a":[{"b":1},{"c":2}]}, '$.a[*].b')#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('{"a":[1,2]}', '$.a[5]')
+-- !query analysis
+Project [json_exists({"a":[1,2]}, $.a[5], False) AS JSON_EXISTS({"a":[1,2]},
'$.a[5]')#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('{"a":[{"b":1},{"b":2}]}', '$.a.b')
+-- !query analysis
+Project [json_exists({"a":[{"b":1},{"b":2}]}, $.a.b, False) AS
JSON_EXISTS({"a":[{"b":1},{"b":2}]}, '$.a.b')#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('{"addr":{"city":"NYC"}}', '$.*')
+-- !query analysis
+Project [json_exists({"addr":{"city":"NYC"}}, $.*, False) AS
JSON_EXISTS({"addr":{"city":"NYC"}}, '$.*')#x]
++- OneRowRelation
+
+
+-- !query
+select json_exists('{"a":1}', '$[')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.INVALID_JSON_PATH",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "functionName" : "`json_exists`",
+ "path" : "'$['",
+ "sqlExpr" : "\"JSON_EXISTS({\"a\":1}, '$[')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 35,
+ "fragment" : "json_exists('{\"a\":1}', '$[')"
+ } ]
+}
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 fa1cb6628ec9..93fcdaf5f478 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
@@ -211,3 +211,37 @@ select json_value('{"a":[1,2]}', '$.a[*]');
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);
+
+-- JSON_EXISTS: test path presence (ANSI SQL:2016)
+select
json_exists('{"id":7,"addr":{"city":"NYC"},"score":null,"tags":["x","y"]}',
'$.addr.city');
+-- present but JSON null -> true
+select json_exists('{"score":null}', '$.score');
+-- absent -> false
+select json_exists('{"addr":{"city":"NYC"}}', '$.addr.zip');
+-- matches an object / array -> true
+select json_exists('{"addr":{"city":"NYC"}}', '$.addr');
+select json_exists('{"tags":["x","y"]}', '$.tags[0]');
+-- NULL input -> NULL (unknown)
+select json_exists(cast(null as string), '$.a');
+-- malformed input -> FALSE ON ERROR (default)
+select json_exists('not json', '$.a');
+-- ON ERROR behaviors
+select json_exists('not json', '$.a' TRUE ON ERROR);
+select json_exists('not json', '$.a' FALSE ON ERROR);
+select json_exists('not json', '$.a' UNKNOWN ON ERROR);
+select json_exists('not json', '$.a' ERROR ON ERROR);
+-- lax wildcard [*]: true iff the array has elements
+select json_exists('{"a":[1,2]}', '$.a[*]');
+select json_exists('{"a":[]}', '$.a[*]');
+-- lax auto-wrap: [*] over a non-array treats it as a single-element array
+select json_exists('{"a":5}', '$.a[*]');
+-- embedded wildcard: any element has the field
+select json_exists('{"a":[{"b":1},{"c":2}]}', '$.a[*].b');
+-- out-of-range index -> false
+select json_exists('{"a":[1,2]}', '$.a[5]');
+-- lax auto-unwrap: a member step over an array applies to each element
+select json_exists('{"a":[{"b":1},{"b":2}]}', '$.a.b');
+-- member wildcard .* matches any member
+select json_exists('{"addr":{"city":"NYC"}}', '$.*');
+-- invalid: an unparseable path is rejected at analysis
+select json_exists('{"a":1}', '$[');
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 6656a7723cd1..8508369a6f1d 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
@@ -1546,3 +1546,179 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"fragment" : "json_value('{}', '$.x' RETURNING INT DEFAULT array(1) ON
EMPTY)"
} ]
}
+
+
+-- !query
+select
json_exists('{"id":7,"addr":{"city":"NYC"},"score":null,"tags":["x","y"]}',
'$.addr.city')
+-- !query schema
+struct<JSON_EXISTS({"id":7,"addr":{"city":"NYC"},"score":null,"tags":["x","y"]},
'$.addr.city'):boolean>
+-- !query output
+true
+
+
+-- !query
+select json_exists('{"score":null}', '$.score')
+-- !query schema
+struct<JSON_EXISTS({"score":null}, '$.score'):boolean>
+-- !query output
+true
+
+
+-- !query
+select json_exists('{"addr":{"city":"NYC"}}', '$.addr.zip')
+-- !query schema
+struct<JSON_EXISTS({"addr":{"city":"NYC"}}, '$.addr.zip'):boolean>
+-- !query output
+false
+
+
+-- !query
+select json_exists('{"addr":{"city":"NYC"}}', '$.addr')
+-- !query schema
+struct<JSON_EXISTS({"addr":{"city":"NYC"}}, '$.addr'):boolean>
+-- !query output
+true
+
+
+-- !query
+select json_exists('{"tags":["x","y"]}', '$.tags[0]')
+-- !query schema
+struct<JSON_EXISTS({"tags":["x","y"]}, '$.tags[0]'):boolean>
+-- !query output
+true
+
+
+-- !query
+select json_exists(cast(null as string), '$.a')
+-- !query schema
+struct<JSON_EXISTS(CAST(NULL AS STRING), '$.a'):boolean>
+-- !query output
+NULL
+
+
+-- !query
+select json_exists('not json', '$.a')
+-- !query schema
+struct<JSON_EXISTS(not json, '$.a'):boolean>
+-- !query output
+false
+
+
+-- !query
+select json_exists('not json', '$.a' TRUE ON ERROR)
+-- !query schema
+struct<JSON_EXISTS(not json, '$.a' TRUE ON ERROR):boolean>
+-- !query output
+true
+
+
+-- !query
+select json_exists('not json', '$.a' FALSE ON ERROR)
+-- !query schema
+struct<JSON_EXISTS(not json, '$.a'):boolean>
+-- !query output
+false
+
+
+-- !query
+select json_exists('not json', '$.a' UNKNOWN ON ERROR)
+-- !query schema
+struct<JSON_EXISTS(not json, '$.a' UNKNOWN ON ERROR):boolean>
+-- !query output
+NULL
+
+
+-- !query
+select json_exists('not json', '$.a' ERROR ON ERROR)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkRuntimeException
+{
+ "errorClass" : "JSON_EXISTS_ON_ERROR",
+ "sqlState" : "2203G",
+ "messageParameters" : {
+ "functionName" : "`json_exists`",
+ "path" : "'$.a'"
+ }
+}
+
+
+-- !query
+select json_exists('{"a":[1,2]}', '$.a[*]')
+-- !query schema
+struct<JSON_EXISTS({"a":[1,2]}, '$.a[*]'):boolean>
+-- !query output
+true
+
+
+-- !query
+select json_exists('{"a":[]}', '$.a[*]')
+-- !query schema
+struct<JSON_EXISTS({"a":[]}, '$.a[*]'):boolean>
+-- !query output
+false
+
+
+-- !query
+select json_exists('{"a":5}', '$.a[*]')
+-- !query schema
+struct<JSON_EXISTS({"a":5}, '$.a[*]'):boolean>
+-- !query output
+true
+
+
+-- !query
+select json_exists('{"a":[{"b":1},{"c":2}]}', '$.a[*].b')
+-- !query schema
+struct<JSON_EXISTS({"a":[{"b":1},{"c":2}]}, '$.a[*].b'):boolean>
+-- !query output
+true
+
+
+-- !query
+select json_exists('{"a":[1,2]}', '$.a[5]')
+-- !query schema
+struct<JSON_EXISTS({"a":[1,2]}, '$.a[5]'):boolean>
+-- !query output
+false
+
+
+-- !query
+select json_exists('{"a":[{"b":1},{"b":2}]}', '$.a.b')
+-- !query schema
+struct<JSON_EXISTS({"a":[{"b":1},{"b":2}]}, '$.a.b'):boolean>
+-- !query output
+true
+
+
+-- !query
+select json_exists('{"addr":{"city":"NYC"}}', '$.*')
+-- !query schema
+struct<JSON_EXISTS({"addr":{"city":"NYC"}}, '$.*'):boolean>
+-- !query output
+true
+
+
+-- !query
+select json_exists('{"a":1}', '$[')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.INVALID_JSON_PATH",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "functionName" : "`json_exists`",
+ "path" : "'$['",
+ "sqlExpr" : "\"JSON_EXISTS({\"a\":1}, '$[')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 35,
+ "fragment" : "json_exists('{\"a\":1}', '$[')"
+ } ]
+}
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 37f174acfe53..2989a7373ffa 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
@@ -212,6 +212,7 @@ ITEMS false
ITERATE false
JOIN true
JSON false
+JSON_EXISTS false
JSON_TABLE false
JSON_VALUE false
KEY false
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 f6b910a54c3a..aa14278fe15a 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
@@ -212,6 +212,7 @@ ITEMS false
ITERATE false
JOIN false
JSON false
+JSON_EXISTS false
JSON_TABLE false
JSON_VALUE false
KEY 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 f6b910a54c3a..aa14278fe15a 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
@@ -212,6 +212,7 @@ ITEMS false
ITERATE false
JOIN false
JSON false
+JSON_EXISTS false
JSON_TABLE false
JSON_VALUE false
KEY false
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/JsonExistsSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/JsonExistsSuite.scala
new file mode 100644
index 000000000000..a4bac2c628a5
--- /dev/null
+++ b/sql/core/src/test/scala/org/apache/spark/sql/JsonExistsSuite.scala
@@ -0,0 +1,188 @@
+/*
+ * 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.{JsonExists,
JsonExistsBehavior, Literal}
+import org.apache.spark.sql.test.SharedSparkSession
+import org.apache.spark.sql.types.BooleanType
+
+/**
+ * End-to-end tests for the SQL:2016 `JSON_EXISTS` predicate.
+ */
+class JsonExistsSuite extends QueryTest with SharedSparkSession {
+ import testImplicits._
+
+ private val doc =
+ """{"id":7,"addr":{"city":"NYC"},"score":null,"tags":["x","y"]}"""
+
+ test("returns BOOLEAN") {
+ assert(sql(s"SELECT json_exists('$doc', '$$.id')").schema.head.dataType
=== BooleanType)
+ }
+
+ test("path present -> true, absent -> false") {
+ checkAnswer(sql(s"SELECT json_exists('$doc', '$$.addr.city')"), Row(true))
+ checkAnswer(sql(s"SELECT json_exists('$doc', '$$.addr.zip')"), Row(false))
+ checkAnswer(sql(s"SELECT json_exists('$doc', '$$.missing')"), Row(false))
+ }
+
+ test("present but JSON null -> true (distinguishes present-null from
absent)") {
+ checkAnswer(sql(s"SELECT json_exists('$doc', '$$.score')"), Row(true))
+ }
+
+ test("a matched object or array -> true") {
+ checkAnswer(sql(s"SELECT json_exists('$doc', '$$.addr')"), Row(true))
+ checkAnswer(sql(s"SELECT json_exists('$doc', '$$.tags')"), Row(true))
+ checkAnswer(sql(s"SELECT json_exists('$doc', '$$.tags[1]')"), Row(true))
+ checkAnswer(sql(s"SELECT json_exists('$doc', '$$.tags[5]')"), Row(false))
+ }
+
+ test("NULL input yields NULL (Unknown), not the ON ERROR path") {
+ checkAnswer(
+ sql("SELECT json_exists(CAST(NULL AS STRING), '$.a' ERROR ON ERROR)"),
Row(null))
+ }
+
+ test("malformed input: FALSE ON ERROR by default, and each ON ERROR
behavior") {
+ checkAnswer(sql("SELECT json_exists('not json', '$.a')"), Row(false))
+ checkAnswer(sql("SELECT json_exists('not json', '$.a' FALSE ON ERROR)"),
Row(false))
+ checkAnswer(sql("SELECT json_exists('not json', '$.a' TRUE ON ERROR)"),
Row(true))
+ checkAnswer(sql("SELECT json_exists('not json', '$.a' UNKNOWN ON ERROR)"),
Row(null))
+ val e = intercept[SparkRuntimeException] {
+ sql("SELECT json_exists('not json', '$.a' ERROR ON ERROR)").collect()
+ }
+ assert(e.getCondition == "JSON_EXISTS_ON_ERROR")
+ }
+
+ test("empty or whitespace-only input is malformed -> ON ERROR") {
+ checkAnswer(sql("SELECT json_exists('', '$.a')"), Row(false))
+ checkAnswer(sql("SELECT json_exists('', '$.a' TRUE ON ERROR)"), Row(true))
+ checkAnswer(sql("SELECT json_exists(' ', '$.a')"), Row(false))
+ checkAnswer(sql("SELECT json_exists(' ', '$.a' TRUE ON ERROR)"),
Row(true))
+ }
+
+ test("a partial/prefix-garbage input is malformed -> ON ERROR") {
+ // A valid prefix followed by trailing garbage is not a single well-formed
value. This holds
+ // whether the path matches (drainToRootEnd runs after the match) or is
absent (drainToRootEnd
+ // runs after the miss) -- both surface the trailing content as malformed
input.
+ checkAnswer(sql("""SELECT json_exists('{"a":1} trailing', '$.a')"""),
Row(false))
+ checkAnswer(sql("""SELECT json_exists('{"a":1} trailing', '$.a' TRUE ON
ERROR)"""), Row(true))
+ checkAnswer(sql("""SELECT json_exists('{"a":1} trailing',
'$.missing')"""), Row(false))
+ checkAnswer(
+ sql("""SELECT json_exists('{"a":1} trailing', '$.missing' TRUE ON
ERROR)"""), Row(true))
+ // A second well-formed root value (not just garbage) is also more than
one value -> malformed;
+ // drainToRootEnd must reject the trailing root, even though the path
matched in the first one.
+ checkAnswer(sql("""SELECT json_exists('{"a":1} {"b":2}', '$.a')"""),
Row(false))
+ checkAnswer(sql("""SELECT json_exists('{"a":1} {"b":2}', '$.a' TRUE ON
ERROR)"""), Row(true))
+ }
+
+ test("works over a column of JSON documents") {
+ withTempView("docs") {
+ Seq(
+ (1, """{"a":1}"""),
+ (2, """{"b":2}"""), // absent -> false
+ (3, """{"a":null}"""), // present null -> true
+ (4, "not json")) // malformed -> false (default)
+ .toDF("k", "j").createOrReplaceTempView("docs")
+ checkAnswer(
+ sql("SELECT k, json_exists(j, '$.a') AS e FROM docs ORDER BY k"),
+ Seq(Row(1, true), Row(2, false), Row(3, true), Row(4, false)))
+ }
+ }
+
+ test("bracket and nested path syntax") {
+ checkAnswer(sql(s"SELECT json_exists('$doc',
'$$[\\'addr\\'][\\'city\\']')"), Row(true))
+ checkAnswer(sql(s"SELECT json_exists('$doc', '$$.addr.city')"), Row(true))
+ }
+
+ test("sql escapes a quoted path literal so the rendering re-parses") {
+ // A bracket-quoted path contains single quotes; the `sql` rendering must
escape them, otherwise
+ // it would emit invalid SQL such as JSON_EXISTS('{}', '$['a']['b']').
+ val e = JsonExists(Literal("{}"), "$['a']['b']", JsonExistsBehavior.False)
+ val parsed = spark.sessionState.sqlParser.parseExpression(e.sql)
+ assert(parsed.isInstanceOf[JsonExists])
+ assert(parsed.asInstanceOf[JsonExists].path === "$['a']['b']")
+ }
+
+ test("lax wildcard [*]: true iff the array has elements; auto-wraps a
non-array") {
+ checkAnswer(sql(s"SELECT json_exists('$doc', '$$.tags[*]')"), Row(true))
+ checkAnswer(sql("""SELECT json_exists('{"tags":[]}', '$.tags[*]')"""),
Row(false))
+ checkAnswer(sql("""SELECT json_exists('{"a":[1,2]}', '$.a[*]')"""),
Row(true))
+ // lax auto-wrap: a non-array value (scalar or object) is a single-element
array.
+ checkAnswer(sql("""SELECT json_exists('{"a":5}', '$.a[*]')"""), Row(true))
+ checkAnswer(sql(s"SELECT json_exists('$doc', '$$.addr[*]')"), Row(true))
+ // lax auto-wrap for an explicit index: over a non-array, [0] matches the
wrapped value and
+ // any [i>0] does not.
+ checkAnswer(sql("""SELECT json_exists('{"a":5}', '$.a[0]')"""), Row(true))
+ checkAnswer(sql("""SELECT json_exists('{"a":5}', '$.a[1]')"""), Row(false))
+ }
+
+ test("lax embedded wildcard $.a[*].b matches when any element has the
field") {
+ checkAnswer(sql("""SELECT json_exists('{"a":[{"b":1},{"c":2}]}',
'$.a[*].b')"""), Row(true))
+ // Match only in a later element (guards the short-circuit loop).
+ checkAnswer(sql("""SELECT json_exists('{"a":[{"c":1},{"b":2}]}',
'$.a[*].b')"""), Row(true))
+ checkAnswer(sql("""SELECT json_exists('{"a":[{"c":1}]}', '$.a[*].b')"""),
Row(false))
+ }
+
+ test("lax member wildcard .* and ['*'] match any member") {
+ checkAnswer(sql(s"SELECT json_exists('$doc', '$$.addr.*')"), Row(true))
+ checkAnswer(sql("""SELECT json_exists('{}', '$.*')"""), Row(false))
+ // ['*'] is the bracket-quoted spelling of the same member wildcard.
+ checkAnswer(sql(s"SELECT json_exists('$doc', '$$[\\'*\\']')"), Row(true))
+ checkAnswer(sql(s"SELECT json_exists('$doc', '$$.addr[\\'*\\']')"),
Row(true))
+ checkAnswer(sql("SELECT json_exists('{}', '$[\\'*\\']')"), Row(false))
+ // lax auto-unwrap: a member wildcard over an array applies to each
element, matching iff some
+ // element has a member.
+ checkAnswer(sql("""SELECT json_exists('[{"a":1}]', '$.*')"""), Row(true))
+ checkAnswer(sql("""SELECT json_exists('[1]', '$.*')"""), Row(false))
+ }
+
+ test("index step over a non-array member is skipped without corrupting the
traversal") {
+ // $.*[1] visits each member; [1] over the object member `a` must be fully
consumed (return
+ // false) so the traversal advances to member `b`, where b[1] matches.
+ checkAnswer(sql("""SELECT json_exists('{"a":{"x":1},"b":[0,1]}',
'$.*[1]')"""), Row(true))
+ checkAnswer(sql("""SELECT json_exists('{"a":{"x":1}}', '$.*[1]')"""),
Row(false))
+ }
+
+ test("lax auto-unwrap: a member step over an array applies to each element")
{
+ checkAnswer(sql("""SELECT json_exists('{"a":[{"b":1},{"b":2}]}',
'$.a.b')"""), Row(true))
+ checkAnswer(sql("""SELECT json_exists('{"a":[{"c":1}]}', '$.a.b')"""),
Row(false))
+ }
+
+ test("duplicate object keys: first-match, consistent with JSON_VALUE /
JSON_TABLE") {
+ // A named-key step follows only the first member with that name; a later
duplicate is ignored.
+ checkAnswer(sql("""SELECT json_exists('{"a":{},"a":{"b":1}}',
'$.a.b')"""), Row(false))
+ checkAnswer(sql("""SELECT json_exists('{"a":{"b":1},"a":{}}',
'$.a.b')"""), Row(true))
+ // The duplicate key itself still exists, so a path that stops at it
matches.
+ checkAnswer(sql("""SELECT json_exists('{"a":{},"a":{"b":1}}', '$.a')"""),
Row(true))
+ }
+
+ test("invalid: an unparseable path is rejected at analysis") {
+ val e = intercept[AnalysisException] {
+ sql(s"SELECT json_exists('$doc', '$$[')").collect()
+ }
+ assert(e.getCondition == "DATATYPE_MISMATCH.INVALID_JSON_PATH")
+ }
+
+ test("usable in a WHERE predicate") {
+ withTempView("docs") {
+ Seq((1, """{"a":1}"""), (2, """{"b":2}""")).toDF("k",
"j").createOrReplaceTempView("docs")
+ checkAnswer(
+ sql("SELECT k FROM docs WHERE json_exists(j, '$.a')"), Seq(Row(1)))
+ }
+ }
+}
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 e6aa18256c90..8249d8fcf4e9 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]
