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; 如何将pkgname和vercode拆解到多行？

您可以使用LATERAL VIEW EXPLODE函数将pkgname和vercode拆分成多行。以下是修改后的查询语句：

SELECT mac, pkgname, vercode
FROM (
    SELECT DISTINCT upper(distinct_id) AS 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
) AS t2
LATERAL VIEW EXPLODE(pkgname) p AS pkgname
LATERAL VIEW EXPLODE(vercode) v AS vercode;

这样，pkgname和vercode将被拆解为多行，每个mac对应一行pkgname和vercode的组合。