[jira] [Commented] (DRILL-5974) Read JSON non-relational fields using text mode

["Aman Sinha (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-5974?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16258550#comment-16258550 ] 

Aman Sinha commented on DRILL-5974:
-----------------------------------

For the specific example of the 2-D array, Drill currently can query it using the array [i] [j] syntax, as shown below (I took this example from the Drill docs page [1]):
{noformat}
SELECT features[0].geometry.coordinates[0][1] from dfs.`/Users/asinha/data/json/polygon`;
+-----------------------------------------------+
|                    EXPR$0                     |
+-----------------------------------------------+
| [-122.42207601332528,37.808835019815085,0.0]  |
+-----------------------------------------------+
{noformat}

If we treat it as text mode,  my understanding is the above query will no longer work.   
Independent of this example, I do agree that there are other types of JSON structures that Drill cannot process without setting the all_text_mode, so those could benefit.  For instance, a list with heterogenous types,  list with NULL values.  

[1] https://drill.apache.org/docs/json-data-model/

> Read JSON non-relational fields using text mode
> -----------------------------------------------
>
>                 Key: DRILL-5974
>                 URL: https://issues.apache.org/jira/browse/DRILL-5974
>             Project: Apache Drill
>          Issue Type: Improvement
>    Affects Versions: 1.13.0
>            Reporter: Paul Rogers
>            Assignee: Paul Rogers
>             Fix For: 1.13.0
>
>
> Proposed is a minor enhancement to the JSON reader to better handle non-relational JSON structures.
> As background, Drill handles simple tuples:
> {code}
> {a: 10, b: “fred”}
> {code}
> Drill also handles arrays:
> {code}
> {name: “fred”, hobbies: [“bowling”, “golf”]}
> {code}
> Drill even handles arrays of tuples:
> {code}
> {name: “fred”, orders: [
>   {id: 1001, amount: 12.34},
>   {id: 1002, amount: 56.78}]}
> {code}
> The above are termed "relational" because there is a straightforward mapping to/from tables into the above JSON structures.
> Things get interesting with non-relational types, such as 2-D arrays:
> {code}
> {id: 4, shape: “square”, points: [[0, 0], [0, 5], [5, 0], [5, 5]]}
> {code}
> Drill has two solutions:
> * Turn on the experimental list and union support.
> * Enable all-text mode to read all fields as JSON text.
> Proposed is a middle ground:
> * Read fields with relational types into vectors.
> * Read non-relational fields using text mode.
> Thus, the first three examples would all result in the JSON data parsed into Drill vectors. But, the fourth, non-relational example would produce a row that looks like this:
> {noformat}
> id, shape, points
> 4, “shape”, “[[0, 0], [0, 5], [5, 0], [5, 5]]”
> {noformat}
> Although Drill can’t parse the 2-D array, Drill will pass the array along to the client, which can use its favorite JSON parser to parse the array and do something useful (like draw the square in this case.)
> Specifically, the proposal is to:
> * Apply this change only to the revised “batch size aware” JSON reader.
> * Use the above parsing model by default.
> * Use the experimental list-and-union support if the existing {{exec.enable_union_type}} system/session option is set.
> Existing queries should “just work.” In fact, now JSON with non-relational types will work “out-of-the-box” without all-text mode or the experimental types.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)