Greenplum 解析json

需求

     从数据源采集到运营商通话数据,为json格式,字段长度比较长,一个字段可能占2M, 有接近100个字段, 按要求解析其中10个字段,并分行

 

测试数据下载地址

https://download.youkuaiyun.com/download/go_away_gui/11065225

 

方法 1 , 使用gp自带的行转列函数regexp_split_to_table, 实现SQL相对简单, 效率低下, 使用附件测试数据, 需要13秒解析出结果

select  regexp_split_to_table(
        replace(
              replace(
        replace(
        (content->'data'->>'contact_list'),
            '[{', '{'),
            '}]', '}'),
            '},{', '}==={'), 
                '===')        from test_json;

 

方法 2, 自己拆分解析, SQL实现复制, 性能较高, 使用附件测试数据, 不到1秒解析出结果

        select json_text->>'phonenum' phone_num,
           json_text->>'phonenumloc' phone_num_loc ,
           json_text->>'contactname' contact_name,
           json_text->>'needstype' needs_type ,
           (json_text->>'callcnt')::int call_cnt ,
           (json_text->>'calllen')::decimal call_len ,
           (json_text->>'calloutcnt')::int call_out_cnt ,
           (json_text->>'calloutlen')::decimal call_out_len ,
           (json_text->>'callincnt')::int call_in_cnt ,
           (json_text->>'callinlen')::decimal call_in_len ,
           json_text->>'prelation' p_relation ,
           (json_text->>'contact1w')::int contact_1w ,
           (json_text->>'contact1m')::int contact_1m ,
           (json_text->>'contact3m')::int contact_3m ,
           (json_text->>'contact3mplus')::int contact_3m_plus ,
           (json_text->>'contactearlymorning')::int contact_early_morning ,
           (json_text->>'contactmorning')::int contact_morning ,
           (json_text->>'contactnoon')::int contact_noon ,
           (json_text->>'contactafternoon')::int contact_afternoon ,
           (json_text->>'contactnight')::int contact_night ,
           case when json_text->>'contactallday' = 'true' then 1 else 0 end contact_all_day ,
           (json_text->>'contactweekday')::int contact_weekday ,
           (json_text->>'contactweekend')::int contact_weekend ,
           (json_text->>'contactholiday')::int contact_holiday 
      from (
    select ('{'||replace(
           replace(
                 unnest(
                 replace(
                 replace(
                 replace(
                 replace(
                 replace(
                     replace(
                     replace(
                     replace(
                 (content->'data'->>'contact_list')::text, 
                     '\"', ''),
                     '_', ''),
                     '\\', ''),
                 ',', '==='),
                 '}==={', ','),
                 '[{', '{'),
                 '}]', '}'),
                 '"','___')::text[]),
                 '___','"'),
                 '===',',')||'}')::json json_text
      from test_json ) tab;