cancel
Showing results for 
Search instead for 
Did you mean: 

JSON Parser output to CSV file

victor
Deputy Chef I
Deputy Chef I

Hello there,

I am getting a response from an API then I passed it through the JSON Parser and it got something as below. You will see that I have items/product that I would like to have in separated rows in a CSV file. I tried with a FOR EACH loop using products array, but it does not iterate, the input is empty, but I am taking the datapill from the JSON parser, what am I missing?

Thanks

 

{
"document": {
"$schema": "http://json-schema.org/draft-07/schema#",
"title": "Product Claim Details",
"type": "object",
"properties": {
"products": {
"type": "array",
"items": [
{
"Vendor": "CANADA",
"BillingDate": "2022/09/02",
"InvoiceNumber": "SL1",
"productNumber": "14-1",
"description": "MUG SET",
"status": "FD",
"vendorPartNumber": "MUGCAN",
"units": 0,
"claimAmounts": 0,
"initialBillingAvailable": 0.14,
"shortfallSurplus": -0.14
},
{
"Vendor": "CANADA",
"BillingDate": "2022/09/02",
"InvoiceNumber": "SL2",
"productNumber": "14-2",
"description": "BOWL",
"status": "FD",
"vendorPartNumber": "29CN",
"units": 0,
"claimAmounts": 0,
"initialBillingAvailable": 2.25,
"shortfallSurplus": -2.25
}
]
}
},
"required": [
"products"
]
}
}

 

1 ACCEPTED SOLUTION

gary1
Executive Chef II
Executive Chef II

I took another look and this is very interesting! I can't get the example you provided to work in the JSON parser as is. 

I have to remove this line:

"required": ["products"]

I guess the one-dimensional array is throwing off the parser. 

Once this is removed, I can access the items data pill without a problem.

Give this a shot, hopefully it will work. 

View solution in original post

5 REPLIES 5

gary1
Executive Chef II
Executive Chef II

Based on the sample, product is an object and items is an array. You need to iterate on items.

 

Hello Gary,

thanks for your reply.  I agree but it doesnt show me item as datapill, only products.

 

victor_0-1707240759927.png

 

gary1
Executive Chef II
Executive Chef II

I took another look and this is very interesting! I can't get the example you provided to work in the JSON parser as is. 

I have to remove this line:

"required": ["products"]

I guess the one-dimensional array is throwing off the parser. 

Once this is removed, I can access the items data pill without a problem.

Give this a shot, hopefully it will work. 

Hello Gary,

I played with the JSON schema and finally the datapill showed for items, from there, the loop was going throughout all items and then I append the record to the CSV file inside the loop, I tried to accumulate to a list inside the loop  and outside the loop to append the list to the CSV, but it didn't work.

Thanks for your help

 

 "items": {
"type": "array",
"properties": {
"Vendor": {
"type": "string",
"description": "Vendor"
},
"BillingDate": {
"type": "string",
"description": "Billing Date"
},
"InvoiceNumber": {
"type": "string",
"description": "The unique identifier for the product."
},
"productNumber": {
"type": "string",
"description": "The unique identifier for the product."
},
"description": {
"type": "string",
"description": "The description of the product."
},
"status": {
"type": "string",
"description": "The status of the product."
},
"vendorPartNumber": {
"type": "string",
"description": "The vendor's part number for the product."
},
"units": {
"type": "integer",
"description": "The number of units claimed."
},
"claimAmounts": {
"type": "number",
"description": "The total amount claimed for the product."
},
"initialBillingAvailable": {
"type": "number",
"description": "The initial billing amount available for the product."
},
"shortfallSurplus": {
"type": "number",
"description": "The shortfall or surplus resulting from the claim."
}
},
"required": ["Vendor", "BillingDate", "InvoiceNumber", "productNumber", "description", "status", "vendorPartNumber", "units", "claimAmounts", "initialBillingAvailable", "shortfallSurplus"]