topic Re: Parsing nested json array in Talend Studio

Parsing nested json array

Anonymous — Sat, 16 Nov 2024 07:52:54 GMT

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

Re: Parsing nested json array

iamabhishek — Tue, 31 Jul 2018 10:03:54 GMT

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.

Re: Parsing nested json array

iamabhishek — Tue, 31 Jul 2018 16:05:19 GMT

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.

Re: Parsing nested json array

Anonymous — Wed, 01 Jul 2020 20:53:19 GMT

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

Re: Parsing nested json array

Anonymous — Wed, 01 Jul 2020 20:53:19 GMT

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