I have a couple of string columns and an array column. My requirement is to convert the array as a string and concatenate with the other string columns to execute MD5 function over the concatenated string column
But Casting array to String is not possible and I tried to use explode and inline function as well in order to extract the array contents but of no luck so far
Any idea on how to achieve this
Explode the array and get the struct elements, build string you need using struct elements and collect array of strings, use concat_ws to convert it to the string and then concatenate with some other column. Like this:
with mydata as (
select ID, my_array
from
( --some array<struct> example
select 1 ID, array(named_struct("city","Hudson","state","NY"),named_struct("city","San Jose","state","CA"),named_struct("city","Albany","state","NY")) as my_array
union all
select 2 ID, array(named_struct("city","San Jose","state","CA"),named_struct("city","San Diego","state","CA")) as my_array
)s
)
select ID, concat(ID,'-', --'-' is a delimiter
concat_ws(',',collect_list(element)) --collect array of strings and concatenate it using ',' delimiter
) as my_string --concatenate with ID column also
from
(
select s.ID, concat_ws(':',a.mystruct.city, mystruct.state) as element --concatenate struct using : as a delimiter Or concatenate in some other way
from mydata s
lateral view explode(s.my_array) a as mystruct
)s
group by ID
;
Returns:
OK
1 1-Hudson:NY,San Jose:CA,Albany:NY
2 2-San Jose:CA,San Diego:CA
Time taken: 63.368 seconds, Fetched: 2 row(s)
Using INLINE you can get struct elements exploded
with mydata as (
select ID, my_array
from
( --some array<struct> example
select 1 ID, array(named_struct("city","Hudson","state","NY"),named_struct("city","San Jose","state","CA"),named_struct("city","Albany","state","NY")) as my_array
union all
select 2 ID, array(named_struct("city","San Jose","state","CA"),named_struct("city","San Diego","state","CA")) as my_array
)s
)
select s.ID, a.city, a.state
from mydata s
lateral view inline(s.my_array) a as city, state
;
And concatenate them as you want again in the string, collect array, concat_ws, etc