parsing salesforce report analytics api results

[sidjayawardena]

i have created a custom action in the SF connector to read a salesforce report

the report data is contained in an array called datacells inside an array called rows

i have put the response body view and the raw json below

what is the easiest way for me to parse this into a list so i can import to an invoices in an external system?

i tried creating a for...each loop on rows but workato errors out if i try to refer to data cells[x].label

i can for each on rows and then for each on data cells but then i can't tell where i am without a variable to track where i am which seems kludgy

the response body looks like this in workato

the raw json looks like this

{
"body": {
"attributes": {
"describeUrl": "/services/data/v42.0/analytics/reports/xxxx/describe",
"instancesUrl": "/services/data/v42.0/analytics/reports/xxxx/instances",
"reportId": "xxxx",
"reportName": "Invoices Import",
"type": "Report"
},
"allData": true,
"factMap": {
"T_T": {
"rows": [
{
"dataCells": [
{
"label": "Billing Company Inc.",
"value": "xxxx"
},
{
"label": "Customer Name Ltd.",
"value": "xxxx"
},
{
"label": "-",
"value": null
},
{
"label": "Customer Name Ltd.",
"value": "xxxx"
},
{
"escapedLabel": "Customer Number",
"label": "Customer Number",
"value": "Customer Number"
},

...

"dataCells": [
{
"label": "Billing Company 2 Inc.",
"value": "xxxx"
},
{
"label": "Customer Name 2 Ltd.",
"value": "xxxx"
},
{
"label": "-",
"value": null
},
{
"label": "Customer Name 2 Ltd.",
"value": "xxxx"
},
{
"escapedLabel": "Customer Number 2",
"label": "Customer Number 2",
"value": "Customer Number 2"
},

...

[beatrice-talens]

Hello, can you provide more details on your use case? Do you want to loop through a nested array? You may check this sample recipe if this is what you require. Here's the link.

[shivakumara · Accepted solution]

Hi @sidjayawardena ,
Here is my thoughts on the requirement.
1. Use Parse Json in sample document provide with above data, and for the actual document "map the actual data coming from source" 
2. Use the create list initialize or you can map directly to incoming parsed value from step_1. Please refer below images for the reference.

Thanks and Regards,
Shivakumara Avadhani