You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by ro...@barclays.com on 2014/11/10 17:35:59 UTC
Flattening or Un-nesting Nested Data
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<ma...@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.
_______________________________________________
Re: Flattening or Un-nesting Nested Data
Posted by Jacques Nadeau <ja...@apache.org>.
Hey Robert,
As Rahul said, this is in active development and is already partially
merged into master. If you want to post some of your example data some
place, we'll be sure to include it in our testing of the feature.
Hopefully, this should be in a release in the next couple week.
thanks,
Jacques
On Mon, Nov 10, 2014 at 9:48 AM, rahul challapalli <
challapallirahul@gmail.com> wrote:
> 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, <ro...@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<ma...@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.
> >
> > _______________________________________________
> >
>
Re: Flattening or Un-nesting Nested Data
Posted by Ted Dunning <te...@gmail.com>.
In the mean time, you can simply reference the fields you are interested in
without flattening the data structure.
On Mon, Nov 10, 2014 at 11:48 AM, rahul challapalli <
challapallirahul@gmail.com> wrote:
> 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, <ro...@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<ma...@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.
> >
> > _______________________________________________
> >
>
Re: Flattening or Un-nesting Nested Data
Posted by rahul challapalli <ch...@gmail.com>.
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, <ro...@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<ma...@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.
>
> _______________________________________________
>