HIVE query with Lateral View,Json_Tuple

2
votes

I have a json file like this 

{"client":[{"name":"xyz","id":"123"}]}

I have created hive external table 

create external table clients (client  STRING) LOCATION '  '

I dropped the json file in the table location.

I am trying to get the name,id fields using LATERAL VIEW, JSON_TUPLE.

select v2.name,v2.id
from clients c
lateral view json_tuple(c.client,'client') v1 as client
lateral view json_tuble(v1.client,'id','name') v2 as id,name

I know above query would work if there are no square brackets in json file.

I am not able to find anything related to this.
How to deal with ARRAY nodes in json file while using LATERAL VIEW,JSON_TUPLE?

1
hivehiveql

1 Answers

0
votes

How about regexp_replace the '[' with ''?

select v2.name,v2.id
from clients c
lateral view json_tuple(regexp_replace(c.client,'[|]',''),'client') v1 as client
lateral view json_tuble(v1.client,'id','name') v2 as id,name

Something along these lines should work