Response data transformations

When you run a request, the API response data (JSON/XML/CSV) is transformed into a tabular format (fields and records) and written to your output table. You set this mapping using the response field mapping.
There are different data transformations you can use: Single Record or Expanded. These handle nested arrays in slightly different ways.
Let's say our API returns this JSON response:
[
{
"id": "company1",
"positions": [
{
"id": "position1"
},
{
"id": "position2"
}
]
},
{
"id": "company2",
"positions": [
{
"id": "position3"
},
{
"id": "position4"
}
]
}
]

Single Record

Using the Single Record transformation, the nested position.id values are combined into one field, separated by a comma. This ensures each parent entity (a company in this example) is contained within a single Airtable record.
The above response would be turned into these fields and records:
Id Positions id
company1 position1,position2
company2 position3,position4
Note that Single Record does not mean everything in the response is put into one record. Data Fetcher looks for the first array in the response and puts each item in that array into a single record.

Expanded

Using the Expanded transformation, each nested position.id values would create a new record, duplicating the parent entity into multiple records.
The JSON response above would be turned into these fields and records:
Id Positions id
company1 position1
company1 position2
company2 position3
company2 position4

Set the data transformation mode

  • On the create request screen, click
    to open the advanced settings.
  • Under Data Transformation, select the transformation you want from the options.

Combine nested object keys

We saw in the example above that when the transformation is set to Single Record, the nested positions.id fields are all combined into one 'Positions id' single-line text field, separated by a comma:
Id Positions id
company1 position1,position2
company2 position3,position4
You can disable this so that each position.id values is given its own field, pre-fixed with a number:
The response would then be turned into these fields and records:
.id .positions.1.id .positions.2.id
company1 position1 position2
company2 position3 position4
To disable the combining of nested object keys:
  • On the create request screen, click
    to open the advanced settings.
  • Under Data Transformation, ensure the transformation is set to 'Single Record'.
  • Switch 'Combine nested object keys' off.

Combine arrays of text/numbers

Similarly, by default, arrays of text/numerical values are combined into one comma-separated value.
By default, for the following JSON response:
[
{
"id": "company1",
"categories": [
"Fintech",
"Startup"
]
},
{
"id": "company2",
"categories": [
"Healthtech",
"Public",
"Unicorn"
]
}
]
The text items in the categories arrays are combined into one field as comma-separated strings:
.id .categories
company1 Fintech,Startup
company2 Healthtech,Public,Unicorn
You disable this behaviour so that each array item is put into a new field like this:
.id .categories.1 .categories.2 .categories.3
company1 Fintech Startup
company2 Healthtech Public Unicorn
To disable this behaviour:
  • On the create request screen, click
    to open the advanced settings.
  • Under Data Transformation, ensure the transformation is set to 'Single Record'.
  • Switch 'Combine arrays of text/numbers' off.
You can also enter a custom Separator using the input. For example, if your values include commas then you might want to use the pipe (|) symbol as the separator instead.
Try the Response JMESPath feature for even more powerful transformation and filtering of the response.