I need help to convert json record into field

Six Stars

I need help to convert json record into field

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

 

 

OrderIDFirstNameLastNameAddressCouponItemIDItemQtyItemDescItemUnitPrice
6675JohnDoe123 spring st Code7369407591Fine Product68.99
 6675John  Doe 123 spring st Code7369408581Good Product37.99
 6675 John Doe 123 spring stCode777771Discount -9.6

 

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

 

Regards,

Joseph


Accepted Solutions
Nine Stars

Re: I need help to convert json record into field

Hi,

 

are you looking for  the following output?

jSonOrder2.PNG

Regards,

Veeru Boppudi
Nine Stars

Re: I need help to convert json record into field

Hi,

Please read json file as input like below

jSonOrder1.PNG

Then use tMap to split records into two pipelines like below

jSonOrder3.PNG

 

Then use tAggregate to get unique order for each coupon.

jSonOrder4.PNG

 

Then load aggregate and tMap output to tBufferout.

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

jSonOrder2.PNG

 

Regards,

 

Veeru Boppudi

All Replies
Nine Stars

Re: I need help to convert json record into field

HI,

 

The provided file is not valid json format.

If possible please provide valid source jSon file.

 

Regards,

Veeru Boppudi
Six Stars

Re: I need help to convert json record into field

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

Seven Stars

Re: I need help to convert json record into field

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)

2018-04-23_13-51-48.png2018-04-23_13-52-01.png

Six Stars

Re: I need help to convert json record into field

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 

Six Stars

Re: I need help to convert json record into field

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 

Nine Stars

Re: I need help to convert json record into field

Hi Joseph,

Please try below

 

jSonOrder.PNGjSonOrder1.PNG

 

Regards,

Veeru Boppudi
Six Stars

Re: I need help to convert json record into field

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

Nine Stars

Re: I need help to convert json record into field

Hi,

 

are you looking for  the following output?

jSonOrder2.PNG

Regards,

Veeru Boppudi
Six Stars

Re: I need help to convert json record into field

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

Nine Stars

Re: I need help to convert json record into field

Hi,

Please read json file as input like below

jSonOrder1.PNG

Then use tMap to split records into two pipelines like below

jSonOrder3.PNG

 

Then use tAggregate to get unique order for each coupon.

jSonOrder4.PNG

 

Then load aggregate and tMap output to tBufferout.

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

jSonOrder2.PNG

 

Regards,

 

Veeru Boppudi
Six Stars

Re: I need help to convert json record into field

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