Parsing nested json array

[Anonymous]

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 

ID	eventType	recievedDate
1	Pen	2018-07-10
1	Rec	2018-07-10
2	Pen	2018-07-10
2	Rec	2018-07-10

Please help !

Thanks,

Vin

[iamabhishek]

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

Job Layout - 

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".

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

[iamabhishek]

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.

[Anonymous]

Hi,

I have a similar problem.

I need to create the following csv starting from the json below

expected result

{
	"data": [{
			"id": 1,
			"data": {
				"Tid": 1,
				"derived.security.Coupon.notional": 10,
				"derived.security.Coupon.paymentAmount": {
					"rows": [{
							"paymentAmount": 1,
							"periodNum": 1
						}, {
							"paymentAmount": 2,
							"periodNum": 2
						}
					]
				},
				"derived.security.Coupon.paymentRate": {
					"rows": [{
							"paymentRate": 0.5,
							"periodNum": 1
						}, {
							"paymentRate": 0.25,
							"periodNum": 2
						}
					]
				},
				"derived.security.Coupon.schedule.scheduleType": "A",
				"derived.security.Coupon.schedule": {
					"rows": [{
							"accrualEndDate": "31/12/2021",
							"periodNum": 1
						}, {
							"accrualEndDate": "31/12/2022",
							"periodNum": 2
						}
					]
				},
				"security": {
					"rows": [{
							"name": "APPLE"
						}, {
							"name": "PINTEREST"
						}, {
							"name": "GOOGLE"
						}
					]
				}
			}
		}, {
			"id": 3,
			"data": {
				"Tid": 3,
				"derived.security.Coupon.notional": 40,
				"derived.security.Coupon.paymentRate": {
					"rows": [{
							"paymentRate": 2,
							"periodNum": 1
						}, {
							"paymentRate": 1.5,
							"periodNum": 2
						}
					]
				},
				"derived.security.Coupon.schedule.scheduleType": "C",
				"derived.security.Coupon.schedule": {
					"rows": [{
							"accrualEndDate": "31/03/2021",
							"periodNum": 1
						}, {
							"accrualEndDate": "31/03/2022",
							"periodNum": 2
						}
					]
				}
			}
		}, {
			"id": 4,
			"data": {
				"Tid": 4,
				"derived.security.Coupon.notional": 20,
				"derived.security.Coupon.paymentAmount": {
					"rows": [{
							"paymentAmount": 2,
							"periodNum": 1
						}, {
							"paymentAmount": 4,
							"periodNum": 2
						}
					]
				},
				"derived.security.Coupon.paymentRate": {
					"rows": [{
							"paymentRate": 2,
							"periodNum": 1
						}, {
							"paymentRate": 1.5,
							"periodNum": 2
						}
					]
				},
				"derived.security.Coupon.schedule.scheduleType": "D"
			}
		}
	]
}

Could you please help me?

Thanks

[Anonymous]

Hi,

I have a similar problem.

I need to create the following csv starting from the json below

expected result

{
	"data": [{
			"id": 1,
			"data": {
				"Tid": 1,
				"derived.security.Coupon.notional": 10,
				"derived.security.Coupon.paymentAmount": {
					"rows": [{
							"paymentAmount": 1,
							"periodNum": 1
						}, {
							"paymentAmount": 2,
							"periodNum": 2
						}
					]
				},
				"derived.security.Coupon.paymentRate": {
					"rows": [{
							"paymentRate": 0.5,
							"periodNum": 1
						}, {
							"paymentRate": 0.25,
							"periodNum": 2
						}
					]
				},
				"derived.security.Coupon.schedule.scheduleType": "A",
				"derived.security.Coupon.schedule": {
					"rows": [{
							"accrualEndDate": "31/12/2021",
							"periodNum": 1
						}, {
							"accrualEndDate": "31/12/2022",
							"periodNum": 2
						}
					]
				},
				"security": {
					"rows": [{
							"name": "APPLE"
						}, {
							"name": "PINTEREST"
						}, {
							"name": "GOOGLE"
						}
					]
				}
			}
		}, {
			"id": 3,
			"data": {
				"Tid": 3,
				"derived.security.Coupon.notional": 40,
				"derived.security.Coupon.paymentRate": {
					"rows": [{
							"paymentRate": 2,
							"periodNum": 1
						}, {
							"paymentRate": 1.5,
							"periodNum": 2
						}
					]
				},
				"derived.security.Coupon.schedule.scheduleType": "C",
				"derived.security.Coupon.schedule": {
					"rows": [{
							"accrualEndDate": "31/03/2021",
							"periodNum": 1
						}, {
							"accrualEndDate": "31/03/2022",
							"periodNum": 2
						}
					]
				}
			}
		}, {
			"id": 4,
			"data": {
				"Tid": 4,
				"derived.security.Coupon.notional": 20,
				"derived.security.Coupon.paymentAmount": {
					"rows": [{
							"paymentAmount": 2,
							"periodNum": 1
						}, {
							"paymentAmount": 4,
							"periodNum": 2
						}
					]
				},
				"derived.security.Coupon.paymentRate": {
					"rows": [{
							"paymentRate": 2,
							"periodNum": 1
						}, {
							"paymentRate": 1.5,
							"periodNum": 2
						}
					]
				},
				"derived.security.Coupon.schedule.scheduleType": "D"
			}
		}
	]
}

Could you please help me?

Thanks