[FEA] JSON input support

[revans2]

Is your feature request related to a problem? Please describe.

High Priority

GetJsonObject/get_json_object

get_json_object is being traced by a separate epic. One of them is in the spark-rapids repo and another is in spark-rapids-jni. The JNI version is trying to write a new parser from scratch to match what Spark is doing.

• [FEA] Fix GetJsonObject #10254
• [FEA] GetJsonObject: Implement get-json-object in JNI repo as Spark does spark-rapids-jni#1823

JsonTuple/json_tuple

For now it is implemented in terms of multiple calls to get_json_object. This is likely to change in the future, but for now it should be tracked with get_json_object

JsonToStructs and ScanJson

JsonToStructs and ScanJson share a common backend and most bugs in one are reflected in the other with the exception of a few issues that are specific to how JsonToStructs prepares it's input so that the CUDF JSON parser can handle it.

• [FEA] JSON reader parses types compatible with Spark #4609
  • [FEA] Support reading JSON numeric values as timestamps and dates #4940
  • [FEA] JSON number normalization when returned as a string rapidsai/cudf#15318
• [BUG] setting timestampFormat/dateFormat for JsonToStructs appears to fall back too often and also not often enough #10485
• [BUG] time zone checks for from_json are too broad and do not follow JsonScan #10453
• [BUG] GPU JSON reader fails to read the JSON string of an empty body  #7616
• [BUG] JsonToStructs fails to parse all empty dicts and invalid lines #10483
• [FEA]Support nested types for parsing JSON #4608
  • [FEA] Support nested types in JSON reader rapidsai/cudf#8827
  • [FEA] Support casting of LIST type to STRING in JSON rapidsai/cudf#15278
  • [BUG] mixed_type_as_string throws exception for nested data with nested STRING schema request rapidsai/cudf#15260
  • [FEA] Add option to read JSON field as unparsed string rapidsai/cudf#14239
• [BUG] from_json generated inconsistent result comparing with CPU for input column with nested json strings #8558
  • [FEA] Support casting of LIST type to STRING in JSON rapidsai/cudf#15278
• [BUG] from_json does not support input with \n in it. #10489
  • [FEA] Find a way to support String column input/fixup for JSON parsing rapidsai/cudf#15277
• [FEA] Support allowUnquotedControlChars for JsonToStructs and ScanJson #4612
  • [FEA] Options to validate JSON fields rapidsai/cudf#15222
• [AUDIT][SPARK-41248][SQL] Add "spark.sql.json.enablePartialResults" to enable/disable JSON partial results #7482
• [BUG] StructsToJson does not fall back to the CPU for unsupported timeZone options #10486
• [BUG] JsonToStructs and ScanJson should return null for non-numeric, non-boolean non-quoted strings #10479
  • [FEA] Options to validate JSON fields rapidsai/cudf#15222
• [BUG] ScanJson and JsonToStructs cannot handle nested empty arrays/structs #10595
• [BUG] ScanJson and JsonToStructs does not deal with escaped single quotes properly #10596
  • [FEA] JSON parsing is not handling escaped single quote the same as Spark rapidsai/cudf#15303
• [AUDIT][SPARK-41248][SQL] Add "spark.sql.json.enablePartialResults" to enable/disable JSON partial results #7482
• [FEA] enable prune_columns for from_json #11458 (Performance)
• [BUG] JSON Scan and JsonToStruct should invalidate an array on out of bounds #11491

Medium Priority

JsonToStructs and ScanJson

• [FEA] Understand why we were able to parse a timestamp correctly for America/Los_Angeles when all we support is UTC.  #10488
• [BUG] GpuCsvUtils and GpuJsonUtils should be under a rapids package #4951
• [FEA] JSON Reader: Support dropFieldIfAllNull option #4718
• [FEA]JSON reader: support "allowBackslashEscapingAnyCharacter" #4616
• [FEA] [JSON] Support timeParserPolicy=LEGACY when parsing dates and timestamps in from_json #9774
• [FEA] Review JsonToStruct and JsonScan and consolidate some testing and implementation #9750
• [BUG] JsonToStructs and ScanJson do not normalize numeric output when read as a string #10458
• [BUG] ScanJson and JsonToStructs allow unquoted control chars by default #10457
• [BUG] Need Improved JSON Validation  #10534
• [BUG] refine timestamp for JsonToStructs and ScanJson #10535
• [BUG] ScanJson and JsonToStructs don't process escapes in key names #10607
• [FEA] JsonToStructs and ScanJson should invalidate the entire array on overflow #10573
• [BUG] JsonToStructs and ScanJson does not normalize non quoted strings when read as strings #10574
• [BUG] JsonToStructs does not match parsing invalid structs on Spark 3.4.1 and below #10588
• [BUG] For from_json and scan json parsing a quoted string int for Spark 3.2 is wrong #11390
• [BUG] For from_json and scan json parsing a quoted string timestamp for Spark 3.2 is wrong #11391

Low Priority

JsonToStructs and ScanJson

• [FEA]JSON reader: support comments #4610
• JSON reader: Support lineSep configuration option
• JSON reader: Support multiLine configuration option
• JSON reader: Support primitivesAsString configuration option
• JSON reader: Support prefersDecimal configuration option
• [FEA][JSON] Support parsing single numbers in strings as dates in from_json #9664
• [FEA][JSON] Support more timestamp formats in from_json #9723
• [FEA] Add support for constant timestamp values such as "now" in from_json (Spark 3.1.x only) #9724
• [BUG] ScanJson on versions prior to 3.3.0 where a double quote " appears in a single quoted string produce the wrong answer on CPU #10495
• [BUG] On versions of Spark prior to 3.3.0 we produced incorrect JSON results for allowNonNumericNumbers #10493
• [FEA] Revisit GpuTextBasedPartitionReader and how returned values are cast to the final result #10539
• [FEA] Support allowUnquotedFieldNames for JsonToStructs and ScanJson #10587
  • [FEA] JSON reader: support unquoted JSON field names. rapidsai/cudf#10266

Testing

JsonToStructs and ScanJson

• [FEA] Finish the JSON test matrix #10491
• [FEA] Improve timestampFormat support in GpuJsonScan and make tests consistent with GpuStructsToJson #10044
• [FEA] remove duplicate tests for JSON operators #10492

[revans2]

We have had a customer ask about this, so we might bump up the priority on this. I have been looking at the JSON parsing and how that relates to the existing CSV parsing. Just like CSV parsing it is not great, but I think we could do with JSON what we want to do with CSV and parse all of the atomic types as Strings and then handle casting/parsing them ourselves. This will make the code a lot more robust in terms of Spark compatibility. But there are still a number of issues that we have to look into and probably address.

1. The current CUDF code does not support nested types for parsing JSON. [FEA] Support nested types in JSON reader rapidsai/cudf#8827 should fix that, but it is not done yet.
2. The current CUDF code parses types how they want to, and not how Spark wants them to be parsed. Like I said above we might be able to ask CUDF to read all of the types as Strings and then parse them ourselves. This will not fix everything because looking at cast there are a number of types that we do not fully support when casting from a string still. But it will be a step in the right direction and should let us avoid most of the enable this type for JSON configs. We could at a minimum reused the cast from string configs that already exist.
3. "allowSingleQuotes" option. This is set to true by default so Spark allows parsing values with single quotes instead of just double quotes. The CUDF code does not. So we probably want to file an issue with CUDF to ask for this, and also in the short term document that we cannot support this.
4. There are also a number of configs in Spark that we will need to play around with in CUDF, but I am fairly sure if they are enabled we will have to fall back to the CPU, or at least really document well the inconsistencies. These include
   a. "allowComments" C/C++ style comments // and /* */
   b. "allowUnquotesFieldNames"
   c. "allowNumericLeadingZeros" Here the parser strips the leading zeros for numeric JSON types instead of marking them as corrupt entries, so if I had something like {"a": 0001, "b": 01.1000000000} both entries would cause the row to be marked as corrupt. Even if we provide a schema an ask for both a and b to be strings they show up as nulls because the values are technically corrupt. If I set the option to true they are parsed, but the leading and in the case of the float, trailing zeros are stripped from the resulting string.
   d. "allowNonNumericNumbers" The docs say that this mean INF, -INF, and NaN but I could not make INF work, just -INF. Not sure how well used this is.
   e. "allowBackslashEscapingAnyCharacter" typically only a few chars in the JSON standard are allowed. In CUDF they only support ", \, \t, \r, and \b. Not sure if there are others in JSON or not. Also not sure what happens if CUDF if others are encountered vs in Spark.
   f. "allowUnquotedControlChars" Docs say ASCII characters with a value less than 32. My guess is CUDF just allows these all the time.
5. The "parseMode" and "columnNameOfCorruptRecord" parse mode config probably needs to always be lenient and we need to fall back to the CPU like with CSV if we ever see the columnNameOfCorruptRecord in the schema. We just don't support that and I don't know what it would take for CUDF to be able to do it.
6. "ignoreNullFields" and "dropFieldIfAllNull" are things we might be able to do with post processing, but we need to play around with it a little.
7. IF we see multi-line we have to fall back to the CPU. We just cannot support it.
8. We need to look at encodings and see if we have to fall back to the CPU or not. I hope that we can normalize things as we read it in like we do with the line ending in CSV and will have to do with the line ending here, but I am not 100% sure on that.
9. We need to check the zoneId like with CSV and fallback if it is not UTC
10. Need to check the date/timestamp formats too and see if there is anything in there that we cannot support.
11. We also need to play around with JSON parsing in general and see what falls out. It looks like Spark is fairly strict in parsing and there can be odd corner cases. For example it looks like unquoted number values, if they are going to be interpreted as a string, are parsed as numbers first and then cast to a string, So 3.00 becomes 3.0 but "3.00" remains unchanged. I don't see any way we can match this behavior and probably will just need to document it.

[revans2]

Oh, also a single line can contain multiple entries if the top level is an array.

[{"a": 1, "b": 1}, {"a": 2}]
[{"a": 1, "b": 2.0}]
{"a": 1, "b": "inf"}

produces

+---+----+
|  a|   b|
+---+----+
|  1|   1|
|  2|null|
|  1| 2.0|
|  1| inf|
+---+----+

But if there is extra JSON like stuff at the end of the line, it is ignored.

{"a": 1, "b": 1}, {"other": 2}
{"a": 1, "b": "inf"} garbage
{"a": 1, "b": 1} {"more": 2}

produces the following with no errors. Which feels really odd to me.

+---+---+
|  a|  b|
+---+---+
|  1|  1|
|  1|inf|
|  1|  1|
+---+---+

There may be a lot of other odd cases, that we need to look into.

[revans2]

We might want to look at some of the Spark JSON tests, but they are not that complete.

[revans2]

Oh that is interesting. The parsing of the JSON keys is case sensitive, but auto detection of the schema is not totally so you can get errors if you let Spark detect the schema and there are keys with different cases. i.e. A vs a. So we should test if we can select the keys in a case sensitive way.

Also if there are multiple keys in a record. For spark it looks like the last one wins. Not sure what CUDF does in those cases.

{"a": 1, "b": 1}
{"a": 1, "a": 2}
{"a": 1, "b": 1}

produces

+---+----+
|  a|   b|
+---+----+
|  1|   1|
|  2|null|
|  1|   1|
+---+----+

with no errors

[revans2]

Added Needs Triage back on so we can look at this again because I think most of the analysis of this is done.