1 SELECT
 2     code,
 3     name,
 4     ai.ACode,
 5     ai.AName,
 6     bi.BCode,
 7     bi.BName
 8 FROM (SELECT
 9             get_json_object(t.value, '$.base.code') AS code,
10             get_json_object(t.value, '$.base.name') AS name,
11             get_json_object(t.value, '$.list') AS list
12         FROM
13             (
14                 SELECT
15                     '{"base":{"code":"xm","name":"project"},"list":[{"ACode":"cp1","AName":"Product1","BList":[{"BCode":"gn1","BName":"Feature1"},{"BCode":"gn2","BName":"Feature2"}]},{"ACode":"cp2","AName":"Product2","BList":[{"BCode":"gn1","BName":"Feature1"}]}]}' AS value
16             ) t 
17     ) t1 
18     lateral VIEW explode(split(regexp_replace(regexp_extract(list, '^\\[(.+)\\]$', 1), '\\}\\]\\}\\,\\{', '\\}\\]\\}\\|\\|\\{'), '\\|\\|')) list AS a lateral VIEW json_tuple(a, 'ACode', 'AName', 'BList') ai AS ACode,
19     AName,
20     BList lateral VIEW explode(split(regexp_replace(regexp_extract(BList, '^\\[(.+)\\]$', 1), '\\}\\,\\{', '\\}\\|\\|\\{'), '\\|\\|')) BList AS b lateral VIEW json_tuple(b, 'BCode', 'BName') bi AS BCode,
21     BName ;