MaxCompute将json数组拆分成多行

 

 

select
     GET_JSON_OBJECT(name_tmp,'$.val')      as val
    ,GET_JSON_OBJECT(name_tmp,'$.area')     as area
    ,GET_JSON_OBJECT(name_tmp,'$.setVal')   as setVal
    ,GET_JSON_OBJECT(name_tmp,'$.isExceed') as isExceed
from (
    -- 删除前后的中括号并修改分隔符
    select 
        regexp_replace(regexp_replace(regexp_replace(json_str,'^\\[',''),'\\]$',''),'},\\{','}|{') as json_str1
    from (
        select '[{"val":5,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":7,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":7,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":1,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":5,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":7,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":5,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":6,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":5,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":5,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":5,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":6,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""}]' as json_str
    ) t1
) t2
lateral view explode(split(json_str1,'\\|')) b AS name_tmp
;

+------------+------------+------------+------------+
| val        | area       | setval     | isexceed   | 
+------------+------------+------------+------------+
| 5          | 测区1        |            | false      | 
| 7          | 测区1        |            | false      | 
| 7          | 测区1        |            | false      | 
| 1          | 测区1        |            | false      | 
| 5          | 测区1        |            | false      | 
| 7          | 测区1        |            | false      | 
| 5          | 测区1        |            | false      | 
| 6          | 测区1        |            | false      | 
| 5          | 测区1        |            | false      | 
| 5          | 测区1        |            | false      | 
| 5          | 测区1        |            | false      | 
| 6          | 测区1        |            | false      | 
+------------+------------+------------+------------+

 

 

    select
         id
        ,area
        ,concat_ws(',',collect_list(val))                as val_list
        ,avg(case when rn1 > 3 and rn2 > 3 then val end) as avg_val
        ,max(fck)                                        as fck
        ,max(avg_val)                                    as avg_val
        ,max(carbon)                                     as carbon
        ,max(is_new)                                     as is_new
    from (
        select
             id
            ,val
            ,area
            ,setVal
            ,isExceed
            ,fck
            ,avg_val
            ,carbon
            ,case when nvl(fck,'') = '' then 0 else 1 end           as is_new -- 0为旧,1为新
            ,row_number() over(partition by id,area order by val asc ) as rn1
            ,row_number() over(partition by id,area order by val desc) as rn2
        from (
            select
                 id
                ,GET_JSON_OBJECT(name_tmp,'$.val')      as val
                ,GET_JSON_OBJECT(name_tmp,'$.area')     as area
                ,GET_JSON_OBJECT(name_tmp,'$.setVal')   as setVal
                ,GET_JSON_OBJECT(name_tmp,'$.isExceed') as isExceed
                ,GET_JSON_OBJECT(name_tmp,'$.fck')      as fck      -- 强度值
                ,GET_JSON_OBJECT(name_tmp,'$.avg')      as avg_val  -- 平均值
                ,GET_JSON_OBJECT(name_tmp,'$.carbon')   as carbon   -- 碳化深度
            from (
                -- 删除前后的中括号并修改分隔符
                select 
                     id
                    ,regexp_replace(regexp_replace(regexp_replace(json_str,'^\\[',''),'\\]$',''),'},\\{','}|{') as json_str1
                from (
                    select id,json_str from json_demo_02
                ) t1
            ) t2
            lateral view explode(split(json_str1,'\\|')) b AS name_tmp
        ) t3
    ) t4
    group by 
         id
        ,area
    order by 
         id
        ,area