Four Stars

Parsing nested json array

Hello All,

 

I am trying to parse json in below format and write it into csv. 

[{
"_id": "1",
"eventHistory": [{

"eventType": "Pen",
"recievedDate": {
"$date": "2018-07-10"
}
}, {
"eventType": "Rec",
"recievedDate": {
"$date": "2018-07-10"
}

}
]
}, {
"_id": "2",
"eventHistory": [{

"eventType": "Pend",
"recievedDate": {
"$date": "2018-07-10"
}
}, {
"eventType": "Rec",
"recievedDate": {
"$date": "2018-07-10"
}

}
]

 

The output iam looking for is 

 

IDeventTyperecievedDate
1Pen2018-07-10
1Rec2018-07-10
2Pen2018-07-10
2Rec2018-07-10

 

Please help !

 

Thanks,

Vin

2 REPLIES
Nine Stars

Re: Parsing nested json array

You could achieve this by using tFileInputJSON and tExtractJSONFields to extract the json data.

Job Layout - 

tExtractJSONField-3.JPG

The tFileInputJSON is just used to get the Id and the Loop is set to the first array of "eventHistory" and the Mappings simply get the "eventType" and the "receivedDate".

 

tFileInputJSON-2.JPG

The Loop is just over the array. In the Mapping the "Id" field is just a pass-through. Leave it blank.

tExtractJSONField-4.JPG

Nine Stars

Re: Parsing nested json array

Looks like you have changed/updated the json content when compared to the original post. The logic still remains same - just replace it with the correct tag names.

------------------------------------------------------------ Please mark the post as accepted if it had helped you, kudos are also welcome.