Filter Array in Hive

Question

An Apache hive table has the following column definition:

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

An example for the corresponding data is:

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

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

In JavaScript I would do something like the following:

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

How can the same result be achieved with Apache Hive QL, preferably without lateral views?

Duplicate of: http://stackoverflow.com/questions/26774332/hive-check-elements-in-array — kecso, Jan 05 '17 at 20:06
i have this question too can you find any solution for it? let me know please https://stackoverflow.com/questions/47280628/how-to-filter-on-map-value-in-hive-arraymapstring-string — reihaneh, Nov 18 '17 at 05:33
First link is not a duplicate, since this is a nested struct, not a flat array. Second link's page seems to be broken... — Marcus, May 13 '21 at 00:48

score 2 · Answer 1 · answered Aug 18 '17 at 13:03

2

In hive you have a set of Collection functions:

 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 query use

...
WHERE
array_contains(myvars,2)

answered Aug 18 '17 at 13:03

Alg_D

2,242
6
31
63

Worked for me! Thanks! – akki Jun 04 '18 at 09:49

score 0 · Answer 2 · answered May 13 '21 at 00:52

I think if you are trying to extract all the values where index is 2, you want something like this:

SELECT DISTINCT value
FROM mytable 
LATERAL VIEW EXPLODE(myvars) exploded_myvars AS idx, value
WHERE idx = 2;

If instead the data type was array<map<string,string>> it would be

SELECT DISTINCT mv["value"]
FROM mytable 
LATERAL VIEW EXPLODE(myvars) exploded_myvars AS mv
WHERE mv["index"] = 2;

Filter Array in Hive

2 Answers2