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 ;
