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.

Convert Nested json with array into CSV into multiple records

Highlighted

Convert Nested json with array into CSV into multiple records

New Contributor

Hi,


I have a json which looks like this:

{

"name": {

"first": "Alex",

"last": "Burn",

"country": "US"

},

"subscriptions": [

{

"OFFERS": "Y",

"MAGAZINE_OFFERS": "Y"

},

{

"OFFERS": "N",

"MAGAZINE_OFFERS": "N"

}

]

}


I need to convert this into a CSV with multiple rows like:

name_first, name_last, country, subscription_offers, subscription_offers, subscription_magazine_offers

Alex, Burn, US, Y, Y

Alex, Burn, US, N, N


What should be my approach for this?


I tried 2 approaches:

1. FlattenJson: but this gives in output as subscriptions[0]_OFFES:Y, subscriptions[1]_OFFERS:N in the same json record

2. SplitJson I tried using splitJson and MergeContent but I cannot figure out how to join back to part of orginial json.


Anyhelp in this issue will be greatly appreciated.


PS: I cannot use JoltTransformation for this as incoming Json will differ each time. I can use AVRO Schema.


Thank you!

Don't have an account?
Coming from Hortonworks? Activate your account here