Looping and adding together numbers

[tkintenn]

I have an array coming from an api and inside that array is a sub array where it gives a client count for each client site. Each client can have as many of these as are necessary. Think locations. Most clients have 1, some have 2, I have one client with 5. My issue is I need to take the agent_count from each site and add them together. I’ve read the looping doc, I’ve read multiple posts on here and i’m not following what n8n needs to accomplish this. in php i create a variable and then “for each” over the array and over the sub array, doing the math in the second for each to add it all up. I need to store the result into a DB.

So for each client id, i need to loop over these and add together the agent count field getting a total for the client. The example client below should end up with 74.

I’ve tried a split out and that does give me separate objects for those sub arrays, I can’t figure out what i need to use to now add these up based on an id field or client field.

here is the json:

[
  {
    "id": 28,
    "name": "Some Company",
    "server_policy": 17,
    "workstation_policy": 16,
    "alert_template": null,
    "block_policy_inheritance": true,
    "sites": [
      {
        "id": 79,
        "name": "Anderson SC",
        "server_policy": null,
        "workstation_policy": null,
        "alert_template": null,
        "client_name": "Some Company",
        "client": 28,
        "custom_fields": [],
        "agent_count": 23,
        "block_policy_inheritance": false,
        "maintenance_mode": false,
        "failing_checks": {
          "error": false,
          "warning": true
        }
      },
      {
        "id": 78,
        "name": "Main",
        "server_policy": null,
        "workstation_policy": null,
        "alert_template": null,
        "client_name": "Some Company",
        "client": 28,
        "custom_fields": [],
        "agent_count": 51,
        "block_policy_inheritance": false,
        "maintenance_mode": false,
        "failing_checks": {
          "error": true,
          "warning": true
        }
      }
    ],
    "custom_fields": [
      {
        "id": 38,
        "field": 1,
        "client": 28,
        "value": null
      },
      {
        "id": 39,
        "field": 2,
        "client": 28,
        "value": null
      },
      {
        "id": 46,
        "field": 5,
        "client": 28,
        "value": "97"
      }
    ],
    "failing_checks": {
      "error": true,
      "warning": true
    }
  }
 
]

Please share your workflow

Share the output returned by the last node

Information on your n8n setup

• n8n version: 1.91.2
• Database (default: SQLite):SQlite
• n8n EXECUTIONS_PROCESS setting (default: own, main):Default
• Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
• Operating system: Linux

[hubschrauber]

If I were trying to solve this and already had a dependency on MySQL, I’d probably see if it was possible/practical to use a temp-table. The session management / connection pooling for MySQL in n8n might not work for this though. It’s just an idea to try.

[crisl]

You could also try to use $jmespath - Query JSON with JMESPath | n8n Docs - to extract the values of agent count and then add them together.

Something like this:

[tkintenn]

The data i’m working with is coming from an api, not mysql. the first mysql pull is simply getting a list of id’s that have a subscription and using that id in the API call. The data isn’t stored in mysql. I could use a temp table but then i might as well skip n8n and just php and cron to run the script. trying to keep all of this inside n8n.

[tkintenn]

this worked. to get what i needed i had to add fields to “include other input fields” and then I got usable data i could then update client agent counts. thank you!

[hubschrauber]

You could adapt this approach to initialize and update a variable in a loop. n8n just doesn’t make this easy because there’s already an implied loop for “items” in each node. Is there a reason you need to do things in your own looped flow-path? Do you need to put a delay between requests to avoid hitting an API rate limit or something like that?

[King_Samuel_David]

Good to know thanks

[mohamed3nan]

Thanks @crisl

This is really a good use case to understand JMESPath

[tkintenn]

I needed to add up all the agent_counts for sites, which are sub array inside the main array returned from the api, n8n didn’t make that easy and I i’m not a javascript guru. I know enough to do what i need for front end stuff, anything else i would typically handle in php bc i understand it better. Trying to use n8n solely has proven difficult for things like this because of the assumption it has looped through, but it only seems to do that for the top level array and sub arrays it’s not intuitive (or doesn’t seem to me it is) on how to interact with them in an instance like this.