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.