title: HIVE, How to get an element from an array, the element itself is an array too
body: <p>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.   </p>
  
  <pre><code>People table:
    Col1      Col2   .....   info
    aaa       bbb           see below
  </code></pre>
  
  <p>For the column "info", it contains the following JSON format string:</p>
  
  <pre><code> [{"name":"abc", 
    "address":{"street":"str1", "city":"c1"},
    "phone":"1234567"
   },
   {"name":"def", 
    "address":{"street":"str2", "city":"c1", "county":"ct"},
    "phone":"7145895"
   }
  ]
  </code></pre>
  
  <p>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:</p>
  
  <pre><code> 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;
  </code></pre>
  
  <p>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.</p>