andygrove opened a new issue, #241:
URL: https://github.com/apache/datafusion-spark/issues/241
Part of #176 and apache/datafusion-comet#7300.
### What the function does
`get_json_object(json_txt, path)` extracts the value at a JSONPath-like
`path` from the JSON string `json_txt` and
returns it as a `STRING`. String leaves are returned without quotes;
objects, arrays, numbers and booleans are
returned as re-serialized JSON text. The result is always nullable: NULL for
NULL input, NULL path, an unparsable
path, a path that does not match, a JSON `null` at the end of a named-field
path, or malformed JSON.
Both arguments must be strings (`ExpectsInputTypes`, so there is no implicit
cast). From 4.0 the inputs are
`StringTypeWithCollation(supportsTrimCollation = true)` and the result
carries the default string type. Available
since Spark 1.5.0. No aliases.
Spark does not use a general JSONPath library. It has its own small path
grammar (`JsonPathParser`) and evaluates it
in a single streaming pass over a Jackson parser. The `JsonFactory` enables
`ALLOW_SINGLE_QUOTES` and
`ALLOW_UNESCAPED_CONTROL_CHARS` (for Hive compatibility). Output goes
through a Jackson `JsonGenerator`.
### Examples
```sql
SELECT get_json_object('{"a":"b"}', '$.a');
-- b
SELECT get_json_object('[{"a":"b"},{"a":"c"}]', '$[0].a');
-- b
SELECT get_json_object('[{"a":"b"},{"a":"c"}]', '$[*].a');
-- ["b","c"]
SELECT get_json_object('{''name'': ''fang'', ''age'': 5}', '$.name');
-- fang
SELECT get_json_object('{"f1": "", "f5": null}', '$.f1');
-- (empty string)
SELECT get_json_object('{"f1": "", "f5": null}', '$.f5');
-- NULL
SELECT get_json_object('[invalid JSON string]', '$.f1');
-- NULL
SELECT get_json_object('[1,2,3]', '$[*]');
-- [1,2,3]
SELECT get_json_object('[1,2,3]', '$.[0]');
-- NULL
SELECT get_json_object('[1,2,3]', '$[3]');
-- NULL
SELECT get_json_object('{"basket":[[1,2,{"b":"y","a":"x"}],[3,4],[5,6]]}',
'$.basket[*][0]');
-- [1,3,5]
SELECT get_json_object('{"basket":[[1,2,{"b":"y","a":"x"}],[3,4],[5,6]]}',
'$.basket[*][*]');
-- [1,2,{"b":"y","a":"x"},3,4,5,6]
SELECT get_json_object('{"basket":[[1,2,{"b":"y","a":"x"}],[3,4],[5,6]]}',
'$.basket[0][*].b');
-- ["y"]
SELECT get_json_object('{ "key with spaces": "it works" }', '$[''key with
spaces'']');
-- it works
```
The first three come from the `ExpressionDescription`. The rest come from
`JsonFunctionsSuite` and
`JsonExpressionsSuite`, with the `basket` paths moved from `$.store.basket`
to a smaller document. The evaluation
logic is the same for both.
### Edge cases to test
- NULL `json_txt`, NULL `path`, and an empty string for either one. All of
these return NULL.
- Invalid paths return NULL: `[0]`, `$0`, `0`, `$.`, `$.[0]`, `$.[*]`,
`$..x`, `$[-1]`. Also test an index too large for a
`Long` (Spark calls `toLong` inside the parser, so confirm what happens).
- Path grammar: a `.name` segment may contain any character except `.` and
`[`, so `$.zip code` and `$.fb:testid` are
valid. A `['name']` segment may contain anything except `'`. In 3.4 and
3.5 it also could not contain `?`.
- Object wildcards (`$.*`, `$['*']`, `$.a.*`) parse but never match, so the
result is NULL. Only array `[*]` is
effective.
- Wildcard output wrapping. Under a single `[*]` a single match is unwrapped
(`$.store.book[*].reader[0].age` returns
`25`). If the wildcard follows an index (`[0][*]`) or is a nested
wildcard, the result is always wrapped (`["y"]`
above). `[*][*]` flattens one level. A wildcard that matches nothing
returns NULL.
- A JSON `null` directly under a named field is not a match (NULL result). A
`null` reached through an array index or
wildcard is written as the text `null`, for example
`get_json_object('[null]', '$[0]')`.
- Duplicate keys. By code reading, the first occurrence whose remaining path
matches wins, and later occurrences
are skipped: `{"a":1,"a":2}` with `$.a` gives `1`. If an earlier
occurrence is `null` or misses, a later one can
still match: `{"a":null,"a":2}` gives `2`.
- Lenient input. Single-quoted keys and strings are accepted. Raw
(unescaped) control characters such as a literal
tab or newline are accepted inside strings. Top-level string results are
written raw, and strings inside returned
containers are re-escaped by the generator.
- Trailing content. Spark stops reading once the path is resolved and never
checks for EOF. By code reading,
`get_json_object('{"a":1} garbage', '$.a')` returns `1`. Malformed content
inside the part that is read (for
example `{"a":1,"b":}`) returns NULL.
- Number rendering. Returned values are re-serialized with Jackson's
`copyCurrentStructure`. Floats are parsed as
`double` and written with `Double.toString`: `1.0E10`, `1.0E-4` for
`0.0001`, `1.5` for `1.50`, `-0.0`. Integers keep
their exact digits, including values beyond `Long`. This also applies to
numbers nested inside a returned object or
array.
- Re-serialization of returned containers drops whitespace and turns single
quotes into double quotes, for example
`{'b':'x'}` becomes `{"b":"x"}`.
- Jackson stream limits (3.5+). A number longer than 1000 digits, or nesting
deeper than 1000, anywhere in the
document makes the whole call return NULL, even if that part is never
selected.
- Large strings (Spark tests a 3000-character value), multi-byte UTF-8 in
keys and values, and `\uXXXX` escapes,
including surrogate pairs.
- Scalar and column for each argument. Spark caches the parsed path when
`path` is foldable.
- The function is not affected by ANSI mode.
### Differences across Spark versions
- Jackson version: 3.4 uses 2.14.2 (no default number-length or nesting
limits), 3.5 uses 2.15.2, 4.0 uses 2.18.6,
and 4.1 and 4.2 use 2.21.2. 3.5 and later reject over-long numbers and
deep nesting.
- In 3.4 and 3.5 the `['name']` segment regex is `[^\'\?]+`. From 4.0 it is
`[^\']+`, so `?` is allowed.
- 4.0 moves evaluation into `GetJsonObjectEvaluator` (no behaviour change)
and adds collation support for both inputs.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/jsonExpressions.scala`
(`GetJsonObject`). The path parser and evaluator are in
`.../expressions/json/JsonExpressionEvalUtils.scala`
(`JsonPathParser`, `GetJsonObjectEvaluator.evaluatePath`,
`SharedFactory`). In 3.x everything is in
`jsonExpressions.scala`.
- DataFusion core has no JSON functions.
- In datafusion-spark, `crates/spark/src/function/json/json_tuple.rs` parses
with `serde_json::Value`. That parser is
strict: it rejects single quotes and raw control characters, and keeps the
last of duplicate keys. So it does not
match Spark's Jackson setup, but it is the natural home for a shared,
Jackson-compatible lenient tokenizer that
`get_json_object`, `json_tuple`, `json_array_length` and
`json_object_keys` could all use. Port the
`evaluatePath` state machine (Raw/Quoted/Flatten write styles) as it is
rather than mapping it onto a general
JSONPath engine.
- Put it in `crates/spark/src/function/json/`, add
`crates/sqllogictest/test_files/json/get_json_object.slt`, and
document it 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]