jiangxintong created SPARK-57927:
------------------------------------
Summary: Add json_valid function for JSON syntax validation
Key: SPARK-57927
URL: https://issues.apache.org/jira/browse/SPARK-57927
Project: Spark
Issue Type: Improvement
Components: SQL
Affects Versions: 4.2.0
Reporter: jiangxintong
h2. Problem
Spark SQL has no built-in function to validate JSON syntax. Users resort to
workaround:
{code:sql}
SELECT get_json_object(col, '$') IS NOT NULL FROM t;
{code}
But this is semantically different — get_json_object returns null for some
malformed JSON without proper validation.
Other databases have this capability:
* MySQL 8.0+: JSON_VALID()
* PostgreSQL: json_valid() (via extension)
h2. Proposed Function
||Function||Signature||Description||
|json_valid|json_valid(STRING) -> BOOLEAN|Returns true if the input is valid
JSON, false otherwise. Returns null for null input.|
h2. Examples
{code:sql}
> SELECT json_valid('{"a": 1}');
true
> SELECT json_valid('not json');
false
> SELECT json_valid('{"a":1} garbage');
false
> SELECT json_valid('');
false
> SELECT json_valid(null);
null
{code}
h2. Semantics (TBD in Review)
Propose strict RFC 8259 validation (matches MySQL JSON_VALID):
* Trailing content after the root value is rejected
* Empty string and whitespace-only string return false
* Numbers, strings, arrays, objects, true/false/null are all valid JSON values
Note: Spark's from_json and get_json_object (via SharedFactory in
JsonExpressionEvalUtils.scala) default to lenient mode (allowSingleQuotes=true,
allowNonNumericNumbers=true). Strict semantics would diverge from these
existing functions. Open to aligning with Spark's lenient behavior instead — to
be decided in review.
h2. Implementation Approach
Use streaming parser (JsonParser + skipChildren) instead of readTree:
* readTree materializes the entire parse tree into memory — unnecessary for
validation
* Streaming parser only checks "is this a single complete value + reached end"
* Trailing token check: after skipChildren(), call nextToken() and verify it
returns null
* Explicitly reject empty/whitespace-only input
h2. Why Built-in Instead of UDF
JSON validation is:
* Standard in major databases (MySQL 8.0+)
* Common data quality check in ETL pipelines
* Performance-critical for large-scale data ingestion
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
