Hi Charles, Essentially, i want to make use of JSONPath in the select clause of SQL for the complex use cases where just by using "." or "[" to navigate JSON won't be sufficient.
*Thanks and Regards,* *Vimal Jain* On Mon, Sep 14, 2020 at 10:33 PM Charles Givre <[email protected]> wrote: > To add to what Paul is saying, can you explain a bit more about what your > goals are with JSON Path? > -- C > > > On Sep 14, 2020, at 12:53 PM, Paul Rogers <[email protected]> wrote: > > > > Hi Vimal, > > > > Drill does not support JSON Path. Instead, Drill attempts to read your > JSON > > into records which you can then manipulate in SQL. Drill supports JSON > > structure to some degree: nested records, arrays of a single type (with > no > > nulls.) More recent versions do provide a way to ignore some part of the > > JSON, such as ignoring the message body of a REST response, to focus just > > on the "payload" portion of the response. > > > > It is possible to add JSON Path by creating a new format plugin. The > > simplest JSON path implementations load your data into memory to simplify > > path queries. Since Drill queries data at scale, if your files are large, > > loading the entire file can be problematic. Instead, you'd want a > streaming > > path solution, or load a single object at a time and apply path rules to > > that. > > > > Another consideration is that using path rules on each record can be > > expensive and is a cost paid for every query, which will slow > performance. > > If you will query the data multiple times, you may find it more effective > > to perform an ETL from the JSON format into a Parquet as a separate step, > > then query the Parquet format to get good query performance. > > > > Thanks, > > > > - Paul > > > > > > On Mon, Sep 14, 2020 at 7:29 AM Vimal Jain <[email protected]> wrote: > > > >> Hi There, > >> I am using Drill 1.17. > >> I have complex json data in AWS S3 to query. > >> I am looking to see if there is already a support in drill to query > using > >> JSON Path ( similar to XPath for XML , for ref - > >> https://goessner.net/articles/JsonPath/ ) > >> If it's not possible by default , is there we can make use of it ? ( > like > >> UDF etc ? ) > >> > >> *Thanks and Regards,* > >> *Vimal Jain* > >> > >
