Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Handling arrays returned by json_tuple ??


Copy link to this message
-
Handling arrays returned by json_tuple ??
I am using the json_tuple lateral view function. It works fine. But I am wondering how to select individual elements from a returned array.

Here is an example...

$ cat array1.json

{"text1" : "smith", "array1" : [6,5,4]}
{"text1" : "jones", "array1" : [1,2,3]}
{"text1" : "white", "array1" : [9,8,7]}
{"text1" : "black", "array1" : [10,11]}

hive> create table t7 (json string);

hive> load data inpath '/tmp/array1.json' overwrite into table t7;

hive> select ar1 from t7 lateral view json_tuple(t7.json, 'text1', 'array1') view1 as t1, ar1;

[6,5,4]
[1,2,3]
[9,8,7]
[10,11]

Notice that the answer is correct; these are the arrays within the JSON array1 field.

But how can I get just one of the values out of the query, such as ar1[1] ? I want the answers

5
2
8
11

I have tried every syntax I can think of, including the explode() function. No luck. Is this possible?

TIA,
Chuck

NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB