CODEWITHSUNDEEP
jsonoracle-databasehadoophive
jlp
jlp

Reputation: 1706

HIVE, How to get an element from an array, the element itself is an array too

I have a database table that has one column storing JSON format strings. The string itself contains multuple elements like an array. Each element contains multiple key-value pairs. Some value may also contain multiple key-value pairs as well, for example, the "address" attribute below. 

People table:
  Col1      Col2   .....   info
  aaa       bbb           see below

For the column "info", it contains the following JSON format string:

 [{"name":"abc", 
  "address":{"street":"str1", "city":"c1"},
  "phone":"1234567"
 },
 {"name":"def", 
  "address":{"street":"str2", "city":"c1", "county":"ct"},
  "phone":"7145895"
 }
]

I need to get the single value of each field within the JSON string. I am able to do that for all fields except the "address" field by calling the explode() as shown below:

 SELECT  
   get_json_object(person, '$.name') AS name,
   get_json_object(person, '$.phone') AS phone,
   get_json_object(person, '$.address') AS addr
 FROM people lateral view explode(split(regexp_replace(
      regexp_replace(info, '\\}\\,\\{', '\\}\\\\n\\{' ), '\\[|\\]',''), '\\\\n')) 
      p as person;

My question is how I get each field within the "address" field. The "address" field can contain any number of key-value pairs and I can't use JSONSerDe. I am thinking to use another explode() call, but I can't get it to work. Can someone please help. Many Thanks.

Upvotes: 0

Views: 1448

Answers (1)

arau
arau

Reputation: 11

You can call the json_objects directly with

SELECT  
  get_json_object(person, '$.name') AS name,
  get_json_object(person, '$.phone') AS phone,
  get_json_object(person, '$.address.street') AS street,
  get_json_object(person, '$.address.city') AS city,
  get_json_object(person, '$.address.county') AS county,      
FROM people lateral view explode(split(regexp_replace(
  regexp_replace(info, '\\}\\,\\{', '\\}\\\\n\\{' ), '\\[|\\]',''), '\\\\n')) 
  p as person;

Upvotes: 1

Related Questions