I need help to convert json record into field

[joefrancois]

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

[vboppudi]

HI,

The provided file is not valid json format.

If possible please provide valid source jSon file.

Regards,

[joefrancois]

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

[Anonymous]

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)

[joefrancois]

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

[joefrancois]

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
Screenshot2018-04-2320.09.50a.png
Screenshot2018-04-2320.07.06.png

[vboppudi]

Hi Joseph,

Please try below

Regards,

[joefrancois]

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

[vboppudi · Accepted solution]

Hi,

are you looking for  the following output?

Regards,

[joefrancois]

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