筛选json里内层数组里字段等于xx的所有记录:
用正则表达式:
SELECT * FROM (SELECT user_id,borrow_nid,outstr,split(regexp_replace(regexp_extract(get_json_object(get_json_object(get_json_object(outstr,'$.data'),'$.data'),'$.others'),'^\\[(.+)\\]$',1),'\\}\\,\\{', '\\}\\|\\|\\{'),'\\|\\|') as str FROM es_rule.t_feature_creditsource WHERE dt < '2018-09-25' AND credit_main_name ='pingAn' AND credit_child_name ='blackList ) pp lateral view explode(pp.str) ss as col WHERE get_json_object(ss.col,'$.matchType') ='imei' ;
---json是: