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

Hive解析json字符串

一、json格式二、Hive解析json

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

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格式的udf函数：get_json_object(string json_string, string path)

解析简单的json格式

获得json中的name字段的value

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

输出：

Tom

解析复杂的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