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

最新推荐文章于 2025-09-18 11:33:28 发布

原创最新推荐文章于 2025-09-18 11:33:28 发布 · 4.2k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#大数据

maxcompute 专栏收录该内容

3 篇文章

订阅专栏

本文深入探讨了如何使用GET_JSON_OBJECT函数从复杂的JSON数据中抽取特定字段，通过具体示例展示了处理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
;