需要解析的json為：
{
????"_id":{
? ?? "$oid":"580db8cf8ead0e093c876a72"
? ? ? ?? }
,
????"aClassId":["15397303000114"],
????"iBookId":{
? ? ? ? "$numberLong":"145"
? ? ? ?? }
,
????"iCreateTime":{
? ? ? "$numberLong":"1477294287"
? ? ?? }
,
????"iEndTime":{
? ? ?? "$numberLong":"1477324799"
? ? ? ? }
,
????"iQuestId":{
? ? ?? "$numberLong":"17"
? ?? }
,
????"iState":{
? ? ?? "$numberLong":"1"
? ? ? }
,
????"iTchId":{
? ? ? "$numberLong":"39250"
? ? ? }
,
????"sQuestInfo":"{"parentId":3,"parentName":"Starter unit 2","childName":"3c"}"
}
解析代碼為：
select
c.oid,
regexp_replace(b.aClassId, '"', '') as aClassId, ? //去掉引號(hào) " "
c.parentId,
c.parentName,
c.childName,
c.iBookId,
c.iCreateTime,
c.iEndTime,
c.iQuestId,
c.iState,
c.iTchId
from odsdata.speaktrain_0001_teacher_homework a
lateral view json_tuple(a.json,'_id','aClassId','iBookId','iCreateTime','iEndTime','iQuestId','iState','iTchId','sQuestInfo') b as id,aClassId,iBookId,iCreateTime,iEndTime,iQuestId,iState,iTchId,sQuestInfo
lateral view json_tuple(b.id,'$oid') c as oid
lateral view json_tuple(b.iBookId,'$numberLong') c as iBookId
lateral view json_tuple(b.iCreateTime,'$numberLong') c as iCreateTime
lateral view json_tuple(b.iEndTime,'$numberLong') c as iEndTime
lateral view json_tuple(b.iQuestId,'$numberLong') c as iQuestId
lateral view json_tuple(b.iState,'$numberLong') c as iState
lateral view json_tuple(b.iTchId,'$numberLong') c as iTchId
lateral view json_tuple(b.sQuestInfo,'parentId','parentName','childName') c as parentId,parentName,childName limit 2;
