payment error

select  p.payment_id
            ,p.channel_status

        -- CYBS ERROR --
        ,json_extract_scalar(p.extra_data, '$.charge_response.errorInformation.reason') as error_reason1
        ,json_extract_scalar(p.extra_data, '$.charge_response.reason') as error_reason2
        ,json_extract_scalar(p.extra_data, '$.update_response.reason_code') as error_reason3
        ,json_extract_scalar(p.extra_data, '$.fail_reason') as error_reason4
        ,json_extract_scalar(p.extra_data, '$.pay_result.fail_reason') as error_reason5
        ,json_extract_scalar(p.extra_data, '$.otp_result.fail_reason') as error_reason6
        ,json_extract_scalar(p.extra_data, '$.pay_result.error') as error_reason7
        ,json_extract_scalar(p.extra_data, '$.otp_required_pay_result.error') as error_reason8
        ,json_extract_scalar(p.extra_data, '$.otp_require_redirect_pay_result.error') as error_reason9
        ,json_extract_scalar(p.extra_data, '$.commit_log.reason_code_text') as payment_responsetext1
        ,json_extract_scalar(p.extra_data, '$.notify_log.reason_code_text') as payment_responsetext2
        ,json_extract_scalar(p.extra_data, '$.expire_errmsg') as payment_expire_responsetext
        ,json_extract_scalar(p.extra_data, '$.cancel_errmsg') as payment_cancel_msg
        ,json_extract_scalar(extra_data, '$.cybs_response.message') as cybs_message
        ,json_extract_scalar(extra_data, '$.params.checkout.mcc') as mcc
        ,json_extract_scalar(extra_data, '$.use_3ds.is_enabled') AS sent_for_3ds
         ,case  when json_extract_scalar(extra_data, '$.cybs_response.payer_authentication_specification_version') is not null then json_extract_scalar(extra_data, '$.cybs_response.payer_authentication_specification_version')
                when regexp_like(json_extract_scalar(extra_data, '$.cybs_response.payer_authentication_proof_xml'), 'version>1.0.2&lt') = true then '1.0.2'
                else NULL
        end as specification_version

        -- AIRPAY ERROR & MESSAGE --
        ,json_extract_scalar(p.extra_data, '$.pay_result.result') as airpay_error1
        ,json_extract_scalar(p.extra_data, '$.otp_required_pay_result.result') as airpay_error2
        ,json_extract_scalar(p.extra_data, '$.otp_require_redirect_pay_result.result') as airpay_error3
        ,json_extract_scalar(p.extra_data, '$.charge_response.errorInformation.message') as error_message1
        ,json_extract_scalar(p.extra_data, '$.charge_response.message') as error_message2
        ,json_extract_scalar(p.extra_data, '$.update_response.reason_code_text') as error_message3

        -- VN Airpay iBanking ERROR --
        ,json_extract_scalar(p.extra_data, '$.notify_request_params.message') as ibank_error_message1
        ,json_extract_scalar(p.extra_data, '$.check_txn_response.message') as ibank_error_message2
        -- 3DS check --
        ,json_extract_scalar(p.extra_data, '$.commit_log.eci') eci1
        ,json_extract_scalar(p.extra_data, '$.notify_log.eci') eci2
        ,json_extract_scalar(p.extra_data, '$.commit_log.enroll_check') enrolcheck1
        ,json_extract_scalar(p.extra_data, '$.notify_log.enroll_check') enrolcheck2
        ,json_extract_scalar(p.extra_data, '$.cybs_response.payer_authentication_pares_status') pares
        -- ORDER_ID --
        ,json_extract_scalar(p.extra_data, '$.pay_result.order.airpay_order_id') as oid1
        ,json_extract_scalar(p.extra_data, '$.update_result.order.airpay_order_id') as oid2
        ,json_extract_scalar(p.extra_data, '$.commit_result.order.airpay_order_id') as oid3
        ,json_extract_scalar(p.extra_data, '$.otp_required_pay_result.order.airpay_order_id') as oid4
from    shopeepay.shopee_payment_module_payment_vn_db__payment_v2_tab__vn_daily_s2_live p
where   1 = 1
  and   p.ctime >= cast(to_unixtime(date_trunc('month', current_date - interval '90' day)) as int) + 3600
  and   p.payment_type = 4  -- PAYMENT