drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andy Pernsteiner <apernstei...@maprtech.com>
Subject Re: Accessing array index inside JSON
Date Thu, 31 Jul 2014 18:58:49 GMT
Thank you, i'll grab the latest build and re-test.


On Thu, Jul 31, 2014 at 2:57 PM, Bob Rumsby <brumsby@maprtech.com> wrote:

> Andy,
> I think the syntax is right. Here is a similar example that works for me.
>
> 0: jdbc:drill:zk=local> select t.batters.batter[0] from
> dfs.`/Users/brumsby/drill/donuts.json` t;
> +------------+
> |   EXPR$0   |
> +------------+
> | {"id":"1001","type":"Regular"} |
> +------------+
> 1 row selected (0.319 seconds)
> 0: jdbc:drill:zk=local> select t.batters.batter from
> dfs.`/Users/brumsby/drill/donuts.json` t;
> +------------+
> |   EXPR$0   |
> +------------+
> |
>
> [{"id":"1001","type":"Regular"},{"id":"1002","type":"Chocolate"},{"id":"1003","type":"Blueberry"},{"id":"1004","type":"Devil's
> Food"}] |
> +------------+
> 1 row selected (0.084 seconds)
>
>
>
> On Thu, Jul 31, 2014 at 11:45 AM, Andy Pernsteiner <
> apernsteiner@maprtech.com> wrote:
>
> > Hello, I'm currently attempting to perform queries against a JSON file
> > which has some nested elements, some of which are arrays.  Here are 2
> > records worth of the file:
> >
> >
> >
> >
> {"trans_id":16,"date":"06/08/2012","time":"07:09:25","user_info":{"cust_id":6,"device":"IOS5","state":"tx"},"marketing_info":{"camp_id":17,"keywords":[]},"trans_info":{"prod_id":[65,1233,182,70,90,12,1520,1563,2144],"purch_flag":"false"}}
> >
> >
> >
> {"trans_id":17,"date":"07/31/2013","time":"16:22:26","user_info":{"cust_id":9,"device":"IOS5","state":"oh"},"marketing_info":{"camp_id":1,"keywords":["careful","us","around","you","whatever","i","how"]},"trans_info":{"prod_id":[10,2597,2658,2202,999],"purch_flag":"false"}}
> >
> >
> >
> > Note that the "prod_id" and "keywords" fields contain arrays.   Here's an
> > example query without trying to specify the array index:
> >
> > 0: jdbc:drill:> select t.trans_info.prod_id from
> > dfs.`/myfiles/JSON/mobile.json` t limit 3;
> >
> > +------------+
> > |   EXPR$0   |
> > +------------+
> > | [1435,673] |
> > | [785]      |
> > | [26]       |
> > +------------+
> > 3 rows selected (0.507 seconds)
> >
> >
> > So, I can see the array elements just fine, but when I try to access a
> > specific one I come up w/ nulls:
> >
> >
> > 0: jdbc:drill:> select t.trans_info.prod_id[0] from
> > `dfs`.`/myfiles/JSON/mobile.json` t limit 10;
> > +------------+
> > |   EXPR$0   |
> > +------------+
> > | null       |
> > | null       |
> > | null       |
> > | null       |
> > | null       |
> > | null       |
> > | null       |
> > | null       |
> > | null       |
> > | null       |
> > +------------+
> > 10 rows selected (0.303 seconds)
> >
> >
> > Am I using the correct notation/subscript syntax here?
> >
> > Thanks!
> >
> >
> > --
> >  Andy Pernsteiner
> >  Manager, Field Enablement
> > ph: 206.228.0737
> >
> > www.mapr.com
> >
>



-- 
 Andy Pernsteiner
 Manager, Field Enablement
ph: 206.228.0737

www.mapr.com

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