incubator-drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rahul challapalli <challapallira...@gmail.com>
Subject Re: Flattening or Un-nesting Nested Data
Date Mon, 10 Nov 2014 17:48:29 GMT
Hi Robert,

This feature is currently under development. When this feature first gets
released we might just support applying the "flatten" function on an array
of maps or scalars. Below is a sample json record

{
  "name" : "Mr Lucky",
  "cars" : [
    {"make":"Jaguar", "model":"S-Type"},
    {"make":"Aston Martin", "model":"DB-9"},
    {"make":"Porsche", "model":"911"}
  ]
}

For the above dataset we can use the below query

select sub.name, sub.car.make, sub.car.model from (select name,
flatten(cars) car from cars.json) sub;

And the output looks something like the below

 Mr Lucky    Jaguar      S-Type
 Mr Lucky    Aston Martin  DB-9
 Mr Lucky    Porsche     911

Let me know if I misunderstood your question

- Rahul



On Mon, Nov 10, 2014 at 8:35 AM, <robert.x.harrison@barclays.com> wrote:

> Hi,
>
> Does Drill support flattening or un-nesting nested data?
>
> For example, given:
> <person>
>             <name>Mr Lucky</name>
>             <car>
>                         <make>Jaguar</make>
>                         <model>S-Type</model>
>             </car>
>             <car>
>                         <make>Aston Martin</make>
>                         <model>DB-9</model>
>             </car>
>             <car>
>                         <make>Porsche</make>
>                         <model>911</model>
>             </car>
> </person >
>
> Could you in principle do something like:
> SELECT p.name AS name, c.make AS make, c.model AS model
> FROM person p
> JOIN UNNEST(p.car) c
>
> To obtain:
> name
>
> make
>
> model
>
> Mr Lucky
>
> Jaguar
>
> S-Type
>
> Mr Lucky
>
> Aston Martin
>
> DB-9
>
> Mr Lucky
>
> Porsche
>
> 911
>
>
> Regards,
> Rob H.
>
> Robert Harrison   I   Data Systems and Insights
> Enterprise Data Distribution Infrastructure   I   Technical Staff
> Tel +972 (0)3 54 52428   I   Mobile +972 (0)54 335 8969   I   Email
> robert.x.harrison@barclays.com<mailto:robert.x.harrison@barclays.com>
> Barclays, Atidim High-Tech Industrial Park, Tel Aviv 61580
> barclays.com
>
>
> _______________________________________________
>
> This message is for information purposes only, it is not a recommendation,
> advice, offer or solicitation to buy or sell a product or service nor an
> official confirmation of any transaction. It is directed at persons who are
> professionals and is not intended for retail customer use. Intended for
> recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary
> from Barclays Sales and/or Trading, who are active market participants; and
> in respect of Barclays Research, including disclosures relating to specific
> issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message