HIVE获取json字段特定值（单个json或者json数组）

1.获取单个json字符串里的某一特定值

函数：get_json_object(单个json,‘$.要获取的字段’)

示例：

代码：SELECT get_json_object(‘{“NAME”:“张三”,“ID”:“1”}’,‘$.NAME’) as name;

SELECT get_json_object(‘{“NAME”:“张三”,“ID”:“1”}’,‘$.NAME’);

2. json_tuple

语法：json_tuple(json_string, k1, k2 …)

说明：解析json的字符串json_string,可指定多个json数据中的key，返回对应的value。如果输入的json字符串无效，那么返回NULL。

示例：

select  b.name ,b.age from tableName a lateral view json_tuple('{"name":"zhangsan","age":18}','name','age') b as name,age;

注意：上面的json_tuple函数中没有

     . 
    
   
     如果在使用 
    
   
     j 
    
   
     s 
    
   
     o 
    
    
    
      n 
     
    
      t 
     
    
   
     u 
    
   
     p 
    
   
     l 
    
   
     e 
    
   
     函数时加上 
    
   
  
    . 如果在使用json_tuple函数时加上 
   
  
.如果在使用jsont​uple函数时加上.就会解析失败.

Hive解析json数组
一、嵌套子查询解析json数组

select good_id,get_json_object(sale_json,'$.monthSales')as monthSales from tableName  LATERAL VIEW explode(split(goods_id,','))goods as good_id  LATERAL VIEW explode(split(regexp_replace(regexp_replace(json_str ,'\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) sales as sale_json;

select t1.*from(select
    t0.*,get_json_object(tmp.task_per_step_definition,'$.daysOfMedication')as daysOfMedication
    ,get_json_object(tmp.task_per_step_definition,'$.dosageForm')as dosageForm
    ,get_json_object(tmp.task_per_step_definition,'$.drugCode')as drugCode
    ,get_json_object(tmp.task_per_step_definition,'$.frequency')as frequency
    ,get_json_object(tmp.task_per_step_definition,'$.frequencyName')as frequencyName
    ,get_json_object(tmp.task_per_step_definition,'$.groupNo')as groupNo
    ,get_json_object(tmp.task_per_step_definition,'$.medicineName')as medicineName
    ,get_json_object(tmp.task_per_step_definition,'$.price')as price
    ,get_json_object(tmp.task_per_step_definition,'$.remark')as remark
    ,get_json_object(tmp.task_per_step_definition,'$.route')as route
    ,get_json_object(tmp.task_per_step_definition,'$.routeName')as routeName
    ,get_json_object(tmp.task_per_step_definition,'$.singleDose')as singleDose
    ,get_json_object(tmp.task_per_step_definition,'$.singleDoseUnit')as singleDoseUnit
    ,get_json_object(tmp.task_per_step_definition,'$.specification')as specification
    ,get_json_object(tmp.task_per_step_definition,'$.totalDose')as totalDose
    ,get_json_object(tmp.task_per_step_definition,'$.totalDoseUnit')as totalDoseUnit
    ,get_json_object(tmp.task_per_step_definition,'$.unitPrice')as unitPrice
 from wedw_dwd.pg_med_risk_emr_western_medicine_prescription_df t0
lateral view explode(split(regexp_replace(regexp_replace(regexp_replace(t0.medicines,'^\\[\\{','\\{'),'\\}\\]$','\\}'),'\\}\\,\\{\\"nodeId\\"','\\}\\@\\#\\{\\"nodeId\\"'),'\\@\\#')) tmp as task_per_step_definition
where t0.date_id = date_sub(current_date(),1)and t0.org_name like'%辛口%') t1