API tools faq
paste
Advertisement
kazi_omar

query json

kazi_omar
Jul 7th, 2025 (edited)
475
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
JSON 12.52 KB | Source Code | 0 0
raw download clone embed print report
  1. {
  2.   "_archival_operations_metadata": {
  3.     "description": "Archival operations with modular SQL structure for dual database operations",
  4.     "pattern": "source_selection + field_extraction + json_consolidation + destination_insert",
  5.     "databases": {
  6.       "source": "bl_ria_api (MySQL_BL_RIA_API)",
  7.       "destination": "bl_ria_archive (MySQL_BL_RIA_ARCHIVE)"
  8.     }
  9.   },
  10.   "archive_transaction_chain": {
  11.     "type": "archival_operation",
  12.     "description": "Archive complete transaction lifecycle with 7-table JSON consolidation",
  13.     "source_operations": {
  14.       "database": {
  15.         "env": "MYSQL_BL_RIA_API"
  16.       },
  17.       "selection": {
  18.         "description": "Find eligible transactions for archiving",
  19.         "sql": "SELECT io.transaction_no FROM bl_ria_api.init_operations io WHERE io.is_recurring = 0 AND io.created_at < DATE_SUB(NOW(), INTERVAL ? DAY) AND NOT EXISTS (SELECT 1 FROM bl_ria_archive.archived_operations ao WHERE ao.transaction_no = io.transaction_no) ORDER BY io.created_at ASC"
  20.       },
  21.       "field_extraction": {
  22.         "description": "Extract core fields from init_operations",
  23.         "base_fields": "SELECT io.transaction_no, io.created_at, io.retailer_channel_id, io.subchannel_id, io.initiator_msisdn, io.id, io.payment_data, io.is_recurring FROM bl_ria_api.init_operations io WHERE io.transaction_no IN (?)",
  24.         "payment_info": {
  25.           "payment_channel": "SELECT tr.payment_channel FROM bl_ria_api.transaction_requests tr WHERE tr.transaction_no = ? LIMIT 1",
  26.           "payment_id": "SELECT COALESCE((SELECT t.payment_id FROM bl_ria_api.transactions t JOIN bl_ria_api.transaction_requests tr ON t.transaction_request_id = tr.id WHERE tr.transaction_no = ? LIMIT 1), (SELECT tr.payment_id FROM bl_ria_api.transaction_requests tr WHERE tr.transaction_no = ? LIMIT 1))",
  27.           "payment_reference": "SELECT t.payment_reference_no FROM bl_ria_api.transactions t JOIN bl_ria_api.transaction_requests tr ON t.transaction_request_id = tr.id WHERE tr.transaction_no = ? LIMIT 1"
  28.         },
  29.         "customer_info": {
  30.           "customer_email": "SELECT COALESCE(NULLIF((SELECT tr.customer_email FROM bl_ria_api.transaction_requests tr WHERE tr.transaction_no = ? LIMIT 1), ''), NULLIF(io.initiator_email, ''), NULL) FROM bl_ria_api.init_operations io WHERE io.transaction_no = ?",
  31.           "target_msisdn": "SELECT rr.customer_msisdn FROM bl_ria_api.recharge_requests rr JOIN bl_ria_api.transactions t ON rr.transaction_id = t.id JOIN bl_ria_api.transaction_requests tr ON t.transaction_request_id = tr.id WHERE tr.transaction_no = ? AND rr.customer_msisdn != (SELECT io.initiator_msisdn FROM bl_ria_api.init_operations io WHERE io.transaction_no = ?) LIMIT 1"
  32.         },
  33.         "business_info": {
  34.           "campaign_id": "SELECT NULLIF(COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(io.payment_data, '$[0].cashback_campaign_id')), 'null'), (SELECT tr.campaign_id FROM bl_ria_api.transaction_requests tr WHERE tr.transaction_no = ? LIMIT 1)), 'null') FROM bl_ria_api.init_operations io WHERE io.transaction_no = ?",
  35.           "failed_reason": "SELECT COALESCE((SELECT tr.failed_reason FROM bl_ria_api.transaction_requests tr WHERE tr.transaction_no = ? AND tr.failed_reason IS NOT NULL LIMIT 1), (SELECT rfr.reason FROM bl_ria_api.refund_requests rfr JOIN bl_ria_api.transactions t ON rfr.transaction_id = t.id JOIN bl_ria_api.transaction_requests tr ON t.transaction_request_id = tr.id WHERE tr.transaction_no = ? AND rfr.reason IS NOT NULL LIMIT 1))",
  36.           "retry_count": "SELECT rr.retry_no FROM bl_ria_api.recharge_requests rr JOIN bl_ria_api.transactions t ON rr.transaction_id = t.id JOIN bl_ria_api.transaction_requests tr ON t.transaction_request_id = tr.id WHERE tr.transaction_no = ? ORDER BY rr.created_at DESC LIMIT 1"
  37.         }
  38.       },
  39.       "json_consolidation": {
  40.         "description": "7-table JSON aggregation for complete audit trail",
  41.         "init_operations": "SELECT JSON_ARRAYAGG(JSON_OBJECT('transaction_no', io2.transaction_no, 'initiator_msisdn', io2.initiator_msisdn, 'initiator_email', io2.initiator_email, 'retailer_channel_id', io2.retailer_channel_id, 'subchannel_id', io2.subchannel_id, 'callback_url_success', io2.callback_url_success, 'callback_url_fail', io2.callback_url_fail, 'callback_url_cancel', io2.callback_url_cancel, 'ipn_url', io2.ipn_url, 'ipn_status', io2.ipn_status, 'payment_data', io2.payment_data, 'status', io2.status, 'custom_params', io2.custom_params, 'user_data', io2.user_data, 'extra_info', io2.extra_info, 'is_recurring', io2.is_recurring, 'recurring_params', io2.recurring_params, 'created_at', io2.created_at, 'updated_at', io2.updated_at)) FROM bl_ria_api.init_operations io2 WHERE io2.transaction_no = ?",
  42.         "transaction_requests": "SELECT JSON_ARRAYAGG(JSON_OBJECT('id', tr.id, 'customer_email', tr.customer_email, 'transaction_no', tr.transaction_no, 'payment_id', tr.payment_id, 'payment_gateway_rule_id', tr.payment_gateway_rule_id, 'retailer_channel_id', tr.retailer_channel_id, 'mobile_number', tr.mobile_number, 'payment_amount', tr.payment_amount, 'individual_recharge_amount', tr.individual_recharge_amount, 'individual_cashback_amount', tr.individual_cashback_amount, 'individual_offer_id', tr.individual_offer_id, 'individual_iris', tr.individual_iris, 'payment_channel', tr.payment_channel, 'transaction_status', tr.transaction_status, 'currency', tr.currency, 'deleted_at', tr.deleted_at, 'created_at', tr.created_at, 'updated_at', tr.updated_at, 'bank_approved_amount', tr.bank_approved_amount, 'payment_reference_no', tr.payment_reference_no, 'customer_msisdn', tr.customer_msisdn, 'op_code', tr.op_code, 'init_operations_id', tr.init_operations_id, 'cb_account', tr.cb_account, 'failed_reason', tr.failed_reason, 'extra_info', tr.extra_info, 'is_recurring', tr.is_recurring, 'campaign_id', tr.campaign_id)) FROM bl_ria_api.transaction_requests tr WHERE tr.transaction_no = ?",
  43.         "transactions": "SELECT JSON_ARRAYAGG(JSON_OBJECT('id', t.id, 'transaction_request_id', t.transaction_request_id, 'payment_id', t.payment_id, 'payment_reference_no', t.payment_reference_no, 'gateway_charge', t.gateway_charge, 'actual_amount', t.actual_amount, 'card_type_name', t.card_type_name, 'card_number', t.card_number, 'card_expiry_date', t.card_expiry_date, 'deleted_at', t.deleted_at, 'created_at', t.created_at, 'updated_at', t.updated_at, 'bank_approved_amount', t.bank_approved_amount)) FROM bl_ria_api.transactions t JOIN bl_ria_api.transaction_requests tr2 ON t.transaction_request_id = tr2.id WHERE tr2.transaction_no = ?",
  44.         "recharge_requests": "SELECT JSON_ARRAYAGG(JSON_OBJECT('id', rr.id, 'transaction_id', rr.transaction_id, 'customer_msisdn', rr.customer_msisdn, 'ref_id', rr.ref_id, 'trx_id', rr.trx_id, 'recharge_amount', rr.recharge_amount, 'cashback_amount', rr.cashback_amount, 'iris_data', rr.iris_data, 'retry_no', rr.retry_no, 'status', rr.status, 'created_at', rr.created_at, 'updated_at', rr.updated_at, 'campaign_id', rr.campaign_id)) FROM bl_ria_api.recharge_requests rr JOIN bl_ria_api.transactions t2 ON rr.transaction_id = t2.id JOIN bl_ria_api.transaction_requests tr3 ON t2.transaction_request_id = tr3.id WHERE tr3.transaction_no = ?",
  45.         "recharges": "SELECT JSON_ARRAYAGG(JSON_OBJECT('transaction_id', r.transaction_id, 'recharge_request_id', r.recharge_request_id, 'retailer_msisdn', r.retailer_msisdn, 'customer_msisdn', r.customer_msisdn, 'recharge_amount', r.recharge_amount, 'recharge_platform', r.recharge_platform, 'payment_channel', r.payment_channel, 'campaign_id', r.campaign_id)) FROM bl_ria_api.recharges r JOIN bl_ria_api.transactions t2 ON r.transaction_id = t2.id JOIN bl_ria_api.transaction_requests tr3 ON t2.transaction_request_id = tr3.id WHERE tr3.transaction_no = ?",
  46.         "refund_requests": "SELECT JSON_ARRAYAGG(JSON_OBJECT('id', rfr.id, 'transaction_id', rfr.transaction_id, 'payment_id', rfr.payment_id, 'query_refund_request_id', rfr.query_refund_request_id, 'amount', rfr.amount, 'currency', rfr.currency, 'status', rfr.status, 'status_code', rfr.status_code, 'accepted', rfr.accepted, 'created_at', rfr.created_at, 'updated_at', rfr.updated_at, 'reason', rfr.reason)) FROM bl_ria_api.refund_requests rfr JOIN bl_ria_api.transactions t3 ON rfr.transaction_id = t3.id JOIN bl_ria_api.transaction_requests tr4 ON t3.transaction_request_id = tr4.id WHERE tr4.transaction_no = ?",
  47.         "refunds": "SELECT JSON_ARRAYAGG(JSON_OBJECT('id', rf.id, 'transaction_id', rf.transaction_id, 'refund_request_id', rf.refund_request_id, 'payment_id', rf.payment_id, 'response_id', rf.response_id, 'amount', rf.amount, 'currency', rf.currency, 'status', rf.status, 'created_at', rf.created_at, 'updated_at', rf.updated_at)) FROM bl_ria_api.refunds rf JOIN bl_ria_api.transactions t4 ON rf.transaction_id = t4.id JOIN bl_ria_api.transaction_requests tr5 ON t4.transaction_request_id = tr5.id WHERE tr5.transaction_no = ?"
  48.       }
  49.     },
  50.     "destination_operations": {
  51.       "database": {
  52.         "env": "MYSQL_BL_RIA_ARCHIVE"
  53.       },
  54.       "insert_template": {
  55.         "description": "INSERT template for archived_operations table",
  56.         "sql": "INSERT INTO bl_ria_archive.archived_operations (transaction_no, transaction_date, payment_channel, payment_id, payment_reference_no, op_type, retailer_channel_id, subchannel_id, initiator_msisdn, customer_email, target_msisdn, campaign_id, failed_reason, retry_count, archived_data, source_table, source_id, archiver_version, data_integrity_hash, transaction_chain_id) VALUES (?, ?, ?, ?, ?, 'transaction_chain', ?, ?, ?, ?, ?, ?, ?, ?, ?, 'transaction_chain', ?, 'v1.0', ?, ?)",
  57.         "field_mapping": {
  58.           "transaction_no": "source.transaction_no",
  59.           "transaction_date": "source.created_at",
  60.           "payment_channel": "field_extraction.payment_info.payment_channel",
  61.           "payment_id": "field_extraction.payment_info.payment_id",
  62.           "payment_reference_no": "field_extraction.payment_info.payment_reference",
  63.           "retailer_channel_id": "source.retailer_channel_id",
  64.           "subchannel_id": "source.subchannel_id",
  65.           "initiator_msisdn": "source.initiator_msisdn",
  66.           "customer_email": "field_extraction.customer_info.customer_email",
  67.           "target_msisdn": "field_extraction.customer_info.target_msisdn",
  68.           "campaign_id": "field_extraction.business_info.campaign_id",
  69.           "failed_reason": "field_extraction.business_info.failed_reason",
  70.           "retry_count": "field_extraction.business_info.retry_count",
  71.           "archived_data": "json_consolidation.consolidated_json",
  72.           "source_id": "source.id",
  73.           "data_integrity_hash": "SHA2(CONCAT(transaction_no, '|', created_at, '|', initiator_msisdn, '|', COALESCE(payment_data, ''), '|', is_recurring), 256)",
  74.           "transaction_chain_id": "CONCAT('CHAIN_', transaction_no)"
  75.         }
  76.       },
  77.       "progress_tracking": {
  78.         "sql": "INSERT INTO bl_ria_archive.archival_progress (batch_id, transaction_count, success_count, error_count, start_time, end_time, status, additional_info) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
  79.         "columns": [
  80.           {
  81.             "id": "batch_id",
  82.             "title": "Batch ID"
  83.           },
  84.           {
  85.             "id": "transaction_count",
  86.             "title": "Transaction Count"
  87.           },
  88.           {
  89.             "id": "success_count",
  90.             "title": "Success Count"
  91.           },
  92.           {
  93.             "id": "error_count",
  94.             "title": "Error Count"
  95.           },
  96.           {
  97.             "id": "start_time",
  98.             "title": "Start Time"
  99.           },
  100.           {
  101.             "id": "end_time",
  102.             "title": "End Time"
  103.           },
  104.           {
  105.             "id": "status",
  106.             "title": "Status"
  107.           },
  108.           {
  109.             "id": "additional_info",
  110.             "title": "Additional Info"
  111.           }
  112.         ]
  113.       }
  114.     },
  115.     "execution_config": {
  116.       "batch_processing": {
  117.         "batchSize": 100,
  118.         "chunkSize": "100",
  119.         "maxRetries": 3,
  120.         "retryDelay": 5000,
  121.         "transactionTimeout": 300000
  122.       },
  123.       "validation": {
  124.         "pre_archival": "SELECT COUNT(*) FROM bl_ria_api.init_operations WHERE transaction_no IN (?)",
  125.         "post_archival": "SELECT COUNT(*) FROM bl_ria_archive.archived_operations WHERE transaction_no IN (?)",
  126.         "integrity_check": "SELECT COUNT(*) FROM bl_ria_archive.archived_operations WHERE transaction_no IN (?) AND JSON_VALID(archived_data) = 1"
  127.       }
  128.     },
  129.     "output": {
  130.       "outputDir": "/usr/src/app/output/archive",
  131.       "outputFilePathPrefix": "archival_operations_",
  132.       "outputFileExtension": "log",
  133.       "delimiter": "|"
  134.     }
  135.   }
  136. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!