Hive解析多层嵌套json数组

原创

万小万小花 2021-07-09 16:41:52 博主文章分类：Hive ©著作权

©著作权归作者所有：来自51CTO博客作者万小万小花的原创作品，谢绝转载，否则将追究法律责任

1. 解析json数组

{"_id":{"$oid":"5e2038189a58551696a8d1b7"},"userId":{"$numberLong":"2100893"},"courseProgresses":[{"courseId":{"$numberLong":"2147"},"lessonProgresses":[{"lessonId":{"$numberLong":"300004"},"stepProgresses":[{"stepId":{"$numberLong":"7"},"isTrial":true,"finished":true,"interactProgresses":[{"interactId":{"$numberLong":"17"},"questionProgresses":[{"questionId":{"$numberLong":"11"},"isAnswer":true}]}]},{"stepId":{"$numberLong":"8"},"isTrial":true,"finished":true},{"stepId":{"$numberLong":"9"},"isTrial":true,"finished":true,"interactProgresses":[{"interactId":{"$numberLong":"20"},"questionProgresses":[{"questionId":{"$numberLong":"16"},"isAnswer":true},{"questionId":{"$numberLong":"18"},"isAnswer":true}]}]},{"stepId":{"$numberLong":"2100893"},"interactProgresses":[{"interactId":{"$numberLong":"20"},"questionProgresses":[{"questionId":{"$numberLong":"15"},"isAnswer":true},{"questionId":{"$numberLong":"17"},"isAnswer":true},{"questionId":{"$numberLong":"16"},"isAnswer":true},{"questionId":{"$numberLong":"18"},"isAnswer":true}]},{"interactId":{"$numberLong":"19"},"questionProgresses":[{"questionId":{"$numberLong":"14"},"isAnswer":true},{"questionId":{"$numberLong":"13"},"isAnswer":true}]},{"interactId":{"$numberLong":"18"},"questionProgresses":[{"questionId":{"$numberLong":"12"},"isAnswer":true}]},{"interactId":{"$numberLong":"17"},"questionProgresses":[{"questionId":{"$numberLong":"11"},"isAnswer":true}]}]}]}]},{"courseId":{"$numberLong":"2439"},"lessonProgresses":[{"lessonId":{"$numberLong":"300243"},"stepProgresses":[{"stepId":{"$numberLong":"579"},"time":299,"finished":true,"finishedTime":{"$date":"2020-09-09T07:05:25.449Z"},"interactProgresses":[{"interactId":{"$numberLong":"1122"},"questionProgresses":[{"questionId":{"$numberLong":"4790"},"isAnswer":true}]},{"interactId":{"$numberLong":"1123"},"questionProgresses":[{"questionId":{"$numberLong":"4791"},"isAnswer":true}]}]},{"stepId":{"$numberLong":"580"},"time":62,"finished":true,"finishedTime":{"$date":"2020-09-09T07:08:36.251Z"},"interactProgresses":[{"interactId":{"$numberLong":"1124"},"questionProgresses":[{"questionId":{"$numberLong":"4817"},"isAnswer":true}]}]},{"stepId":{"$numberLong":"581"},"time":21,"finished":true,"finishedTime":{"$date":"2020-09-09T07:09:44.503Z"},"interactProgresses":[{"interactId":{"$numberLong":"1125"},"questionProgresses":[{"questionId":{"$numberLong":"4834"},"isAnswer":true},{"questionId":{"$numberLong":"4840"},"isAnswer":true}]}]}]}]},{"courseId":{"$numberLong":"2146"},"lessonProgresses":[{"lessonId":{"$numberLong":"300003"},"stepProgresses":[{"stepId":{"$numberLong":"4"},"isTrial":false,"time":167,"finished":true,"finishedTime":{"$date":"2020-11-22T04:40:08.131Z"},"interactProgresses":[{"interactId":{"$numberLong":"26"},"questionProgresses":[{"questionId":{"$numberLong":"29"},"isAnswer":true}]},{"interactId":{"$numberLong":"27"},"questionProgresses":[{"questionId":{"$numberLong":"30"},"isAnswer":true}]}]}]}]}],"createdAt":{"$date":"2020-01-16T10:16:56.588Z"},"updatedAt":{"$date":"2020-11-22T04:42:14.717Z"}}

2. 存储多层数组嵌套

第一步：先用get_json_object 把需要的元素解析出来

第二步：再用json_array解析数组，同时用lateral view explode炸裂出多行数据

第三步：最后用json_tuple，解析多条json数据

with temp1 as (
   select
        translate(t1.doc, '"$', '"') as doc
   from
   (
   select
      '{"_id":{"$oid":"5e2038189a58551696a8d1b7"},"userId":{"$numberLong":"2100893"},"courseProgresses":[{"courseId":{"$numberLong":"2147"},"lessonProgresses":[{"lessonId":{"$numberLong":"300004"},"stepProgresses":[{"stepId":{"$numberLong":"7"},"isTrial":true,"finished":true,"interactProgresses":[{"interactId":{"$numberLong":"17"},"questionProgresses":[{"questionId":{"$numberLong":"11"},"isAnswer":true}]}]},{"stepId":{"$numberLong":"8"},"isTrial":true,"finished":true},{"stepId":{"$numberLong":"9"},"isTrial":true,"finished":true,"interactProgresses":[{"interactId":{"$numberLong":"20"},"questionProgresses":[{"questionId":{"$numberLong":"16"},"isAnswer":true},{"questionId":{"$numberLong":"18"},"isAnswer":true}]}]},{"stepId":{"$numberLong":"2100893"},"interactProgresses":[{"interactId":{"$numberLong":"20"},"questionProgresses":[{"questionId":{"$numberLong":"15"},"isAnswer":true},{"questionId":{"$numberLong":"17"},"isAnswer":true},{"questionId":{"$numberLong":"16"},"isAnswer":true},{"questionId":{"$numberLong":"18"},"isAnswer":true}]},{"interactId":{"$numberLong":"19"},"questionProgresses":[{"questionId":{"$numberLong":"14"},"isAnswer":true},{"questionId":{"$numberLong":"13"},"isAnswer":true}]},{"interactId":{"$numberLong":"18"},"questionProgresses":[{"questionId":{"$numberLong":"12"},"isAnswer":true}]},{"interactId":{"$numberLong":"17"},"questionProgresses":[{"questionId":{"$numberLong":"11"},"isAnswer":true}]}]}]}]},{"courseId":{"$numberLong":"2439"},"lessonProgresses":[{"lessonId":{"$numberLong":"300243"},"stepProgresses":[{"stepId":{"$numberLong":"579"},"time":299,"finished":true,"finishedTime":{"$date":"2020-09-09T07:05:25.449Z"},"interactProgresses":[{"interactId":{"$numberLong":"1122"},"questionProgresses":[{"questionId":{"$numberLong":"4790"},"isAnswer":true}]},{"interactId":{"$numberLong":"1123"},"questionProgresses":[{"questionId":{"$numberLong":"4791"},"isAnswer":true}]}]},{"stepId":{"$numberLong":"580"},"time":62,"finished":true,"finishedTime":{"$date":"2020-09-09T07:08:36.251Z"},"interactProgresses":[{"interactId":{"$numberLong":"1124"},"questionProgresses":[{"questionId":{"$numberLong":"4817"},"isAnswer":true}]}]},{"stepId":{"$numberLong":"581"},"time":21,"finished":true,"finishedTime":{"$date":"2020-09-09T07:09:44.503Z"},"interactProgresses":[{"interactId":{"$numberLong":"1125"},"questionProgresses":[{"questionId":{"$numberLong":"4834"},"isAnswer":true},{"questionId":{"$numberLong":"4840"},"isAnswer":true}]}]}]}]},{"courseId":{"$numberLong":"2146"},"lessonProgresses":[{"lessonId":{"$numberLong":"300003"},"stepProgresses":[{"stepId":{"$numberLong":"4"},"isTrial":false,"time":167,"finished":true,"finishedTime":{"$date":"2020-11-22T04:40:08.131Z"},"interactProgresses":[{"interactId":{"$numberLong":"26"},"questionProgresses":[{"questionId":{"$numberLong":"29"},"isAnswer":true}]},{"interactId":{"$numberLong":"27"},"questionProgresses":[{"questionId":{"$numberLong":"30"},"isAnswer":true}]}]}]}]}],"createdAt":{"$date":"2020-01-16T10:16:56.588Z"},"updatedAt":{"$date":"2020-11-22T04:42:14.717Z"}}' as doc
   ) t1
),
temp2 as (
    select
        a1.id1,
        get_json_object(a1.id1, '$.oid') as  id,
        cast(get_json_object(a1.userid, '$.numberLong') as int) as  user_id,
        a2.courseProgressesIds,
        cast(get_json_object(a3.courseId, '$.numberLong') as int) as course_id,
        cast(get_json_object(a5.lessonId, '$.numberLong') as int) as lesson_id,
        cast(get_json_object(a7.stepId, '$.numberLong') as int) as step_id,
        cast(a7.isTrial as boolean) as istrial,
        cast(a7.finished as boolean) as finished,
        get_json_object(a7.finishedTime, '$.numberLong') as finishedTime,
        a7.interactProgresses as interactProgresses
    from temp1
    lateral view json_tuple(doc, '_id','userId', 'courseProgresses') a1 as id1, userid, courseProgresses
    lateral view explode(json_array(a1.courseProgresses)) a2 as courseProgressesIds
    lateral view json_tuple(a2.courseProgressesIds, 'courseId', 'lessonProgresses') a3 as courseId, lessonProgresses
    lateral view explode(json_array(a3.lessonProgresses)) a4 as lessonProgresseIds
   -- lateral view explode(split(regexp_replace(a3.lessonProgresses, '\\[|\\]', ''), ',')) a4 as lessonProgresseIds
    lateral view json_tuple(a4.lessonProgresseIds, 'lessonId', 'stepProgresses') a5 as lessonId, stepProgresses
   -- lateral view explode(split(regexp_replace(a5.stepProgresses, '\\[|\\]', ''), ',')) a6 as stepProgressesIds
    lateral view explode(json_array(a5.stepProgresses)) a6 as stepProgressesIds
    lateral view json_tuple(a6.stepProgressesIds, 'stepId', 'isTrial', 'finished', 'finishedTime', 'interactProgresses') a7 as stepId, isTrial, finished,finishedTime,interactProgresses
)
select * from temp2;

案例二

with temp_data as (
    select '{"appid": "xxxx", "client_ip": "192.168.1.134", "data_object": {"data": [{"#account_id": "aaa", "#event_name": "login", "#type": "track", "#time": "2018-06-27 18:16:38", "#ip": "192.168.1.134", "properties": {"device_id": "9894b1ad33dd7ff77a941d5863a51e05", "app_version": "", "sdk_version": "1.0", "os": "Android", "os_version": "5.1", "device_model": "8681-A01", "manufacturer": "QiKU", "duration": 0, "ts_cid": "100019", "channel": "xiaomi", "ts_sid": 10001 } }, {"#account_id": "bbb", "#event_name": "click-buy", "#type": "track", "#time": "2018-06-27 18:16:38", "#ip": "192.168.1.134", "properties": {"device_id": "9894b1ad33dd7ff77a941d5863a51e05", "app_version": "", "sdk_version": "1.0", "os": "Android", "os_version": "5.1", "device_model": "8681-A01", "manufacturer": "QiKU", "duration": 0, "ts_cid": "100019", "channel": "xiaomi", "ts_sid": 10001 } } ] }, "receive_time": "2018-06-28 17:25:20"}' as doc
)
select
    a1.appid,
    a1.client_ip,
    a1.data_object,
    a1.receive_time,
    a3.data
from temp_data
lateral view json_tuple(doc, 'appid', 'client_ip', 'data_object', 'receive_time') a1 as appid, client_ip, data_object,receive_time
lateral view json_tuple(a1.data_object, 'data') a2 as data
lateral view explode(json_array(a2.data)) a3 as data
;