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

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_str1
        from (
            select '[{"date":"2016-09-24","period":1,"custom_dimension":"","term_type_id":14}]' as json_str 
        ) t1
    ) t2
    lateral 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_str1
        from (
            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
    ) t2
    lateral view explode(split(json_str1,'\\|')) b AS name_tmps
;