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