Second level path to many

Integrator.io
1

I have a business case to do three level if imports form a API JSON output.

{
  "page_of_records": [
    {
      "record": {
        "revenue": 20,
        "impressions": 200,
        "revenueLocal": 20,
        "billingCountry": "United States",
        "serviceCountry": [
          {
            "serviceCountry": "United States",
            "revenue": 10,
            "revenueLocal": 10,
            "impressions": 100,
            "rate": 10,
            "brand": [
              {
                "brand": "Coke",
                "revenue": 5,
                "revenueLocal": 5,
                "impressions": 50,
                "rate": 10
              },
              {
                "brand": "Pepsi",
                "revenue": 5,
                "revenueLocal": 5,
                "impressions": 50,
                "rate": 10
              }
            ]
          },
          {
            "serviceCountry": "India",
            "revenue": 10,
            "revenueLocal": 10,
            "impressions": 100,
            "rate": 10,
            "brand": [
              {
                "brand": "Coke",
                "revenue": 5,
                "revenueLocal": 5,
                "impressions": 50,
                "rate": 10
              },
              {
                "brand": "Pepsi",
                "revenue": 5,
                "revenueLocal": 5,
                "impressions": 50,
                "rate": 10
              }
            ]
          }
        ]
      }
    }
  ]
}

I'm doing a simple transformation and importing records to table1. On table 2 I need path to many on serviceContry along with table1 response id field. On the third import to table 3 I need path to many on brand *** with the response id field from previous table2 import.*** I tried this approach and I'm able to achieve till table2 import. On third import I’m not getting brand under path to many rather I'm still seeing service country.

2

You are zigging when you need to zag. You know that one-to-many thing? Yeah, that doesn’t flip back real well. For mine, I flattened the arrays into objects that I could use. It was big and messy, but instead of having multiple levels of arrays, I had numerous objects with lots of key-value pairs. Flattening the data with JavaScript made mine work.

3

What application are you importing into? One-to-many doesn't support a path that is more than 1 level deep, so like @toddhill447 suggested, you'll need to transform the data to bring nested items more than 1 level deep up into a 1st level array.

Alternatively, you can use things like an 'each' statement in your SQL insert to provide multiple rows at once, or use a batch insert with a mapper where your top-level mapping can point to nested arrays more than 1 level deep. These two options maybe won't work though since you're needing a single id in the response.

4

What Tyler said. Flatten that puppy and you can do anything you want.

5

This is a known limitation of how One-to-many works in integrator.io.

One-to-many mappings only operate on root-level arrays. As soon as the source data contains arrays nested more than one level deep (for example: array → object → array → object), One-to-many can no longer traverse into the deeper level. It does not re-scope the data context or move back up the hierarchy.

In practice, scenarios such as:

  • Grouped exports
  • Lookups that return arrays

quickly result in nested array structures, which One-to-many cannot process correctly in Celigo.

The common workaround is to reshape the data in the source (first) step of the flow by flattening nested arrays into root-level records.

If the required data is only available through later lookups and multiple import steps need to act on that nested array, an alternative approach is to use virtual exports in the first step (for example, querying NetSuite directly). The virtual export retrieves all required records upfront, flattens the nested arrays, and returns them at the root level.

Once the data is flat and each logical child record exists as its own root-level record, you can apply filters in the import or update steps and reliably loop over multiple records to insert or update related data.

This pattern ensures consistent processing despite the One-to-many limitations and avoids unreliable behavior when working with nested arrays.