How to remove the elements with value as zero in hive array

user_15532119 0
Viewed 377

I have an array column in hive which will be having 7 numbers.

For Ex: [32,4,0,43,23,0,1]

I want my output to be [32,4,43,23,1] (with all the zero elements removed)

Someone help me to accomplish this?

hue hiveql hive arrays sql
1 Answers

Explode array, filter, collect again.

Demo:

with mydata as (
select array(32,4,0,43,23,0,1) as initial_array
)

select initial_array, collect_set(element) as result_array
from
(
select initial_array, e.element
  from mydata
       lateral view outer explode(initial_array)e as element
) s
where element != 0
group by initial_array

Result:

initial_array       result_array
[32,4,0,43,23,0,1]  [32,4,43,23,1]
user_27003440
Related