I have a hive table with a column actions that is a mix of has json array and elements. Sample data looks as follows - [{"type":"FinancialTransferAction","value":"success"},{"EventPublisherData":{"financialTransId":"VSxxx","debitTransactionId":"xxx6"}}] [{"type":"SendFundsAction","value":"success"},{"EventPublisherData":{"financialTransId":"VSAxxx","debitTransactionId":"xxx6"}}] {"type":"CreateAccountAction","value":"failure"} {"type":"CreateAccountAction","value":"failure"} {"type":"PaymentInstrumentAction","value":"cvvFailure"} {"type":"PaymentInstrumentAction","value":"cvvFailure"} {"type":"AuthenticationAction","value":"success"} {"type":"VerificationAction","value":"success"} [{"type":"FinancialTransferAction","value":"success"},{"EventPublisherData":{"financialTransId":"VSxxxx","debitTransactionId":"7xxxxx"}}] [{"type":"SendFundsAction","value":"success"},{"EventPublisherData":{"financialTransId":"VSxxxxx","debitTransactionId":"xxxxx1"}}] I am looking for a way to flatten out the json and have a view with columns for type, value, financialTransId and creditTransactionId with corresponding values. I tried following query - select v1.type,v1.value,v2.creditTransactionId,v2.financialTransId from nudetect lateral view json_tuple(nudetect.actions,'type','value','EventPublisherData') v1 as type,value,EventPublisherData lateral view json_tuple(v1.EventPublisherData,'creditTransactionId','financialTransId') v2 as creditTransactionId,financialTransId where component='nudetect_push' and length(actions)>2 limit 4 It returns NULL for financialTransId and creditTransactionId . Any help is appreciated.
... View more
