Hive如何解析复杂的json字符串嵌套结构

Hive解析json字符串

json类似于python的字典，类似于key-value格式，但是可以实现嵌套的数据结构

一、json格式

json简单格式：

{
  "name":"Tom",
  "age":20,
  "sex":"male"
}

上面是简单的json格式，还可以有复杂的嵌套格式

{
  "name":"Tom",
  "age":20,
  "sex":"male",
  "friend":["Jerry","Lilly","Bob"],
  "local":{
    "country":"China",
    "city":"beijing",
    "foods":["dock","douzhi","dianxin"]
  }
}

二、Hive解析json

Hive提供了解析json格式的udf函数：get_json_object(string json_string, string path)

1. 解析简单的json格式

   获得json中的name字段的value

   select get_json_object('{
     "name":"Tom",
     "age":20,
     "sex":"male"
   }', '$.name');
   

   输出：

   Tom
   

2. 解析复杂的json

   获得Tom朋友的第二个，以及所在的城市，和城市的美食

   1⃣️获得朋友中的第二个

   select get_json_object('{
     "name":"Tom",
     "age":20,
     "sex":"male",
     "friend":["Jerry","Lilly","Bob"],
     "local":{
       "country":"China",
       "city":"beijing",
       "foods":["dock","douzhi","dianxin"]
     }
   }', '$.friend[1]');
   

   输出：

   Lilly
   

2⃣️Tom所在的城市

select get_json_object('{
  "name":"Tom",
  "age":20,
  "sex":"male",
  "friend":["Jerry","Lilly","Bob"],
  "local":{
    "country":"China",
    "city":"beijing",
    "foods":["dock","douzhi","dianxin"]
  }
}', '$.local.city');

输出：

beijing

3⃣️城市的第二个美食

select get_json_object('{
  "name":"Tom",
  "age":20,
  "sex":"male",
  "friend":["Jerry","Lilly","Bob"],
  "local":{
    "country":"China",
    "city":"beijing",
    "foods":["dock","douzhi","dianxin"]
  }
}', '$.local.foods[1]');

输出：

douzhi