记录使用炸裂函数SQL数据a公司 [{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject:流动资产},{balanceSheetType:1,closingBalance:1706816.620000,lastYearEndBalance:268.140000,reportProject: 货币资金},{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject: 以公允价值计量且其变动计入当期损益的金融资产},{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject: 衍生金融资产},{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject: 应收票据}] b公司 [{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject:流动资产},{balanceSheetType:1,closingBalance:170.620000,lastYearEndBalance:2.140000,reportProject: 货币资金},{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject: 以公允价值计量且其变动计入当期损益的金融资产},{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject: 衍生金融资产},{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject: 应收票据}]、SQL maxcomputeWITH test_table AS ( SELECT a公司 AS id, [{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject:流动资产},{balanceSheetType:1,closingBalance:1706816.620000,lastYearEndBalance:268.140000,reportProject: 货币资金},{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject: 以公允价值计量且其变动计入当期损益的金融资产},{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject: 衍生金融资产},{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject: 应收票据}] AS json_str UNION ALL SELECT b公司 AS id, [{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject:流动资产},{balanceSheetType:1,closingBalance:170.620000,lastYearEndBalance:2.140000,reportProject: 货币资金},{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject: 以公允价值计量且其变动计入当期损益的金融资产},{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject: 衍生金融资产},{balanceSheetType:1,closingBalance:0,lastYearEndBalance:0,reportProject: 应收票据}] AS json_str UNION ALL SELECT C公司 as id, AS json_str ) SELECT id, get_json_object(json_item, $.balanceSheetType) AS balanceSheetType, get_json_object(json_item, $.reportProject) AS reportProject, get_json_object(json_item, $.closingBalance) AS closingBalance, get_json_object(json_item, $.lastYearEndBalance) AS lastYearEndBalance FROM test_table LATERAL VIEW OUTER explode(from_json(json_str, arraystring)) tmp AS json_item;执行结果idbalanceSheetTypereportProjectclosingBalancelastYearEndBalancea公司1流动资产00a公司1货币资金1706816.62268.14a公司1以公允价值计量且其变动计入当期损益的金融资产00a公司1衍生金融资产00a公司1应收票据00b公司1流动资产00b公司1货币资金170.622.14b公司1以公允价值计量且其变动计入当期损益的金融资产00b公司1衍生金融资产00b公司1应收票据00C公司\N\N\N\NLATERAL VIEW OUTER和LATERAL VIEW的区别LATERAL VIEW内连接型如果explode的数组是空数组 /null→原行会被直接删掉丢数据。LATERAL VIEW OUTER左连接型即使数组空 /null→原行保留炸裂出来的字段全部填 null。使用LATERAL VIEW测试结果idbalanceSheetTypereportProjectclosingBalancelastYearEndBalancea公司1流动资产00a公司1货币资金1706816.62268.14a公司1以公允价值计量且其变动计入当期损益的金融资产00a公司1衍生金融资产00a公司1应收票据00b公司1流动资产00b公司1货币资金170.622.14b公司1以公允价值计量且其变动计入当期损益的金融资产00b公司1衍生金融资产00b公司1应收票据00