json数组直接解析

 select did,scheme_from,max(account_id) account_id from (
                  select omgid,qimei36,nvl(omgid,qimei36) did,scheme_from,get_json_object(col, '$.account_id') as account_id
                  from (
                      select tdbank_imp_date,
                              regexp_replace(get_json_object(origin,'$.origin'),'\\\\','') origin,
                              get_json_object(ext_js,'$.qidong.omgid') omgid,
                              get_json_object(ext_js,'$.qidong.qimei36') qimei36,
                              get_json_object(ext_js,'$.qidong.scheme_from') scheme_from
                          from pcg_atta_public_tdbank::t_atta_v1_06b00052690
                          where tdbank_imp_date >= '2022011000' and tdbank_imp_date <= '2022011023'
                  ) pp
                  lateral view explode(split(regexp_replace(regexp_extract(pp.origin, '^\\[(.+)\\]$', 1),'\\}\\,\\{', '\\}\\|\\|\\{'),'\\|\\|')) ss as col
              ) group by did,scheme_from