Reply
Highlighted
Explorer
Posts: 9
Registered: ‎07-26-2016

Split json array in hive

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.

 

Announcements