topic Re: I need help to convert json record into field in Talend Studio

I need help to convert json record into field

joefrancois — Sat, 16 Nov 2024 08:22:00 GMT

Hello,

I am trying to extract multiple row from a JSON tRESTClient input with some static data inject in the row creation. My JSON input look like so:

[
{
"OrderID": 6675,
"FirstName": "John",
"LastName": "Doe",

"Address": "123 spring st",

"OrderItemList": [
{
"ItemID": "736940759",
"ItemQty": 1,
"ItemDesc": "Widget One",
"ItemUnitPrice": 68.99,

},
{
"ItemID": "736940858",
"ItemQty": 1,
"ItemDesc": "Widget Two",

"ItemUnitPrice": 37.99,

}
],
"PromotionList": [
{
"PromotionName": "Code",
"Coupon": "Code",
"DiscountAmount": 9.6
}
]

I am trying to get the following out put

OrderID	FirstName	LastName	Address	Coupon	ItemID	ItemQty	ItemDesc	ItemUnitPrice
6675	John	Doe	123 spring st	Code	736940759	1	Fine Product	68.99
6675	John	Doe	123 spring st	Code	736940858	1	Good Product	37.99
6675	John	Doe	123 spring st	Code	77777	1	Discount	-9.6

I have been pulling my hair out, any help would be greatly appreciated.

Regards,

Joseph

Re: I need help to convert json record into field

vboppudi — Sun, 22 Apr 2018 23:43:52 GMT

HI,

The provided file is not valid json format.

If possible please provide valid source jSon file.

Regards,

Re: I need help to convert json record into field

joefrancois — Mon, 23 Apr 2018 16:33:42 GMT

Hello,

Please find attached the json file. I only modified the data to remove customer data, I replaced it with generic Jane Doe data.

Regards,

joseph

example-1.txt

Re: I need help to convert json record into field

Anonymous — Mon, 23 Apr 2018 18:54:43 GMT

Use tExtractJSONFields to accomplish this. See screenshots below. I typically use a combination of 1 or more components in the event of nested JSON (Coupon/Item details in your example)

Re: I need help to convert json record into field

joefrancois — Mon, 23 Apr 2018 19:54:38 GMT

Thanks evansdar,

What I am trying to accomplish is to have a separate line for the discount. I am able to have a line per item ordered, please see the attachment.If a customer order 2 items with a discount code, the script should generate 3 lines (one for each items plus the discount line.).

I just not fluent enough with JSON to make it happen.

Regards,

Joseph

Screenshot2018-04-2320.09.50a.png
Screenshot2018-04-2320.07.06.png

Re: I need help to convert json record into field

joefrancois — Mon, 23 Apr 2018 19:54:45 GMT

Thanks evansdar,

I just not fluent enough with JSON to make it happen.

Regards,

Joseph

Screenshot2018-04-2320.09.50a.png
Screenshot2018-04-2320.07.06.png
Screenshot2018-04-2320.09.50a.png
Screenshot2018-04-2320.07.06.png

Re: I need help to convert json record into field

vboppudi — Mon, 23 Apr 2018 21:43:57 GMT

Hi Joseph,

Please try below

Regards,

Re: I need help to convert json record into field

joefrancois — Mon, 23 Apr 2018 22:37:02 GMT

Hello boppudi,

Thank you for your suggestion.

I get this far, but I need to be able to extract the discount to another line with some static field. As shown on the original post. Think of the discount as a 3 product with a negative amount.

Regards,

Joseph

Re: I need help to convert json record into field

vboppudi — Tue, 24 Apr 2018 16:35:27 GMT

Hi,

are you looking for the following output?

Regards,

Re: I need help to convert json record into field

joefrancois — Tue, 24 Apr 2018 17:11:32 GMT

Hello Boppudi,

Yep this is exactly the output I am looking for. I can take this output and write it to the DB. How can I replicate what you have done?

Regards,

Joseph

Re: I need help to convert json record into field

vboppudi — Tue, 24 Apr 2018 17:21:23 GMT

Hi,

Please read json file as input like below

Then use tMap to split records into two pipelines like below

Then use tAggregate to get unique order for each coupon.

Then load aggregate and tMap output to tBufferout.

Use tBufferinput to read two sets of data and load into table/File.

Regards,

Re: I need help to convert json record into field

joefrancois — Thu, 03 May 2018 21:03:36 GMT

Thank you so much for the solution. I am new to Talend and would have never been able to figure this out on my own. I still however have one problem. I can't seem to figure out how to transform the DiscountAmount into a negative number. I keep on getting a compiling error "Detail Message: Type mismatch: cannot convert from Double to String". Any tip on how to resolve it?

Thank you again and regards,

Joseph