Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Split json array in hive

Split json array in hive

Explorer

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

Re: Split json array in hive

New Contributor

Hi Snadeep,

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