SELECT mac,
get_json_object(appListInfo, '$[*].pkgname') AS pkgname,
get_json_object(appListInfo, '$[*].vercode') AS vercode
FROM(
SELECT DISTINCT upper(distinct_id) AS mac,
lower(properties['appListInfo']) AS appListInfo
FROM default.ods_slog
WHERE DAY = '20230720'
AND event = 'appListInfo'
AND distinct_id NOT LIKE '00%'
AND distinct_id NOT IN ('',' ','null','NULL')
AND upper(distinct_id) NOT IN ('FFFFFFFFFFFF','HHHHHHHHHHHH')
) AS t;
其中vercode示例:[402005,70600000,20100003,1012,10400011,1100088,40300004,501092115,10208,1030026,1302,2070004,10011,15,109037,3074,805000036,805000036,805000036,10000900,7900095],如何拆分多行?