先看效果
再看SQL
select a.address,t.a_json
from(
select
get_json_object( tab.json_str , '$.address') address,
split(regexp_replace(regexp_extract ( get_json_object(tab.json_str , '$.store.fruit'), '(\\[)(.*?)(\\])',2), '\\},\\{','\\}|\\{'),'\\|') as arr
from (
select '{"store":{"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}]},"address":"北京"}' json_str
union all
select '{"store":{"fruit":[{"weight":33,"type":"apple"},{"weight":44,"type":"pear"}]},"address":"石家庄"}' json_str
)tab
) a
lateral view explode(arr) t as a_json;