redshift多层json解析

redshfit不支持json格式数据

测试数据:
{“appVersion”:“14”,“bundleid”:“com.mg.pandaloan”,“firstOpen”:“0”,“deviceId”:“0ea6419f-64de-4b3a-87ef-58c0ddc13168”,“userAgent”:“Dalvik/2.1.0 (Linux; U; Android 9; V1829A Build/PKQ1.181030.001)”,“longitude”:null,“networkAc”:“0”,“devicePlatform”:“0”,“deviceManufacturer”:“vivo”,“deviceType”:“V1829A”,“deviceVersion”:“28”,“screenResolution”:“1080|2340”,“deviceInfo”:"{“androidId”:“c46de9b3835c0c6b”,“availMemory”:“2.50 GB”,“codeName”:“REL”,“cpu”:“arm64-v8a”,“cpuInfo”:“AArch64 Processor rev 13 (aarch64) “,“deviceSoftwareVersion”:“38”,“display”:“1080|2340”,“gsmCellLocation”:””,“hardware”:“qcom”,“imei”:“861042048386536”,“imsi”:“460016198623228”,“language”:“zh”,“macAddress”:“DC-31-D1-DD-D6-15”,“manufacturer”:“vivo”,“model”:“V1829A”,“networkOperator”:“46001”,“networkOperatorName”:“CHN-UNICOM”,“networkType”:“0”,“product”:“PD1829”,“radioVersion”:“710_GEN_PACK-1.208529.3.208919.1,710_GEN_PACK-1.208529.3.208919.1”,“release”:“9”,“sdkVersion”:“28”,“serialNumber”:“6b064310”,“totalMemory”:“8.00 GB”,“uuid”:“02360dcc-22dc-322d-b736-51135c2fa3ba”}"}

• select
  device_id,device_info,json_extract_path_text(REPLACE(json_extract_path_text(device_info,
  ‘deviceInfo’),’\’,’’),‘uuid’) device_info from device_tokens

–redshift 解析json数组
SELECT apply_id,created_at,
json_extract_path_text(json_extract_array_element_text(review_hit_rules, seq.i),‘rule_name’) as type
FROM oversea_id.sc_risk_review_records , oversea_id.seq AS seq
— arman why is this less than the array
where review_type=‘ORDER_SUBMIT’
and review_result=‘REJECTED’
and seq.i < JSON_ARRAY_LENGTH(review_hit_rules) order by created_at desc,apply_id

redshift 列转行
select pid, listagg(hit_remark, ‘,’) within group (order by pid)
from oversea_id.sc_pro_process_data
group by pid