Home | About | Sematext search-lucene.com search-hadoop.com
 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 ??
Connell, Chuck 2012-09-07, 19:27
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