andygrove opened a new issue, #245:
URL: https://github.com/apache/datafusion-spark/issues/245

   Part of #176 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `schema_of_json(json[, options])` infers the Spark type of a JSON string and 
returns it as a DDL-formatted `STRING`
   (`DataType.sql`), for example `STRUCT<c1: BIGINT, c2: ARRAY<BIGINT>>`. The 
argument must be foldable and non-NULL.
   A column argument fails analysis with 
`DATATYPE_MISMATCH.NON_FOLDABLE_INPUT`, and `schema_of_json(null)` fails with
   `DATATYPE_MISMATCH.UNEXPECTED_NULL`. In practice the result is a constant, 
and it is mostly used as the schema
   argument of `from_json`. The result is non-nullable. `options` is a 
`map(...)` of string literals with the same
   `JSONOptions` keys as the JSON data source. Available since Spark 2.4.0.
   
   Inference uses `JsonInferSchema.inferField` on the first token, followed by 
`canonicalizeType`, with a Jackson
   factory built from `JSONOptions` (UTC time zone). The rules are:
   
   - integers become `BIGINT`, or `DECIMAL(p, 0)` beyond `Long` up to precision 
38, and `DOUBLE` after that;
   - floats become `DOUBLE`, or `DECIMAL` with `prefersDecimal`;
   - booleans become `BOOLEAN`, and strings become `STRING` (`TIMESTAMP` only 
with `inferTimestamp`);
   - `null` and `""` start as `NullType` and are canonicalized to `STRING`;
   - objects become `STRUCT` with fields sorted by name;
   - array element types are merged with `compatibleType`, and an empty array 
becomes `ARRAY<STRING>`.
   
   ### Examples
   
   ```sql
   SELECT schema_of_json('[{"col":0}]');
   -- ARRAY<STRUCT<col: BIGINT>>
   SELECT schema_of_json('[{"col":01}]', map('allowNumericLeadingZeros', 
'true'));
   -- ARRAY<STRUCT<col: BIGINT>>
   SELECT schema_of_json('{"c1":0, "c2":[1]}');
   -- STRUCT<c1: BIGINT, c2: ARRAY<BIGINT>>
   SELECT schema_of_json('{"c1":1}', map('primitivesAsString', 'true'));
   -- STRUCT<c1: STRING>
   SELECT schema_of_json('{"c1":01, "c2":0.1}', map('allowNumericLeadingZeros', 
'true', 'prefersDecimal', 'true'));
   -- STRUCT<c1: BIGINT, c2: DECIMAL(1,1)>
   SELECT schema_of_json('{"col0":["a"], "col1": {"col2": "b"}}');
   -- STRUCT<col0: ARRAY<STRING>, col1: STRUCT<col2: STRING>>
   SELECT schema_of_json('{"id": null}');
   -- STRUCT<id: STRING>
   SELECT schema_of_json('{"id": ""}');
   -- STRUCT<id: STRING>
   SELECT schema_of_json(regexp_replace('{"item_id": 1, "item_price": 0.1}', 
'item_', ''));
   -- STRUCT<id: BIGINT, price: DOUBLE>
   SELECT schema_of_json('{"time": "14:30:45"}');
   -- STRUCT<time: STRING>
   SELECT schema_of_json('null', map('dropFieldIfAllNull', 'true'));
   -- STRING
   SELECT schema_of_json('{"id": "a", "drop": {"drop": null}}', 
map('dropFieldIfAllNull', 'true'));
   -- STRUCT<id: STRING>
   ```
   
   These come from the `ExpressionDescription`, `json-functions.sql.out`, 
`JsonExpressionsSuite` and `JsonFunctionsSuite`.
   
   ### Edge cases to test
   
   - Field ordering. Fields are sorted by name, so by code reading 
`{"b":1,"a":"x"}` gives
     `STRUCT<a: STRING, b: BIGINT>`.
   - Field names that need quoting in DDL: spaces (`{"a b": 100}` round-trips 
into `from_json`), backticks, and
     non-ASCII characters. These depend on how `DataType.sql` quotes 
identifiers.
   - Number widening. Integers beyond `Long` become `DECIMAL(p,0)`, for example 
`12345678901234567890` becomes
     `DECIMAL(20,0)` by code reading. Beyond precision 38 they become `DOUBLE`. 
With `prefersDecimal`, floats become
     `DECIMAL(max(precision, scale), scale)`. Exponent forms such as `1.0E10` 
become `DOUBLE`.
   - Array merging: `[1, 1.5]`, `[1, "a"]`, `[{"a":1},{"b":2}]` (struct union), 
`[null]`, `[]`, and nested empty arrays.
     Follow `JsonInferSchema.compatibleType` exactly.
   - Top-level scalars (`1`, `"x"`, `true`, `null`), and empty or 
whitespace-only input.
   - Empty structs (SPARK-8093). `canonicalizeType` drops struct fields whose 
type is an empty struct, and drops fields
     with an empty name (`""`). A struct left with no fields is removed, and at 
the top level that falls back to
     `STRUCT<>`. So by code reading, `{}` and `{"a":{}}` both give `STRUCT<>`.
   - Options: `primitivesAsString`, `prefersDecimal`, 
`allowNumericLeadingZeros`, `allowSingleQuotes` (default true),
     `allowUnquotedFieldNames` (`{a:1}`), `allowComments`, 
`dropFieldIfAllNull`, `inferTimestamp`, `timestampFormat`,
     `locale` (with `prefersDecimal`, a locale-formatted `1000.001` string is 
inferred as `DECIMAL(7,3)` for en-US,
     ko-KR, ru-RU and de-DE in `JsonExpressionsSuite`), and 
`allowNonNumericNumbers`.
   - Malformed JSON. `SchemaOfJsonEvaluator` has no `try`/`catch`, so a Jackson 
parse error or
     `malformedJSONError()` propagates as an error. It does not return NULL. 
Pin down the exact error before
     implementing it.
   - Trailing content after the first value is not read.
   - Non-foldable and NULL arguments are analysis-time errors, not runtime 
NULLs.
   
   ### Differences across Spark versions
   
   - None found in the inference logic between 3.4 and 4.2. 4.0 makes the 
expression `RuntimeReplaceable` (it calls
     `SchemaOfJsonEvaluator` through `Invoke`), and the result uses the default 
string type (collations).
   - Error messages: in 3.x the `NON_FOLDABLE_INPUT` parameter is `"inputName" 
: "json"`, and from 4.0 it is
     `` "`json`" ``.
   - 3.4 uses Jackson 2.14 (no number-length or nesting limits), and 3.5 and 
later have the limits.
   
   ### Implementation notes
   
   - Spark: `SchemaOfJson` in 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/jsonExpressions.scala`.
     The evaluator is `SchemaOfJsonEvaluator` in 
`.../expressions/json/JsonExpressionEvalUtils.scala`, inference is in
     
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/json/JsonInferSchema.scala`
 (`inferField`,
     `compatibleType`, `canonicalizeType`), and the DDL rendering is 
`DataType.sql`.
   - DataFusion core has no equivalent. Arrow's 
`arrow_json::reader::infer_json_schema` uses Arrow types and different
     merge rules, for example `Int64` against `Float64`, and no DECIMAL 
inference. It does not sort fields and has no
     Spark DDL output, so it can't be used as it is.
   - Because the input must be foldable, this can be planned as a constant: a 
scalar UDF that rejects non-scalar input
     and returns `Utf8`. Reuse the lenient JSON tokenizer proposed for the 
other JSON functions in
     `crates/spark/src/function/json/`. This pairs naturally with `from_json`, 
which needs the reverse step of parsing
     a Spark DDL string into an Arrow type.
   - Add `crates/sqllogictest/test_files/json/schema_of_json.slt` and document 
the function with `#[user_doc]`.
   
   ---
   _This issue was generated by an LLM (Claude) from the Apache Spark source 
code and tests. Examples come from Spark's `ExpressionDescription` examples, 
test suites and `.sql.out` golden files, but please verify them against Spark 
before relying on them._


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to