json 字符串为:
字段名为: json
{"appId":36222,"deviceId":"12536521-7b3d-41f6-9c09-fdb94b1098a6","md":"{\"os\":\"Android\",\"imei\":\"351834334\",\"nt\":\"wifi\",\"apps\":\"com.sec.android.gallery3d,com.vlingo.midas,com.sskj.flashlight,com.android.browser,com.tencent.qqpimsecure,com.android.mms,com.sec.android.app.voicenote,com.android.settings,com.qiyi.video,com.samsung.android.app.sreminder,com.ss.android.article.news,com.tencent.android.qqdownloader,com.samsung.memorymanager,com.android.stk,com.smile.gifmaker,com.sec.android.app.music,com.tencent.mm,com.android.contacts,com.duomi.android,com.samsung.everglades.video,com.best.browser,com.dn.tgxm.gg,com.sitech.ac,com.sec.android.app.sbrowser,com.android.qidian.mycalculator,com.android.qidian.calendar,com.android.email,com.cleanmaster.security_cn,com.snda.wifilocating,com.sec.android.app.camera,com.sec.android.app.fm,com.sec.android.app.myfiles,com.sec.android.mimage.sstudio,com.happyelements.AndroidAnimal.qq,com.sec.android.app.clockpackage,com.xunmeng.pinduoduo,\",\"os_version\":\"4.4.4\",\"vendor\":\"samsung\",\"model\":\"SM-A5000\",\"cid\":217598465,\"lac\":22014}","ui":null,"ec":null,"logType":1,"slotId":4126}
解析出其中的每个字段:(这里是一个jason里面套json的格式,所以要用两层嵌套解析);
select
get_json_object(json,'$.appId') as app_id,
get_json_object(json,'$.deviceId') as device_id,
get_json_object(json,'$.ui') as ui,
get_json_object(json,'$.ec') as ec,
get_json_object(json,'$.logType') as log_type,
get_json_object(get_json_object(json,'$.md'),'$.imei') as imei,
get_json_object(get_json_object(json,'$.md'),'$.idfa') as idfa,
get_json_object(get_json_object(json,'$.md'),'$.nt') as nt,
get_json_object(json,'$.slotId') as slot_id
from tableA ;