Handling arrays returned by json_tuple ??

["Connell, Chuck" <Ch...@nuance.com>]

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

RE: Handling arrays returned by json_tuple ??

["Connell, Chuck" <Ch...@nuance.com>]

Something else... If json_tuple cannot select elements in an array, that means that JSON objects within an array are essentially "frozen" within their array. So if I had

{"text1" : "smith", "array1" : [{json-object},{json-object}]}
{"text1" : "jones", "array1" : [{json-object},{json-object}]}

I could extract only the top level value array1, but could not "open up" that array to do anything with its embedded elements which are valid json objects!  Is this true?

Chuck


________________________________
From: Connell, Chuck
Sent: Friday, September 07, 2012 3:27 PM
To: user@hive.apache.org
Subject: 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