presto解析jsonArr转多行
一、假数据解析
SELECT r1.col.dataSourceId, r1.col.database, r1.col.dataTable FROM (SELECT explode(r.json) AS col FROM (
SELECT from_json('[{"dataSourceId":4,"database":"db_statistics","dataTable":"compass_sdk_page"}]','array<struct<dataSourceId:string,database:string,dataTable:string>>') AS json
) r) AS r1;
二、查表解析
select task_id,out_tab.task_config.dataSourceId, out_tab.task_config.database, out_tab.task_config.dataTable from(
select
task_id,
explode(from_json(temp_col,'array<struct<dataSourceId:string,database:string,dataTable:string>>')) as task_config
from (
select
task_id,
get_json_object(config_content,'$.sourceTableConfig.multipleSourceList') as temp_col
from iceberg_catalog.data_lake_ods.ods_bdg_test where task_id=30007
)inn_tab
)out_tab