{
"messageBody": "{\"task\":{\"taskId\":\"c6d9fb0e-42ba-4a3e-bd39-f2a32a6958c1\",\"serializedTaskData\":\"{\\\"clientId\\\":\\\"Payables\\\",\\\"billerId\\\":\\\"UPSN\\\",\\\"paymentGroupIdentifier\\\":\\\"c6d9fb0e-42ba-4a3e-bd39-f2a32a6958c1\\\",\\\"paymentConfiguration\\\":{\\\"paytermConfiguration\\\":\\\"{\\\\\\\"properties\\\\\\\":{},\\\\\\\"canonicalName\\\\\\\":\\\\\\\"com.amazon.simplepaymentservice.entities.paymentconfiguration.PaytermConfigurationFromPayee\\\\\\\"}\\\",\\\"paytermArray\\\":[],\\\"paymentEngineConfiguration\\\":{\\\"properties\\\":{\\\"payeeSiteId\\\":{\\\"expression\\\":\\\"resolveProperty(\\\\u0027SHIPMENT_ACCOUNT_TYPE\\\\u0027)\\\",\\\"expressionType\\\":\\\"SPEL\\\",\\\"dataType\\\":\\\"LONG\\\",\\\"resolverMap\\\":{\\\"AUDIBLE\\\":\\\"28558\\\",\\\"CLOSED\\\":\\\"28558\\\",\\\"OUTBOUND-POSTAGES\\\":\\\"28558\\\",\\\"C-RETURNS-AGL NA\\\":\\\"28558\\\",\\\"ZAPPOS\\\":\\\"28558\\\",\\\"INBOUND-SP-RETAIL-OTHER\\\":\\\"28558\\\",\\\"Avalon\\\":\\\"28558\\\",\\\"INBOUND-CA\\\":\\\"714914\\\",\\\"OUTBOUND-NA\\\":\\\"28558\\\",\\\"C-RETURNS-AGL-NA\\\":\\\"28558\\\",\\\"INBOUND-SP-FBA-CA\\\":\\\"714914\\\",\\\"OUTBOUND-CA YVR2\\\":\\\"714914\\\",\\\"SUPPLIES\\\":\\\"28558\\\",\\\"C-RETURNS-CA\\\":\\\"714914\\\",\\\"IDA D AND T\\\":\\\"28558\\\",\\\"WAREHOUSE-TRANSFERS\\\":\\\"28558\\\",\\\"INBOUND-SP-NON-FBA\\\":\\\"28558\\\",\\\"Audible-NA\\\":\\\"28558\\\",\\\"ON DEMAND\\\":\\\"28558\\\",\\\"WT-SMALL-PARCEL\\\":\\\"28558\\\",\\\"CORP-CA\\\":\\\"714914\\\",\\\"IMPORTS-CA\\\":\\\"714914\\\",\\\"TFM\\\":\\\"7939814\\\",\\\"C-RETURNS\\\":\\\"28558\\\",\\\"SPECIAL_HANDLING_CR\\\":\\\"28558\\\",\\\"AWS\\\":\\\"28558\\\",\\\"OUTBOUND-CA\\\":\\\"714914\\\",\\\"TFM-C-RETURNS\\\":\\\"7939814\\\",\\\"OUTBOUND-V-RETURNS\\\":\\\"28558\\\",\\\"INBOUND-SP-FBA\\\":\\\"28558\\\",\\\"OUTBOUND UNDELIVERABLE\\\":\\\"28558\\\",\\\"CREATESPACE\\\":\\\"28558\\\",\\\"SUPPLIER-FREIGHT-US\\\":\\\"28558\\\",\\\"Dropship\\\":\\\"28558\\\",\\\"INBOUND-SP-NI\\\":\\\"28558\\\",\\\"INBOUND-SP-RENTAL\\\":\\\"28558\\\",\\\"CORP\\\":\\\"28558\\\",\\\"INBOUND-SP-NON-FBA-CA\\\":\\\"714914\\\",\\\"PRIME-NOW\\\":\\\"28558\\\",\\\"OUTBOUND-V-RETURNS-CA\\\":\\\"714914\\\",\\\"OUTBOUND-NA NVDC\\\":\\\"28558\\\"}},\\\"marketplaceId\\\":{\\\"expression\\\":\\\"resolveProperty(\\\\u0027SHIPMENT_ACCOUNT_TYPE\\\\u0027)\\\",\\\"expressionType\\\":\\\"SPEL\\\",\\\"dataType\\\":\\\"LONG\\\",\\\"resolverMap\\\":{\\\"AUDIBLE\\\":\\\"1\\\",\\\"CLOSED\\\":\\\"1\\\",\\\"OUTBOUND-POSTAGES\\\":\\\"1\\\",\\\"C-RETURNS-AGL NA\\\":\\\"1\\\",\\\"ZAPPOS\\\":\\\"1\\\",\\\"INBOUND-SP-RETAIL-OTHER\\\":\\\"1\\\",\\\"Avalon\\\":\\\"1\\\",\\\"INBOUND-CA\\\":\\\"7\\\",\\\"OUTBOUND-NA\\\":\\\"1\\\",\\\"C-RETURNS-AGL-NA\\\":\\\"1\\\",\\\"INBOUND-SP-FBA-CA\\\":\\\"7\\\",\\\"OUTBOUND-CA YVR2\\\":\\\"7\\\",\\\"SUPPLIES\\\":\\\"1\\\",\\\"C-RETURNS-CA\\\":\\\"7\\\",\\\"IDA D AND T\\\":\\\"1\\\",\\\"WAREHOUSE-TRANSFERS\\\":\\\"1\\\",\\\"INBOUND-SP-NON-FBA\\\":\\\"1\\\",\\\"Audible-NA\\\":\\\"1\\\",\\\"ON DEMAND\\\":\\\"1\\\",\\\"WT-SMALL-PARCEL\\\":\\\"1\\\",\\\"CORP-CA\\\":\\\"7\\\",\\\"IMPORTS-CA\\\":\\\"7\\\",\\\"TFM\\\":\\\"1\\\",\\\"C-RETURNS\\\":\\\"1\\\",\\\"SPECIAL_HANDLING_CR\\\":\\\"1\\\",\\\"AWS\\\":\\\"1\\\",\\\"OUTBOUND-CA\\\":\\\"7\\\",\\\"TFM-C-RETURNS\\\":\\\"1\\\",\\\"OUTBOUND-V-RETURNS\\\":\\\"1\\\",\\\"INBOUND-SP-FBA\\\":\\\"1\\\",\\\"OUTBOUND UNDELIVERABLE\\\":\\\"1\\\",\\\"CREATESPACE\\\":\\\"1\\\",\\\"SUPPLIER-FREIGHT-US\\\":\\\"1\\\",\\\"Dropship\\\":\\\"1\\\",\\\"INBOUND-SP-NI\\\":\\\"1\\\",\\\"INBOUND-SP-RENTAL\\\":\\\"1\\\",\\\"CORP\\\":\\\"1\\\",\\\"INBOUND-SP-NON-FBA-CA\\\":\\\"7\\\",\\\"PRIME-NOW\\\":\\\"1\\\",\\\"OUTBOUND-V-RETURNS-CA\\\":\\\"7\\\",\\\"OUTBOUND-NA NVDC\\\":\\\"1\\\"}},\\\"payGroup\\\":{\\\"expression\\\":\\\"resolveProperty(\\\\u0027SHIPMENT_ACCOUNT_TYPE\\\\u0027)\\\",\\\"expressionType\\\":\\\"SPEL\\\",\\\"dataType\\\":\\\"STRING\\\",\\\"resolverMap\\\":{\\\"AUDIBLE\\\":\\\"FREIGHT\\\",\\\"CLOSED\\\":\\\"FREIGHT\\\",\\\"OUTBOUND-POSTAGES\\\":\\\"FREIGHT\\\",\\\"C-RETURNS-AGL NA\\\":\\\"FREIGHT\\\",\\\"ZAPPOS\\\":\\\"FREIGHT\\\",\\\"INBOUND-SP-RETAIL-OTHER\\\":\\\"FREIGHT\\\",\\\"Avalon\\\":\\\"FREIGHT\\\",\\\"INBOUND-CA\\\":\\\"FREIGHT\\\",\\\"OUTBOUND-NA\\\":\\\"FREIGHT\\\",\\\"C-RETURNS-AGL-NA\\\":\\\"FREIGHT\\\",\\\"INBOUND-SP-FBA-CA\\\":\\\"FREIGHT\\\",\\\"OUTBOUND-CA YVR2\\\":\\\"FREIGHT\\\",\\\"SUPPLIES\\\":\\\"FREIGHT\\\",\\\"C-RETURNS-CA\\\":\\\"FREIGHT\\\",\\\"IDA D AND T\\\":\\\"FREIGHT\\\",\\\"WAREHOUSE-TRANSFERS\\\":\\\"FREIGHT\\\",\\\"INBOUND-SP-NON-FBA\\\":\\\"FREIGHT\\\",\\\"Audible-NA\\\":\\\"FREIGHT\\\",\\\"ON DEMAND\\\":\\\"FREIGHT\\\",\\\"WT-SMALL-PARCEL\\\":\\\"FREIGHT\\\",\\\"CORP-CA\\\":\\\"FREIGHT\\\",\\\"IMPORTS-CA\\\":\\\"FREIGHT\\\",\\\"TFM\\\":\\\"FREIGHT\\\",\\\"C-RETURNS\\\":\\\"FREIGHT\\\",\\\"SPECIAL_HANDLING_CR\\\":\\\"FREIGHT\\\",\\\"AWS\\\":\\\"FREIGHT\\\",\\\"OUTBOUND-CA\\\":\\\"FREIGHT\\\",\\\"TFM-C-RETURNS\\\":\\\"FREIGHT\\\",\\\"OUTBOUND-V-RETURNS\\\":\\\"FREIGHT\\\",\\\"INBOUND-SP-FBA\\\":\\\"FREIGHT\\\",\\\"OUTBOUND UNDELIVERABLE\\\":\\\"FREIGHT\\\",\\\"CREATESPACE\\\":\\\"FREIGHT\\\",\\\"SUPPLIER-FREIGHT-US\\\":\\\"FREIGHT\\\",\\\"Dropship\\\":\\\"FREIGHT\\\",\\\"INBOUND-SP-NI\\\":\\\"FREIGHT\\\",\\\"INBOUND-SP-RENTAL\\\":\\\"FREIGHT\\\",\\\"CORP\\\":\\\"FREIGHT\\\",\\\"INBOUND-SP-NON-FBA-CA\\\":\\\"FREIGHT\\\",\\\"PRIME-NOW\\\":\\\"FREIGHT\\\",\\\"OUTBOUND-V-RETURNS-CA\\\":\\\"FREIGHT\\\",\\\"OUTBOUND-NA NVDC\\\":\\\"FREIGHT\\\"}},\\\"description\\\":{\\\"expression\\\":\\\"resolveProperty(\\\\u0027SHIPMENT_ACCOUNT_TYPE\\\\u0027)\\\",\\\"expressionType\\\":\\\"SPEL\\\",\\\"dataType\\\":\\\"STRING\\\",\\\"resolverMap\\\":{\\\"AUDIBLE\\\":\\\"FREIGHT\\\",\\\"CLOSED\\\":\\\"FREIGHT\\\",\\\"OUTBOUND-POSTAGES\\\":\\\"FREIGHT\\\",\\\"C-RETURNS-AGL NA\\\":\\\"FREIGHT\\\",\\\"ZAPPOS\\\":\\\"FREIGHT\\\",\\\"INBOUND-SP-RETAIL-OTHER\\\":\\\"INBOUND\\\",\\\"Avalon\\\":\\\"FREIGHT\\\",\\\"INBOUND-CA\\\":\\\"FREIGHT\\\",\\\"OUTBOUND-NA\\\":\\\"FREIGHT\\\",\\\"C-RETURNS-AGL-NA\\\":\\\"FREIGHT\\\",\\\"INBOUND-SP-FBA-CA\\\":\\\"INBOUND\\\",\\\"OUTBOUND-CA YVR2\\\":\\\"FREIGHT\\\",\\\"SUPPLIES\\\":\\\"SUPPLIES\\\",\\\"C-RETURNS-CA\\\":\\\"FREIGHT\\\",\\\"IDA D AND T\\\":\\\"FREIGHT\\\",\\\"WAREHOUSE-TRANSFERS\\\":\\\"FREIGHT\\\",\\\"INBOUND-SP-NON-FBA\\\":\\\"INBOUND\\\",\\\"Audible-NA\\\":\\\"FREIGHT\\\",\\\"ON DEMAND\\\":\\\"FREIGHT\\\",\\\"WT-SMALL-PARCEL\\\":\\\"FREIGHT\\\",\\\"CORP-CA\\\":\\\"FREIGHT\\\",\\\"IMPORTS-CA\\\":\\\"FREIGHT\\\",\\\"TFM\\\":\\\"FREIGHT\\\",\\\"C-RETURNS\\\":\\\"FREIGHT\\\",\\\"SPECIAL_HANDLING_CR\\\":\\\"FREIGHT\\\",\\\"AWS\\\":\\\"FREIGHT\\\",\\\"OUTBOUND-CA\\\":\\\"FREIGHT\\\",\\\"TFM-C-RETURNS\\\":\\\"FREIGHT\\\",\\\"OUTBOUND-V-RETURNS\\\":\\\"FREIGHT-V-RETURNS\\\",\\\"INBOUND-SP-FBA\\\":\\\"INBOUND\\\",\\\"OUTBOUND UNDELIVERABLE\\\":\\\"FREIGHT\\\",\\\"CREATESPACE\\\":\\\"FREIGHT\\\",\\\"SUPPLIER-FREIGHT-US\\\":\\\"FREIGHT\\\",\\\"Dropship\\\":\\\"FREIGHT\\\",\\\"INBOUND-SP-NI\\\":\\\" INBOUND\\\",\\\"INBOUND-SP-RENTAL\\\":\\\"INBOUND\\\",\\\"CORP\\\":\\\"FREIGHT\\\",\\\"INBOUND-SP-NON-FBA-CA\\\":\\\"INBOUND\\\",\\\"PRIME-NOW\\\":\\\"FREIGHT\\\",\\\"OUTBOUND-V-RETURNS-CA\\\":\\\"FREIGHT\\\",\\\"OUTBOUND-NA NVDC\\\":\\\"FREIGHT\\\"}},\\\"lineDescription\\\":{\\\"expression\\\":\\\"\\\\u0027Freight\\\\u0027\\\",\\\"expressionType\\\":\\\"SPEL\\\",\\\"dataType\\\":\\\"STRING\\\",\\\"resolverMap\\\":{}},\\\"glCompanyCode\\\":{\\\"expression\\\":\\\"properties.get(\\\\u0027COMPANY_CODE\\\\u0027)\\\",\\\"expressionType\\\":\\\"SPEL\\\",\\\"dataType\\\":\\\"STRING\\\",\\\"resolverMap\\\":{}},\\\"glLocation\\\":{\\\"expression\\\":\\\"properties.get(\\\\u0027LOCATION\\\\u0027)\\\",\\\"expressionType\\\":\\\"SPEL\\\",\\\"dataType\\\":\\\"STRING\\\",\\\"resolverMap\\\":{}},\\\"glCostCenter\\\":{\\\"expression\\\":\\\"properties.get(\\\\u0027COST_CENTER\\\\u0027)\\\",\\\"expressionType\\\":\\\"SPEL\\\",\\\"dataType\\\":\\\"STRING\\\",\\\"resolverMap\\\":{}},\\\"glAccount\\\":{\\\"expression\\\":\\\"properties.get(\\\\u0027ACCOUNT\\\\u0027)\\\",\\\"expressionType\\\":\\\"SPEL\\\",\\\"dataType\\\":\\\"STRING\\\",\\\"resolverMap\\\":{}},\\\"glProductLine\\\":{\\\"expression\\\":\\\"properties.get(\\\\u0027PRODUCT_LINE\\\\u0027)\\\",\\\"expressionType\\\":\\\"SPEL\\\",\\\"dataType\\\":\\\"STRING\\\",\\\"resolverMap\\\":{}},\\\"glChannel\\\":{\\\"expression\\\":\\\"properties.get(\\\\u0027SALES_CHANNEL\\\\u0027)\\\",\\\"expressionType\\\":\\\"SPEL\\\",\\\"dataType\\\":\\\"STRING\\\",\\\"resolverMap\\\":{}},\\\"glProject\\\":{\\\"expression\\\":\\\"properties.get(\\\\u0027PROJECT\\\\u0027)\\\",\\\"expressionType\\\":\\\"SPEL\\\",\\\"dataType\\\":\\\"STRING\\\",\\\"resolverMap\\\":{}}},\\\"canonicalName\\\":\\\"com.amazon.simplepaymentservice.entities.paymentconfiguration.OfaPaymentEngineConfiguration\\\"}},\\\"paymentBatchInfo\\\":{\\\"clientId\\\":\\\"Payables\\\",\\\"batchId\\\":\\\"c6d9fb0e-42ba-4a3e-bd39-f2a32a6958c1\\\",\\\"billerId\\\":\\\"UPSN\\\",\\\"customerId\\\":\\\"AMAZON\\\",\\\"invoiceNumber\\\":\\\"000000400E3E114A\\\",\\\"invoiceVersion\\\":1},\\\"invoice\\\":{\\\"invoiceProperties\\\":{\\\"INV_BILL_NAME\\\":\\\"AMAZON.COM.KYDC INC.\\\",\\\"INV_TOTAL_LI\\\":\\\"1460\\\",\\\"INV_PLAN_NUMBER\\\":\\\"00000380DN\\\",\\\"INV_SHIP_COUNTRY_CODE\\\":\\\"US\\\",\\\"INV_METHOD_OF_PAYMENT\\\":\\\"PP\\\",\\\"STORE_MISC_AS\\\":\\\"INVOICE_SPECIFIC_UNMATCHED\\\",\\\"INVOICE_LOAD_DATE\\\":\\\"1710655409672\\\",\\\"INV_TOTAL_PKGS\\\":\\\"1460\\\",\\\"INV_ACCOUNT_NUMBER\\\":\\\"400E3E\\\",\\\"INV_TXN_COUNT\\\":\\\"1087\\\",\\\"INV_BILL_ADDRESS1\\\":\\\"333 BOREN AVE N\\\",\\\"RECORDS_PER_CONTAINER_THRESHOLD\\\":\\\"10\\\",\\\"FILE_NAME\\\":\\\"invoices/20240317/UPSN-000000400E3E114A-2024031706032872.inv.txt\\\",\\\"SHIPMENT_ACCOUNT_TYPE\\\":\\\"CORP\\\",\\\"INV_CURRENCY_CODE\\\":\\\"USD\\\",\\\"INVOICE_ID\\\":\\\"5257874370\\\",\\\"INV_BILL_COUNTRY_CODE\\\":\\\"US\\\",\\\"INV_COMPANY_CODE\\\":\\\"UNKNOWN\\\",\\\"BUSINESS_TYPE\\\":\\\"NON_INVENTORY\\\",\\\"INV_BILL_STATE\\\":\\\"WA\\\",\\\"INV_TERMS_OF_SALE\\\":\\\"01\\\",\\\"ORIGINAL_ACCOUNT_NUMBER\\\":\\\"0000400E3E\\\",\\\"INV_SCAC\\\":\\\"UPSN\\\",\\\"INV_SHIP_STATE\\\":\\\"WA\\\",\\\"INV_SHIP_NAME\\\":\\\"AMAZON.COM.KYDC INC.\\\",\\\"INV_NET_AMOUNT\\\":\\\"4333.57\\\",\\\"INV_SHIP_POSTAL_CODE\\\":\\\"981095407\\\",\\\"FINANCIAL_DOCUMENT_TYPE\\\":\\\"INVOICE\\\",\\\"INV_BILL_CITY\\\":\\\"SEATTLE\\\",\\\"HEADER_PARSING_TYPE\\\":\\\"TYPE_50\\\",\\\"INV_BILL_COMPANY\\\":\\\"BILLING DEPT\\\",\\\"INV_DATE\\\":\\\"20240316\\\",\\\"FILE_URL\\\":\\\"invoicefiles-na\\\",\\\"SERVICE_REF_ID_STRATEGY\\\":\\\"SMALL_PARCEL\\\",\\\"CONTRACT_TYPE\\\":\\\"SMALL_PARCEL\\\",\\\"INV_BILL_POSTAL_CODE\\\":\\\"981093536\\\",\\\"AMAZON_INVOICE_RECEIVED_DATE\\\":\\\"2024-03-17 00:00:00.0\\\",\\\"EVENT_ORG_ID\\\":\\\"NA\\\",\\\"INV_SHIP_ADDRESS1\\\":\\\"325 EASTLAKE AVE E\\\",\\\"INV_SHIP_CITY\\\":\\\"SEATTLE\\\",\\\"isEnabledForMatcher\\\":\\\"false\\\",\\\"INV_PARTITION_KEY\\\":\\\"2024-03-17 06:03:30.0\\\",\\\"SHIPMENT_ACCOUNT_ID\\\":\\\"340044351\\\",\\\"TRANSPORT_CARRIER_ID\\\":\\\"26991\\\",\\\"INV_NUMBER\\\":\\\"000000400E3E114A\\\",\\\"LINE_ITEM_SEQUENCE_STRATEGY\\\":\\\"ORDER_IN_FILE\\\",\\\"COUNTRY_CODE\\\":\\\"US\\\"},\\\"billerId\\\":\\\"UPSN\\\",\\\"customerId\\\":\\\"AMAZON\\\",\\\"netAmount\\\":4333.57,\\\"taxes\\\":[],\\\"invoiceDate\\\":{\\\"seconds\\\":1710547200,\\\"nanos\\\":0},\\\"invoiceDueDate\\\":{\\\"seconds\\\":1710547200,\\\"nanos\\\":0},\\\"invoiceNumber\\\":\\\"000000400E3E114A\\\",\\\"currency\\\":{\\\"amount\\\":4333.57,\\\"name\\\":\\\"USD\\\"},\\\"validTransactionCount\\\":3407,\\\"isDeleted\\\":false,\\\"loadDate\\\":{\\\"seconds\\\":1710655410,\\\"nanos\\\":896000000},\\\"invoiceVersion\\\":1,\\\"invoiceDocumentIdentification\\\":{\\\"documentTypeInfo\\\":{\\\"documentType\\\":\\\"INVOICE\\\",\\\"documentSubType\\\":\\\"STANDARD\\\"}}}}\"},\"checkpoint\":{\"checkpointData\":\"{\\\"payeeSiteId\\\":28558,\\\"glAggregates\\\":[[{\\\"paymentLineItemType\\\":\\\"TRANSACTION\\\",\\\"glCoding\\\":{\\\"companyCode\\\":\\\"2D\\\",\\\"location\\\":\\\"1000\\\",\\\"costCenter\\\":\\\"8798\\\",\\\"account\\\":\\\"64757\\\",\\\"productLine\\\":\\\"0000\\\",\\\"salesChannel\\\":\\\"0001\\\",\\\"project\\\":\\\"0000\\\"}},143.38]],\\\"aggregatedPaymentGLDetails\\\":[{\\\"glCoding\\\":{\\\"companyCode\\\":\\\"2D\\\",\\\"location\\\":\\\"1000\\\",\\\"costCenter\\\":\\\"8798\\\",\\\"account\\\":\\\"64757\\\",\\\"productLine\\\":\\\"0000\\\",\\\"salesChannel\\\":\\\"0001\\\",\\\"project\\\":\\\"0000\\\"},\\\"amount\\\":143.38,\\\"paymentTaxGLDetails\\\":[]}],\\\"aggregatedTransactionCount\\\":10}\",\"isComplete\":true}}",
"stackTrace": "",
"tags": "",
"id": "",
"value": 0,
"errorType": "UNKNOWN"
}
I have a nested json like above. The nested json inside messageBody has backslashes. I want to flatten the above json till the last level of json. While i convert the above to dataframe I can only convert it into 6 columns as given above. The messagebody column contains the json as string format. I need help flattening the messageBody json to the last level. For example: messageBody.task.taskId should be one column in dataframe,messageBody.task. serializedTaskData.clientId should be another column in dataframe,messageBody.task. serializedTaskData.billerId should be another column and so on. The schema after i read the above json and convert it to dataframe is
root
|-- messageBody: string (nullable = true)
|-- stackTrace: string (nullable = true)
|-- tags: string (nullable = true)
|-- id: string (nullable = true)
|-- value: integer (nullable = true)
|-- errorType: string (nullable = true)
Since the messageBody is StringType, I am having difficulty in accessing the inner values of json and flattening them. Thanks.
