Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Split json array in hive

avatar
Contributor

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.

 

1 REPLY 1

avatar
Contributor

Hi Snadeep,

Did you find solution for this,if so can you please share the query here