Hive filter array

Question

The hive Apache table has the following column definition:

myvars:array<struct<index:bigint,value:string>>

An example for the relevant data:

"myvars":[
  {"index":2,"value":"value1"}
  , {"index":1,"value":"value2"}
  , {"index":2,"value":"value3"}
]

How can this array be filtered for all elements where "index" == 2.

In JavaScript, I would do something like the following:

myvars.filter(function(d){return d.index==2;})

How can I achieve the same result with Apache Hive QL, preferably without side views?

Answer 1

In the hive you have a collection of functions Collection :

 Collection
    array_contains(Array<T> a, val)
    array<K.V> map_keys(Map<K.V> a)
    array<K.V> map_values(Map<K.V> a)
    size(Map<K.V>|Array<T> a)
    sort_array(Array<T> a)

in your request use

...
WHERE
array_contains(myvars,2)