select

     name

    ,REGEXP_REPLACE(str,"[\\[\"\\]]",'')

from (

    select

        trans_array(1, ",", name,list) as (name,str)

    from (

    select '经办人' as name,'["1001","1002"]' as list

    ) t1

) t2

;

select

    trans_array(1, ",", name,REGEXP_REPLACE(list,"[\\[\"\\]]",'')) as (name,str)

from (

select '经办人' as name,'["1001","1002"]' as list

) t1

;

select

     name

    ,list_tmp

from (

    select '经办人' as name,'["1001","1002"]' as list

) t1

lateral view explode(split(REGEXP_REPLACE(list,"[\\[\"\\]]",''),',')) b AS list_tmp

;