Response data transformations

When you run a request, the response data gets transformed into a tabular format with fields and records for your output table. You control this process using response field mapping and response data transformation settings.

Understanding the two transformation types

Data Fetcher offers two ways to handle nested arrays in your API responses: Single Record and Expanded. The key difference is how they treat nested data.

Let's use this sample JSON response to illustrate both approaches:

[
  {
    "id": "company1",
    "positions": [
      { "id": "position1" },
      { "id": "position2" }
    ]
  },
  {
    "id": "company2",
    "positions": [
      { "id": "position3" },
      { "id": "position4" }
    ]
  }
]

Single record transformation

Single Record keeps each parent entity in one row by combining nested values with commas. Each company stays in its own record:

Id	Positions id
company1	position1,position2
company2	position3,position4

Note: Single Record doesn't mean everything goes into one record. Data Fetcher finds the first array in your response and creates one record for each item in that array.

Expanded transformation

Expanded creates separate records for each nested item, duplicating parent data across multiple rows:

Id	Positions id
company1	position1
company1	position2
company2	position3
company2	position4

How to set the transformation mode

1. On the create screen, scroll to the bottom and click to open the Advanced Settings.

2. Under Data Transformation, select either Single Record or Expanded

Note: These settings are also available on the Response Field Mapping.

Advanced single record options

Combine nested object keys

By default, nested object values are combined into comma-separated fields. Using our example above, all positions.id values become one single-line text field called "Positions id".

You can disable this to create separate numbered fields instead:

Id	Positions 1 id	Positions 2 id
company1	position1	position2
company2	position3	position4

To disable combining nested object keys:

1. In advanced settings, ensure transformation is set to Single Record.

2. Turn off Combine nested object keys.

Combine arrays of text/numbers

Similarly, arrays containing text or numbers are combined by default. For this response:

[
  {
    "id": "company1",
    "categories": ["Fintech", "Startup"]
  },
  {
    "id": "company2", 
    "categories": ["Healthtech", "Public", "Unicorn"]
  }
]

The default output combines categories:

Id	Categories
company1	Fintech,Startup
company2	Healthtech,Public,Unicorn

You can disable this to create separate numbered fields:

To disable combining arrays:

1. In advanced settings, ensure transformation is set to Single Record.

2. Turn off Combine arrays of text/numbers.

Custom separators

Instead of commas, you can specify a custom separator for combined values. This is useful when your data contains commas and you need a different delimiter like the pipe symbol (|).

Both "Combine nested object keys" and "Combine arrays of text/numbers" use the separator you specify in the Separator input field.

Need More Control?

For even more flexible data transformation options, consider using a response JMESPath to customize exactly how your data is processed.