Search code examples
sqlarrayshivehiveqlhue

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

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?

Solution

  • 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]