Retrieve value in an array of an array with struct

Question

I have a column in Hive table with type:

array<array<struct<type:string,value:string,currency:string>>>

Here is the sample of data in the column:

[
  [
    {
       "type": "PROFIT",
       "value": "100",
       "currency": "USD"
    },
    {
       "type": "NET",
       "value": "50",
       "currency": "USD"
    },
    {
       "type": "TOTAL",
       "value": "250",
       "currency": "USD"
    }
  ]
]

How do I query each 'Type' to a column?

PROFIT	NET	TOTAL

Answer 1

Explode upper level array to get inner arrays of struct, use array index [] to access struct, use dot to access struct elements:

select a1.inner_array[0].type as type1,
       a1.inner_array[1].type as type2,
       a1.inner_array[2].type as type3
  from yourtable t
       --explode upper array
       lateral view outer explode(col_name) a1 as inner_array