Unwrapping a nested array with variable number of key:values

Describe the issue/error/question

I’m an n8n / JavaScript newbie.
My use case is extracting historical data from my CRM API to build my historical pipeline, taking into account all historical dealstage changes.
After making an API call, I have managed to format the data to return an object where each deal is an item with the following format: [json:{dealname:“Customer1”,dealstagehistory:{{dealstage1:“Stagevalue1”,timestamp1:“Number1”},{dealstage2:“Stagevalue2”,timestamp2:"Number2},…}].

Of course, depending on the deal, there is a variable number of dealstage/timestamp couples (from one to a dozen); as you can see, they are wrapped inside an array.

My goal is to transform this output in a simple array where each deal is a “flat” item with complete information of the dealstage history (instead of them being nested inside an array). Basically building a flat file!
I think it should be doable with the .map function, but I’m lost as I need to iterate over a variable number of key:values couples :frowning:

Please share the workflow

See below with example data from the HubSpot API, but their demo deals do not have several dealstage/timestamp couples each so it doesn’t fit my real data. Once gain, I must stress that there can be any number of dealstage/timestamp couples for a given deal.

{
  "nodes": [
    {
      "parameters": {
        "url": "=https://api.hubapi.com/deals/v1/deal/paged?hapikey=demo&&properties=dealname&propertiesWithHistory=dealstage&properties=amount&limit=15",
        "options": {
          "splitIntoItems": true
        },
        "headerParametersUi": {
          "parameter": []
        },
        "queryParametersUi": {
          "parameter": []
        }
      },
      "name": "Get All Hubspot Deals with History bis",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        400,
        600
      ]
    },
    {
      "parameters": {
        "functionCode": "var newArr = []\nvar stream = items[0].json.deals\nfor(let i=0; i < stream.length; i++){\n  var newArr2 = []\n  var dealstageh=stream[i].properties.dealstage.versions\n  for(let j=0;j < dealstageh.length;j++){\n    var dealstage = \"dealstage\".concat(j)\n    var timestamp = \"timestamp\".concat(j)\n    newArr2.push({[dealstage]:dealstageh[j].value,[timestamp]:dealstageh[j].timestamp})\n    }\n  newArr3=newArr2.slice()\n  newArr.push({json:{dealname:stream[i].properties.dealname.value,newArr3}})\n}\nreturn newArr;\n"
      },
      "name": "Flattening dealstage",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        700,
        400
      ]
    }
  ],
  "connections": {
    "Get All Hubspot Deals with History bis": {
      "main": [
        [
          {
            "node": "Flattening dealstage",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Share the output returned by the last node

Expected output is to have [json:{dealname:"Customer",dealstage1:"Stagevalue1",timestamp1:"Number1",dealstage2:"Stagevalue2:",timestamp2:"Number2",...}] where, if a deal has no dealstages/timestamps past a certain number n, the values are empty strings for dealstage[N]/timestamp[N] where N>n.

Here is a limited workflow with demo data from the HubSpot API I’m using.
Please note that the dealstage history is limited to one item but once again, I must stress that there can be any number of dealstage/timestamp couples for a given deal (e.g. 2 couples for example deal 1, 9 couples for example deal 2,…)

{
  "nodes": [
    {
      "parameters": {
        "url": "=https://api.hubapi.com/deals/v1/deal/paged?hapikey=demo&&properties=dealname&propertiesWithHistory=dealstage&properties=amount&limit=15",
        "options": {
          "splitIntoItems": true
        },
        "headerParametersUi": {
          "parameter": []
        },
        "queryParametersUi": {
          "parameter": []
        }
      },
      "name": "Get All Hubspot Deals with History bis",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        400,
        600
      ]
    },
    {
      "parameters": {
        "functionCode": "var newArr = []\nvar stream = items[0].json.deals\nfor(let i=0; i < stream.length; i++){\n  var newArr2 = []\n  var dealstageh=stream[i].properties.dealstage.versions\n  for(let j=0;j < dealstageh.length;j++){\n    var dealstage = \"dealstage\".concat(j)\n    var timestamp = \"timestamp\".concat(j)\n    newArr2.push({[dealstage]:dealstageh[j].value,[timestamp]:dealstageh[j].timestamp})\n    }\n  newArr3=newArr2.slice()\n  newArr.push({json:{dealname:stream[i].properties.dealname.value,newArr3}})\n}\nreturn newArr;\n"
      },
      "name": "Flattening dealstage",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        700,
        400
      ]
    }
  ],
  "connections": {
    "Get All Hubspot Deals with History bis": {
      "main": [
        [
          {
            "node": "Flattening dealstage",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Hi @AlexPerrin, I am trying to wrap my head around this, but the example data you have provided doesn’t seem to be valid JSON, so it’s hard to understand the data structure you are working with.

Is there are a reason you’re not using the existing Hubspot node to obtain the information on deal stages? When using this node, moving every deal stage into its own field would be quite simple using the Function Item node like so:

Example Workflow
{
  "nodes": [
    {
      "parameters": {
        "functionCode": "item.Stages.forEach(function (stage, i) {\n  item[`dealstage${i + 1}`] = stage.value;\n  item[`timestamp${i + 1}`] = stage.timestamp;\n});\ndelete item.Stages;\nreturn item;"
      },
      "name": "FunctionItem",
      "type": "n8n-nodes-base.functionItem",
      "typeVersion": 1,
      "position": [
        880,
        460
      ]
    },
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        240,
        300
      ]
    },
    {
      "parameters": {
        "operation": "getAll",
        "filters": {
          "properties": [
            "dealname",
            "amount"
          ],
          "propertiesWithHistory": [
            "dealstage"
          ]
        }
      },
      "name": "Hubspot",
      "type": "n8n-nodes-base.hubspot",
      "typeVersion": 1,
      "position": [
        440,
        460
      ],
      "credentials": {
        "hubspotApi": {
          "id": "20",
          "name": "Hubspot account"
        }
      }
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "Name",
              "value": "={{$json[\"properties\"][\"dealname\"][\"value\"]}}"
            },
            {
              "name": "Stages",
              "value": "={{$json[\"properties\"][\"dealstage\"][\"versions\"]}}"
            },
            {
              "name": "Amount",
              "value": "={{$json[\"properties\"][\"amount\"][\"value\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        660,
        460
      ]
    },
    {
      "parameters": {
        "functionCode": "item.Stages.forEach(function (stage, i) {\n  item[`dealstage${i + 1}`] = stage.value;\n  item[`timestamp${i + 1}`] = stage.timestamp;\n});\ndelete item.Stages;\nreturn item;"
      },
      "name": "FunctionItem",
      "type": "n8n-nodes-base.functionItem",
      "typeVersion": 1,
      "position": [
        880,
        460
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Hubspot",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Hubspot": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "FunctionItem",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

In this example flow I am first using the Hubspot node to fetch Deals with the Deal Name, Deal Amount and Deal Stage properties. I am then using a Set node to simplify the data I’m working with. Lastly, the Function Item node runs this little snippet here to create the individual fields and then delete the original stages field which is no longer needed:

item.Stages.forEach(function (stage, i) {
  item[`dealstage${i + 1}`] = stage.value;
  item[`timestamp${i + 1}`] = stage.timestamp;
});
delete item.Stages;
return item;

Does this help you? If not, could you share the example data returned by your HTTP Request node?

Hi @MutedJam
I feel extremely dumb: I had discussed with a colleague who’s already using N8N to query HubSpot data and he told me that the “propertywithhistory” function was specific to an HTTP request and not native to the HubSpot node and I trusted his word.
It doesn’t change much in the end but makes the formatting of the API request much more intuitive. Thanks!

As for the rest of the workflow, it does exactly what I need. I couldn’t manage to use a “Set” node on the HTTP request because of its output format: the deals array was nested inside another with offset/hasMore values (which I understand to be deciphered natively by the HubSpot node) hence my admittedly complicated Function node in the middle. Maybe I could have used Set with cleverer thinking, though.

Anyway, your last node with FunctionItem does exactly what I was missing - unwrapping the dealstages array in separate columns.

Thanks a bunch for your help!

1 Like

Awesome, I’m glad to hear you figured it out! Thank you so much for confirming and have fun automating :slight_smile:

Hi @MutedJam,
I’m sorry for bothering you again on the same topic. I’m encountering another issue which must be very basic but that I can’t solve despite Googling around.
I tried replicating the same method as above to get the amounts history rather than the dealstage (so, putting Amount as a PropertyWithHistory instead of Property in the HubSpot node).
I then use a Set node to get the Name & Amounts properties, the latter being an array. It seems to work properly.

However, when I try to use a similar FunctionItem node to iterate on various amounts versions, I get the following error: Cannot read property 'forEach' of undefined. If I try to return item or return item.Name, it works fine, but return item.Amounts instead gets me a No Data error.

In short, it seems that my Set Amounts array is somehow not recognized as such, despite following an identical method as the dealstages array from your example (as far as I can see).

You can find below the Set & FunctionItem nodes that can be linked to the HubSpot node from your example (with Amount switched to PropertyWithHistory).

{
  "nodes": [
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "Name",
              "value": "={{$json[\"properties\"][\"dealname\"][\"value\"]}}"
            },
            {
              "name": "Amounts",
              "value": "={{$json[\"properties\"][\"amount\"][\"versions\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set amounts",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        760,
        400
      ]
    },
    {
      "parameters": {
        "functionCode": "item.Amounts.forEach(function (dealamount, i) {\n  item[`amount${i + 1}`] = dealamount.value;\n  item[`timestamp${i + 1}`] = dealamount.timestamp;\n});\ndelete item.Amounts;\nreturn item;\n"
      },
      "name": "Flatten amounts1",
      "type": "n8n-nodes-base.functionItem",
      "typeVersion": 1,
      "position": [
        1000,
        400
      ]
    }
  ],
  "connections": {
    "Set amounts": {
      "main": [
        [
          {
            "node": "Flatten amounts1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Sorry for the late reply @AlexPerrin - as mentioned on Discord I might overlook questions in topics already marked as solved. Could you share an example data set returned by your Set node? This would allow me to see why the JS snippet you have shared might not work as expected.

When testing this with the example I had provided earlier in this thread, your code works as expected:

After discussing further with @MutedJam on the n8n Discord, we found what the issue was : deals in my data did not always have an amount set in HubSpot, which meant it failed if I retrieved it.

The following adaptation to the code snippet did the trick:

if (item.Amounts) {
  item.Amounts.forEach(function (dealamount, i) {
    item[`amount${i + 1}`] = dealamount.value;
    item[`timestamp${i + 1}`] = dealamount.timestamp;
  });
} else {
  item['amount1'] = 0;
  item['timestamp1'] = 0;
}

delete item.Amounts;
return item;

Thanks again for your help !

1 Like