title: How to query struct array with Hive (get_json_object) or json serde
body: <p>I am trying to query the following JSON example file stored on my HDFS</p>
  
  <pre><code>{
      "tag1": "1.0",
      "tag2": "blah",
      "tag3": "blahblah",
      "tag4": {
          "tag4_1": [{
                  "tag4_1_1": [{
                          "tag4_1_1_1": {
                              "Addr": {
                                  "Addr1": "blah",
                                  "City": "City",
                                  "StateProvCd": "NY",
                                  "PostalCode": "99999"
                              }
                          }
                          "tag4_1_1_1": {
                              "Addr": {
                                  "Addr1": "blah2",
                                  "City": "City2",
                                  "StateProvCd": "NY",
                                  "PostalCode": "99999"
                              }
                          }
                      }
                  ]
              }
          ]
      }
  }
  </code></pre>
  
  <p>I used the following to create an external table over the data</p>
  
  <pre><code>CREATE  EXTERNAL TABLE DB.hv_table
  (
    tag1 string
  , tag2 string
  , tag3 string
  , tag4 struct&lt;tag4_1:ARRAY&lt;struct&lt;tag4_1_1:ARRAY&lt;struct&lt;tag4_1_1_1:struct&lt;Addr
                  Addr1:string
                  , City:string
                  , StateProvCd:string
                  , PostalCode:string&gt;&gt;&gt;&gt;&gt;&gt;
  )
  ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' 
  LOCATION 'HDFS/location';
  </code></pre>
  
  <p>Ideally, I want to query the data such that it would return to me as such:</p>
  
  <pre><code>select tag1, tag2, tag3, tag4(all data) from DB.hv_table;
  </code></pre>
  
  <p>Can someone provide me an example of how I can query without writing it in the following manner:</p>
  
  <pre><code>select tag1, tag2, tag3
  , tag4.tag4_1[0].tag4_1_1[0].tag4_1_1_1.Addr.Addr1 as Addr1 
  , tag4.tag4_1[0].tag4_1_1[0].tag4_1_1_1.Addr.City as City 
  , tag4.tag4_1[0].tag4_1_1[0].tag4_1_1_1.Addr.StateProvCd as StateProvCd 
  , tag4.tag4_1[0].tag4_1_1[0].tag4_1_1_1.Addr.PostalCode as PostalCode 
  from DB.hv_table
  </code></pre>
  
  <p>Most importantly, I would like to not define the array item element number. In my example, I am only able to target the first element of my array (tag4_1_1_1). I would to target everything if possible. </p>