分解数组并获取结构元素，使用结构元素构建所需的字符串并收集字符串数组，使用concat\ws将其转换为字符串，然后与其他列连接。这样地：

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 
;

退货：

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)

使用inline可以分解结构元素

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

;

并在字符串、collect array、concat\u ws等中按需要再次连接它们