jsonarray打jar包处理.sql

MSCK REPAIR TABLE dw_ods.ods_app_tmall_trade_fullinfo_get_topic_iu_h;

drop table if exists dw_tmp.a02;
create table dw_tmp.a02(
    content string
);
insert overwrite table dw_tmp.a02
select get_json_object(get_json_object(get_json_object(get_json_object(content,'$.trade_fullinfo_get_response'),'$.trade'),'$.promotion_details'),'$.promotion_detail')
from
dw_ods.ods_app_tmall_trade_fullinfo_get_topic_iu_h
where  dt >= '2023030710' limit 1;





WITH a_json
         AS (SELECT '
{
   "fixed_charge": {
      "discount": 1,
      "fixed_fee": -1,
      "end_amount": -1,
      "actual_rate": -1,
      "origin_rate": -1,
      "start_amount": -1
   },
   "float_charges": [
      {
         "discount": 10,
         "fixed_fee": -1,
         "end_amount": 1000000,
         "actual_rate": 0.01,
         "origin_rate": 0.01,
         "start_amount": 0
      },
      {
         "discount": 10,
         "fixed_fee": -1,
         "end_amount": 3000000,
         "actual_rate": 0.006,
         "origin_rate": 0.006,
         "start_amount": 1000000
      },
      {
         "discount": 10,
         "fixed_fee": -1,
         "end_amount": 5000000,
         "actual_rate": 0.002,
         "origin_rate": 0.002,
         "start_amount": 3000000
      },
      {
         "discount": -1,
         "fixed_fee": 1000,
         "end_amount": -999,
         "actual_rate": -1,
         "origin_rate": -1,
         "start_amount": 5000000
      }
   ],
   "user_discount_flag": 0
}' AS doc)
SELECT GET_JSON_OBJECT(doc, '$.fixed_charge.discount'),
       GET_JSON_OBJECT(doc, '$.fixed_charge.fixed_fee'),
       GET_JSON_OBJECT(doc, '$.fixed_charge.end_amount'),
       GET_JSON_OBJECT(doc, '$.fixed_charge.actual_rate'),
       GET_JSON_OBJECT(doc, '$.fixed_charge.origin_rate'),
       GET_JSON_OBJECT(doc, '$.fixed_charge.start_amount'),
       GET_JSON_OBJECT(float_charges, '$.discount'),
       GET_JSON_OBJECT(float_charges, '$.fixed_fee'),
       GET_JSON_OBJECT(float_charges, '$.end_amount'),
       GET_JSON_OBJECT(float_charges, '$.actual_rate'),
       GET_JSON_OBJECT(float_charges, '$.origin_rate'),
       GET_JSON_OBJECT(float_charges, '$.start_amount'),
       GET_JSON_OBJECT(doc, '$.float_charges')
FROM a_json t
         LATERAL VIEW
--              EXPLODE_JSON_ARRAY(GET_JSON_OBJECT(doc, '$.float_charges')) tmp AS float_charges;
             ExplodeJSON2(GET_JSON_OBJECT(doc, '$.float_charges')) tmp AS float_charges;




create table dw_ods.ods_app_tmall_trade_fullinfo_get_topic_iu_h(
    content string
)partitioned by (dt string);

create table dw_ods.ods_app_tmall_items_seller_list_get_topic_iu_h(
    content string
)partitioned by (dt string);




select count(1)
from (
    select dt,
           single_sku_array,
           item
    from (
       select dt,
              item,
              get_json_object(get_json_object(item,'$.skus'),'$.sku') sku_array
       from (
           select
                  get_json_object(get_json_object(get_json_object(content, '$.items_seller_list_get_response'), '$.items'),'$.item')item_array,
                  dt
           from dw_ods.ods_app_tmall_items_seller_list_get_topic_iu_h
           where dt >= '2023030701' and  dt <= '2023030701'
       )t LATERAL VIEW EXPLODE_JSON_ARRAY(item_array) tmp as item
       where get_json_object(get_json_object(item,'$.skus'),'$.sku') is not null
    ) LATERAL VIEW EXPLODE_JSON_ARRAY(sku_array) tmp2 as single_sku_array
);