复杂格式数据处理

SELECT
    code,
    name,
    ai.ACode,
    ai.AName,
    bi.BCode,
    bi.BName
FROM (SELECT
            get_json_object(t.value, '$.base.code') AS code,
            get_json_object(t.value, '$.base.name') AS name,
            get_json_object(t.value, '$.list') AS list
        FROM
            (
                SELECT
                    '{"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
            ) t 
    ) t1 
    lateral VIEW explode(split(regexp_replace(regexp_extract(list, '^\\[(.+)\\]$', 1), '\\}\\]\\}\\,\\{', '\\}\\]\\}\\|\\|\\{'), '\\|\\|')) list AS a lateral VIEW json_tuple(a, 'ACode', 'AName', 'BList') ai AS ACode,
    AName,
    BList lateral VIEW explode(split(regexp_replace(regexp_extract(BList, '^\\[(.+)\\]$', 1), '\\}\\,\\{', '\\}\\|\\|\\{'), '\\|\\|')) BList AS b lateral VIEW json_tuple(b, 'BCode', 'BName') bi AS BCode,
    BName ;