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]，如何拆分多行？

如果要将vercode字段拆分成多行，您可以使用LATERAL VIEW EXPLODE函数。以下是修改后的查询语句：

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 vercodes
    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 OUTER EXPLODE(split(vercodes, ',')) t3 AS vercode;

这样，vercode将会按照逗号分隔符拆分成多行，并与mac和pkgname字段一起显示。请注意，vercodes是以逗号分隔的字符串列表。