{"af_timestamp":"1513714062309","installAttribution":"{"af_status":"Organic","af_message":"organic install"}","sdk":"19","app_version_name":"1.4.0","eventName":"af_purchase","deviceData":{"arch":"","cpu_abi":"armeabi-v7a","cpu_abi2":"armeabi","build_display_id":"KTU84P.T113XXS0AQC2"},"deviceFingerPrintId":"ffffffff-c95a-d351-0000-0000678a7658","receipt_data":{"signature":"","sig-data":"{"purchaseToken":"vopwcvklkztcrfmvfulrxcxu","developerPayload":"{\"amount\":0.0,\"class\":\"com.unic.googleplay.GPOrderInfo\",\"currency\":\"\",\"orderId\":\"235474\",\"productId\":\"gp_delphi_money_6\"}","packageName":"com.camex.tactical.monster","purchaseState":0,"orderId":"6697165655337690790.3387143787184440","purchaseTime":1513714060006,"productId":"gp_delphi_money_6"}","public-key":"MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAgnP/dxtGhi0+0AzKnXiE5fypGWjZgToJHdA96oPxE1/Yni9/sfgv/vFGY2kpKRpvXlt2cmbRrXjSO8pYArqWASPffEv2gFvPUSFh+KdjJfu9T3nm5Ta77cRDJ2CemSzGuDqmyuoZCuTzPg8VuxuVMJYG76EfLRl4ggrFAG/G8nEhA3bhWk8Y3hg9ilu+DbbWHpKLrRVlfx71GOVKJ7tOn6hsk7YHh32dojXNU03UzashnJ70Zs+tLZYZ9zeoh9PuI8N69PuunJMSx/ssajRiRCF4sJHQ/FrD2pdIHEBi8LOf6PLVOPsZaA4qa4KPHbGqG9D9S5i2GJtDH2rQnEvcmwIDAQAB"},"buildnumber":"4.7.3","eventValue":"","af_v":"f5b8d1553a5f57e25c593526eea71195c6fbdddd","uid":"1513713791482-9014482895754680161","af_preinstalled":"false","app_id":"com.camex.tactical.monster","af_v2":"4238449b37ddac1c03b8620f8a6644ab0cd9e07d","advertiserIdEnabled":"true","carrier":"","af_events_api":"1","brand":"samsung","lang":"italiano","currency":"USD","registeredUninstall":false,"counter":"2","app_version_code":"75","product":"goyavewifixx","date2":"2017-12-19_190311-0100","advertiserId":"853d1a30-17e3-48fb-8e3e-a0ac9ffd2626","appid":"com.camex.tactical.monster","appsflyerKey":"d7hYWorekhVd5TtsxkmrHg","platformextension":"android_unity","isFirstCall":"false","operator":"","deviceType":"user","firstLaunchDate":"2017-12-19_190339-0100","device":"goyavewifi","network":"WIFI","version":"v4","lang_code":"it","price":"100","date1":"2017-12-19_190311-0100","country":"IT","installDate":"2017-12-19_190311-0100","isGaidWithGps":"true","iaecounter":"1","model":"SM-T113"}
我想要的是
"receipt_data":{"signature":"","sig-data":"{"purchaseToken":"vopwcvklkztcrfmvfulrxcxu","developerPayload":"{\"amount\":0.0,\"class\":\"com.unic.googleplay.GPOrderInfo\",\"currency\":\"\",\"orderId\":\"235474\",\"productId\":\"gp_delphi_money_6\"}","packageName":"com.camex.tactical.monster","purchaseState":0,"orderId":"6697165655337690790.3387143787184440","purchaseTime":1513714060006,"productId":"gp_delphi_money_6"}","public-key":"MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAgnP/dxtGhi0+0AzKnXiE5fypGWjZgToJHdA96oPxE1/Yni9/sfgv/vFGY2kpKRpvXlt2cmbRrXjSO8pYArqWASPffEv2gFvPUSFh+KdjJfu9T3nm5Ta77cRDJ2CemSzGuDqmyuoZCuTzPg8VuxuVMJYG76EfLRl4ggrFAG/G8nEhA3bhWk8Y3hg9ilu+DbbWHpKLrRVlfx71GOVKJ7tOn6hsk7YHh32dojXNU03UzashnJ70Zs+tLZYZ9zeoh9PuI8N69PuunJMSx/ssajRiRCF4sJHQ/FrD2pdIHEBi8LOf6PLVOPsZaA4qa4KPHbGqG9D9S5i2GJtDH2rQnEvcmwIDAQAB"}

REGEXP_EXTRACT(raw_device_params_json,r'"sig-data":"{\"orderId\":\"([^"]*)\"') AS order 这个表达式查出来的是6697165655337690790.3387143787184440
raw_device_params_json 这个是我们表里的列名

REGEXP_EXTRACT(raw_device_params_json,r'"receipt_data":({.*}),"buildnumber"') AS receipt_data