JSONArray转多行JSON

先通过explode把原数据变成n行数据，然后再用get_json_object去解析

select ss.json_col
from (
       select split(regexp_replace(regexp_extract('需要解析的jsonArray', '^\\[(.+)\\]$',1)
              ,'\\}\\,\\{', '\\}\\|\\|\\{'), '\\|\\|') as json_str
       from json_array_table
      ) pp
lateral view explode(pp.json_str) ss as json_col;

 实例:

select ss.json_col
from (
       select split(regexp_replace(regexp_extract('[{"time":1582487998465,"_track_id":1358410713,"event":"joyLinkClick","_flush_time":1582488009148,"distinct_id":"6342133","properties":{"$os_version":"12.4.5","$device_id":"FCF68CFD-1F1A-4C2C-BA67-AD556C4EB125","app_name":"查悦社保","$carrier":"中国移动","$os":"iOS","$screen_height":640,"$is_first_day":false,"$screen_width":360,"$model":"iPhone7,1","$lib":"iOS","$network_type":"WIFI","$app_version":"3.4.3","$manufacturer":"Apple","$wifi":true,"jtrack_page":"服务","jtrack_name":"服务","$lib_version":"1.10.21"},"type":"track","lib":{"$lib_version":"1.10.21","$lib":"iOS","$app_version":"3.4.3","$lib_method":"code"}},{"time":1582487998507,"_track_id":957323951,"event":"joyLinkClick","_flush_time":1582488009149,"distinct_id":"6342133","properties":{"$os_version":"12.4.5","$device_id":"FCF68CFD-1F1A-4C2C-BA67-AD556C4EB125","app_name":"查悦社保","$carrier":"中国移动","$os":"iOS","$screen_height":640,"$is_first_day":false,"$screen_width":360,"$model":"iPhone7,1","$lib":"iOS","$network_type":"WIFI","$app_version":"3.4.3","$manufacturer":"Apple","$wifi":true,"jtrack_page":"社保","jtrack_name":"社保","$lib_version":"1.10.21"},"type":"track","lib":{"$lib_version":"1.10.21","$lib":"iOS","$app_version":"3.4.3","$lib_method":"code"}},{"time":1582487998572,"_track_id":535356242,"event":"$AppClick","_flush_time":1582488009151,"distinct_id":"6342133","properties":{"$os_version":"12.4.5","$element_content":"服务","$device_id":"FCF68CFD-1F1A-4C2C-BA67-AD556C4EB125","app_name":"查悦社保","$carrier":"中国移动","$os":"iOS","$screen_height":640,"$is_first_day":false,"$screen_width":360,"$model":"iPhone7,1","$lib":"iOS","$network_type":"WIFI","$app_version":"3.4.3","$manufacturer":"Apple","$wifi":true,"$screen_name":"JOYServiceController","$element_type":"UIControl","$lib_version":"1.10.21"},"type":"track","lib":{"$lib_detail":"JOYServiceController######","$lib_version":"1.10.21","$lib":"iOS","$app_version":"3.4.3","$lib_method":"code"}},{"time":1582487998616,"_track_id":2696301578,"event":"$AppViewScreen","_flush_time":1582488009152,"distinct_id":"6342133","properties":{"$os_version":"12.4.5","$device_id":"FCF68CFD-1F1A-4C2C-BA67-AD556C4EB125","app_name":"查悦社保","$carrier":"中国移动","$os":"iOS","$screen_height":640,"$is_first_day":false,"$screen_width":360,"$model":"iPhone7,1","$lib":"iOS","$network_type":"WIFI","$app_version":"3.4.3","$manufacturer":"Apple","$wifi":true,"$screen_name":"JOYServiceController","$lib_version":"1.10.21"},"type":"track","lib":{"$lib_detail":"JOYServiceController######","$lib_version":"1.10.21","$lib":"iOS","$app_version":"3.4.3","$lib_method":"code"}},{"time":1582488001012,"_track_id":149863720,"event":"joyLinkClick","_flush_time":1582488009153,"distinct_id":"6342133","properties":{"$os_version":"12.4.5","$device_id":"FCF68CFD-1F1A-4C2C-BA67-AD556C4EB125","app_name":"查悦社保","$carrier":"中国移动","$os":"iOS","$screen_height":640,"$is_first_day":false,"$screen_width":360,"$model":"iPhone7,1","$lib":"iOS","$network_type":"WIFI","$app_version":"3.4.3","$manufacturer":"Apple","$wifi":true,"jtrack_page":"课程","jtrack_name":"课程","$lib_version":"1.10.21"},"type":"track","lib":{"$lib_version":"1.10.21","$lib":"iOS","$app_version":"3.4.3","$lib_method":"code"}},{"time":1582488001061,"_track_id":563548821,"event":"$AppClick","_flush_time":1582488009154,"distinct_id":"6342133","properties":{"$os_version":"12.4.5","$element_content":"课程","$device_id":"FCF68CFD-1F1A-4C2C-BA67-AD556C4EB125","app_name":"查悦社保","$carrier":"中国移动","$os":"iOS","$screen_height":640,"$is_first_day":false,"$screen_width":360,"$model":"iPhone7,1","$lib":"iOS","$network_type":"WIFI","$app_version":"3.4.3","$manufacturer":"Apple","$wifi":true,"$screen_name":"JOYServiceController","$element_type":"UIControl","$lib_version":"1.10.21"},"type":"track","lib":{"$lib_detail":"JOYServiceController######","$lib_version":"1.10.21","$lib":"iOS","$app_version":"3.4.3","$lib_method":"code"}},{"time":1582488001609,"_track_id":977721946,"event":"$AppViewScreen","_flush_time":1582488009155,"distinct_id":"6342133","properties":{"$os_version":"12.4.5","$device_id":"FCF68CFD-1F1A-4C2C-BA67-AD556C4EB125","app_name":"查悦社保","$carrier":"中国移动","$os":"iOS","$screen_height":640,"$is_first_day":false,"$screen_width":360,"$model":"iPhone7,1","$lib":"iOS","$network_type":"WIFI","$app_version":"3.4.3","$manufacturer":"Apple","$wifi":true,"$screen_name":"WebViewController","$lib_version":"1.10.21"},"type":"track","lib":{"$lib_detail":"WebViewController######","$lib_version":"1.10.21","$lib":"iOS","$app_version":"3.4.3","$lib_method":"code"}},{"time":1582488003641,"_track_id":984325331,"event":"$AppViewScreen","_flush_time":1582488009155,"distinct_id":"6342133","properties":{"$os_version":"12.4.5","$device_id":"FCF68CFD-1F1A-4C2C-BA67-AD556C4EB125","app_name":"查悦社保","$carrier":"中国移动","$os":"iOS","$screen_height":640,"$is_first_day":false,"$screen_width":360,"$model":"iPhone7,1","$lib":"iOS","$network_type":"WIFI","$app_version":"3.4.3","$manufacturer":"Apple","$wifi":true,"$screen_name":"JOYServiceController","$lib_version":"1.10.21"},"type":"track","lib":{"$lib_detail":"JOYServiceController######","$lib_version":"1.10.21","$lib":"iOS","$app_version":"3.4.3","$lib_method":"code"}},{"time":1582488005011,"_track_id":4176680657,"event":"$AppClick","_flush_time":1582488009156,"distinct_id":"6342133","properties":{"$os_version":"12.4.5","$element_content":"社保","$device_id":"FCF68CFD-1F1A-4C2C-BA67-AD556C4EB125","app_name":"查悦社保","$carrier":"中国移动","$os":"iOS","$screen_height":640,"$is_first_day":false,"$screen_width":360,"$model":"iPhone7,1","$lib":"iOS","$network_type":"WIFI","$app_version":"3.4.3","$manufacturer":"Apple","$wifi":true,"$screen_name":"JOYServiceController","$element_type":"UIButton","$lib_version":"1.10.21"},"type":"track","lib":{"$lib_detail":"JOYServiceController######","$lib_version":"1.10.21","$lib":"iOS","$app_version":"3.4.3","$lib_method":"code"}},{"time":1582488005049,"_track_id":1880014458,"event":"$AppViewScreen","_flush_time":1582488009157,"distinct_id":"6342133","properties":{"$os_version":"12.4.5","$device_id":"FCF68CFD-1F1A-4C2C-BA67-AD556C4EB125","app_name":"查悦社保","$carrier":"中国移动","$os":"iOS","$screen_height":640,"$is_first_day":false,"$screen_width":360,"$model":"iPhone7,1","$lib":"iOS","$network_type":"WIFI","$app_version":"3.4.3","$manufacturer":"Apple","$wifi":true,"$screen_name":"JOYHomeController","$title":"","$lib_version":"1.10.21"},"type":"track","lib":{"$lib_detail":"JOYHomeController######","$lib_version":"1.10.21","$lib":"iOS","$app_version":"3.4.3","$lib_method":"code"}},{"time":1582488005131,"_track_id":224480708,"event":"joyLinkClick","_flush_time":1582488009158,"distinct_id":"6342133","properties":{"$os_version":"12.4.5","$device_id":"FCF68CFD-1F1A-4C2C-BA67-AD556C4EB125","app_name":"查悦社保","$carrier":"中国移动","$os":"iOS","$screen_height":640,"$is_first_day":false,"$screen_width":360,"$model":"iPhone7,1","$lib":"iOS","$network_type":"WIFI","$app_version":"3.4.3","$manufacturer":"Apple","$wifi":true,"jtrack_page":"社保","jtrack_name":"社保","$lib_version":"1.10.21"},"type":"track","lib":{"$lib_version":"1.10.21","$lib":"iOS","$app_version":"3.4.3","$lib_method":"code"}}]', '^\\[(.+)\\]$',1)
              ,'\\}\\,\\{', '\\}\\|\\|\\{'), '\\|\\|') as json_str
     --  from json_array_table -- 验证是否正确时,把这行注释了就可以, 代码中用的时候取消注释
      ) pp
lateral view explode(pp.json_str) ss as json_col;