当前位置：首页 > news >正文

【MaxCompute】MaxCompute 处理复杂json、json数组

news 2023/9/22 12:16:43

json数组

数据样例：

[{"date":"2016-09-24","period":1,"custom_dimension":"","term_type_id":14}]

GET_JSON_OBJECT 函数：

SELECT GET_JSON_OBJECT(name_tmp,'$.date')FROM (SELECT regexp_replace(regexp_replace(regexp_replace(json_str,'^\\[',''),'\\]$',''),'},\\{','}|{') as json_str1from (select '[{"date":"2016-09-24","period":1,"custom_dimension":"","term_type_id":14}]' as json_str ) t1) t2lateral view explode(split(json_str1,'\\|')) b AS name_tmp
;

复杂json串

数据样例：

{"approved":[{"code":2,"receiver_type":12,"title_params":[1],"content_params":[1,3,2],"type":1}],"remind":[{"code":3,"receiver_type":6,"title_params":[],"content_params":[1,3,2],"type":1}]}

GET_JSON_OBJECT 函数：

SELECT GET_JSON_OBJECT(name_tmps,'$.title_params')FROM (SELECT regexp_replace(regexp_replace(regexp_replace(json_str,'^\\[',''),'\\]$',''),'},\\{','}|{') as json_str1from (select (SELECT GET_JSON_OBJECT(name_tmp,'$.remind')FROM (SELECT '{"approved":[{"code":2,"receiver_type":12,"title_params":[1],"content_params":[1,3,2],"type":1}],"remind":[{"code":3,"receiver_type":6,"title_params":[],"content_params":[1,3,2],"type":1}]}' AS name_tmp) )as json_str ) t1) t2lateral view explode(split(json_str1,'\\|')) b AS name_tmps
;