这是一种方法。首先去掉 [" 和 ]",因为 city 列中的双引号不包含单个数组元素,而是包含所有元素,然后标记字符串并使用 strtok_to_array 将其作为真实数组返回,然后将数组元素展平为单独的行并将行(城市)横向连接回记录的其余部分。
with data as
(select 'A' as name, 'M' as gender, '["completed"]' as orders, '["Cochi,Hyderabad"]' as city
union all
select 'B' as name, 'M' as gender, '["completed"]' as orders, '["Cochi,Hyderabad,Delhi"]' as city
union all
select 'C' as name, 'F' as gender, '["cancelled"]' as orders, '["Mumbai,Pune"]' as city
union all
select 'D' as name, 'M' as gender, '["pending"]' as orders, '["cochi"]' as city
)
, data2 as
( select d.name
, d.gender
, replace(replace(d.orders,'["',''),'"]','') as orders
, strtok_to_array(replace(replace(city,'["',''),'"]',''),',') as city
from data d
)
select d2.name
, d2.gender
, d2.orders
, replace(c.value,'"','') as city
from data2 d2
, lateral flatten(input => d2.city) c;