HIVE解析json格式数据

1.最近遇到一个需求，数据是这样的：

| id | conditions |
|312|{“diyItems”:[{“a”:510723474755551232,“b”:1,“c”:80,“d”:507,“e”:2,“f”:45,“g”:30},{“a”:495134318704852992,“b”:2,“c”:90,“d”:229,“e”:0,“f”:48,“g”:118},{“a”:568838501110611968,“b”:3,“c”:90,“d”:642,“e”:0,“f”:44,“g”:69}]}
|313|{“diyItems”:[{“a”:510723474755551232,“b”:1,“c”:80,“d”:507,“e”:2,“f”:45,“g”:30},{“a”:495134318704852992,“b”:2,“c”:90,“d”:229,“e”:0,“f”:48,“g”:118},{“a”:568838501110611968,“b”:3,“c”:90,“d”:642,“e”:0,“f”:44,“g”:69}]}|

2.需求大概是说把json格式的数据打散，进行列转行，只要json中的a和c属性。

3.废话不多说开始动手吧：

第一步：我们可以看到conditions字段下的json格式数据，是json串里面套着类似于List结构,我们先取出。

GET_JSON_OBJECT(conditions,"$.diyItems")
取出之后数据是这样的：
[{"a":510723474755551232,"b":1,"c":80,"d":507,"e":2,"f":45,"g":30},{"a":495134318704852992,"b":2,"c":90,"d":229,"e":0,"f":48,"g":118},{"a":568838501110611968,"b":3,"c":90,"d":642,"e":0,"f":44,"g":69}]

第二步：我们把List结构中每个元素拿出来形成数组，方便后期列转行。

split(regexp_replace(regexp_replace(GET_JSON_OBJECT(conditions,"$.diyItems"),'\\[\\{',''),'}]',''),'},\\{')
我是先把"[{"和"}]"换成" "然后按照"},{"切开形成数组

第三步：行转列

  select id,str_to_map(newjson,",",":")['a'] ids,str_to_map(newjson,",",":")['c'] 
     from wms_ods.ods_cms_activity
     LATERAL  view explode(split(regexp_replace(regexp_replace(GET_JSON_OBJECT(conditions,"$.diyItems"),'\\[\\{',''),'}]',''),'},\\{')) jsstr as newjson


   ps：str_to_map(newjson,",",":"）第二个参数","区分每个k-v对，第三个参数区分每个k与v

THE END

id1,510723474755551232,80
id1,495134318704852992,90
.........