查看代码中的注释。转换步骤不是有意组合的,以便于理解:
with your_table as ( --use your_table instead of this
select stack(4,'{"AR":["Fasdfadsfasd","Hasdfasdf"]}','{"DR":["Aasdfads","Baasdfasdf","Iasdfadsf","Zasdfasdf"]}','{"PR":["Easdfadsf","Gasdfadsfads"]}','{"SR":["Casdfasdf","Dasdfadsf","Jasdfasdfa"]}'
) as src
)
select max(AR) AR,max(DR) DR,max(PR) PR,max(SR) SR --group by position
from
(
select case when key='AR' then value end AR,--transpose
case when key='DR' then value end DR,case when key='PR' then value end PR,case when key='SR' then value end SR,pos
from
(
select s.key,regexp_replace(v.value,'(^\\")|(\\"$)','') value,v.pos --remove quotes from value
from
(
select regexp_replace(m[0],'') key,regexp_replace(m[1],'\\[|\\]','') value --remove [] from value and unquote key
from
(
select split(
regexp_replace(src,'\\{|\\}',''),--remove curly braces
'[:]') as m --split map key and value
from your_table t
)s
)s lateral view outer posexplode(split(value,'\\",\\"')) v as pos,value
)s
)s
group by pos;
结果:
ar dr pr sr
Fasdfadsfasd Aasdfads Easdfadsf Casdfasdf
Hasdfasdf Baasdfasdf Gasdfadsfads Dasdfadsf
NULL Iasdfadsf NULL Jasdfasdfa
NULL Zasdfasdf NULL NULL
本文链接:https://www.f2er.com/3113853.html