Yep: 0: jdbc:drill:zk=local> select jblob['v'] from (select convert_from(columns[1], 'JSON') as jblob from `AncestrySample100.tsv`) limit 1; +------------+ | EXPR$0 | +------------+ | 3.0 | +------------+ 1 row selected (0.136 seconds)
Thanks Carol! On Fri, Apr 3, 2015 at 9:28 AM, Carol McDonald <[email protected]> wrote: > maybe something like > > select convert_from(t.columns[1], 'JSON') from AncestrySample100.tsv t > > On Fri, Apr 3, 2015 at 9:06 AM, Vince Gonzalez <[email protected]> > wrote: > > > Can I tell Drill to parse the JSON in a column of a TSV? > > > > cd /tmp > > curl -L --output AncestrySample100.tsv > > > > > https://raw.githubusercontent.com/ThinkBigAnalytics/ThinkBigChallenge2014/master/data/AncestrySample100 > > > > ... > > > > 0: jdbc:drill:zk=local> use dfs.tmp; > > 0: jdbc:drill:zk=local> select columns[1] from `AncestrySample100.tsv` > > limit 1; > > +------------+ > > | EXPR$0 | > > +------------+ > > | {"v":"3.0","Persons":{"Genders":[{"id":"2168621643","q":381,<snip> > > +------------+ > > 1 row selected (0.135 seconds) > > 0: jdbc:drill:zk=local> select columns[1]['v'] from > `AncestrySample100.tsv` > > limit 1; > > +------------+ > > | EXPR$0 | > > +------------+ > > | null | > > +------------+ > > 1 row selected (0.117 seconds) > > > > The query that attempts to look inside the JSON blob in columns[1] > returns > > null for all rows, presumably because drill doesn't try to parse JSON at > > all given the .tsv extension? > > >
