Hive解析json

需要解析的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,   //去掉引号 " "

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;