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.
