How to reverse an elements in the array using Spark SQL query?-CodePudding

I have a data like below Input data Key data a [5,2,6,null,null] b [5,7,9,4,null,null,null]

I want output to be like below. Output: Key data a [6,2,5,null,null] b [4,9,7,5,null,null,null]

Basically elements in the array needs to be reversed by keeping nulls at the end as it is. Can someone please help me with spark SQL query?

CodePudding user response：

Untested:

reverse(filter(array(0, null, 2, 3, null), x -> x IS NOT NULL))

then append:

filter(array(0, null, 2, 3, null), x -> x IS NULL)

CodePudding user response：

My approach - transform NULLs for sorting then transform back to NULL

select transform(sort_array(transform(data, x -> coalesce(x, 0)), False), x -> case when x=0 then null else x end) from table1

[EDIT]

Just noticed the transformations are not required if the NULLs are to be sorted at the end based on reverse order. sort_array() will work by itself

sort_array(data, False)