Data Fetcher Help Center
🚀 Try for free📮 Support
  • 👋Welcome
  • Create Requests
    • Create a request
    • Use table values in requests
    • Use variables in requests
    • Response field mapping
    • Schedule a request
    • Add a request trigger
    • Troubleshoot errors
    • Run request using webhook URL
    • List of Airtable integrations
  • Custom Requests
    • Create a custom request
    • Set request method
    • Set request parameters
    • Request authorization
    • Set request headers
    • Add request body
    • Connect to a preset OAuth API
    • Create a custom OAuth connection
  • Advanced settings
    • Write modes
    • Update based on field(s)
    • Pagination
    • Response data transformations
    • Response JMESPath
    • Add fixed value to output
    • No output mode
    • Rate limit
    • Max response records
    • Run request from button field
    • Denormalize response
    • XML array handling
    • Fixed IP Address
  • Organise Requests
    • Run history
    • Duplicate a request
    • Delete a request
    • Add request description
    • Import request from cURL command
    • Export requests as JSON file
    • Import requests from JSON file
  • Create Sequences
    • Create a sequence
    • Schedule a sequence
    • Run sequence from button field
    • Run sequence using webhook URL
  • Account
    • Upgrade workspace
    • Authorize Data Fetcher and Airtable
    • Add user to workspace
    • Roles and permissions
    • View monthly usage
    • Update name or email address
    • Update payment card
    • Update billing information
    • View invoices
    • Switch workspace
  • Get a Google Maps API key
Powered by GitBook
On this page
  • The problem
  • The solution
  • How to denormalize the response
  1. Advanced settings

Denormalize response

Some API endpoints return JSON data where each value is nested under different keys that change between responses. This creates problems when mapping response fields to an output table.

The problem

For example, calling the exchangerate.host API between 2020-01-01 and 2020-01-03 gives us this JSON:

{
  "motd": {
    "msg": "If you or your company use this project or like what we doing, please consider backing us so we can continue maintaining and evolving this project.",
    "url": "https://exchangerate.host/#/donate"
  },
  "success": true,
  "timeseries": true,
  "base": "EUR",
  "start_date": "2020-01-01",
  "end_date": "2020-01-07",
  "rates": {
    "2020-01-01": {
      "USD": 1.1221,
      "GBP": 0.8462
    },
    "2020-01-02": {
      "USD": 1.1221,
      "GBP": 0.8466
    },
    "2020-01-03": {
      "USD": 1.1171,
      "GBP": 0.8495
    }
  }
}

If we change the request parameters to use different dates (like 2021-01-01 to 2021-01-03), we get completely new keys under rates that won't map to any existing fields:

{
   "motd":{
      "msg":"If you or your company use this project or like what we doing, please consider backing us so we can continue maintaining and evolving this project.",
      "url":"https://exchangerate.host/#/donate"
   },
   "success":true,
   "timeseries":true,
   "base":"EUR",
   "start_date":"2021-01-01",
   "end_date":"2021-01-03",
   "rates":{
      "2021-01-01":{
         "USD":1.217582
      },
      "2021-01-02":{
         "USD":1.2135
      },
      "2021-01-03":{
         "USD":1.223409
      }
   }
}

What we really want is just two fields, a Date field and a Rates USD field, and the different dates to be added as different records with these two fields.

We can do this by denormalizing the response. When we denormalize the response and run the request, the response will be transformed into:

{
  "motd": {
    "msg": "If you or your company use this project or like what we doing, please consider backing us so we can continue maintaining and evolving this project.",
    "url": "https://exchangerate.host/#/donate"
  },
  "success": true,
  "timeseries": true,
  "base": "EUR",
  "start_date": "2020-01-01",
  "end_date": "2020-01-07",
  "rates": 
    [
      {
        "Date": "2020-01-01",
        "USD": 1.1221,
        "GBP": 0.8462
      },
      {
        "Date": "2020-01-02",
        "USD": 1.1221,
        "GBP": 0.8466
      },
      {
        "Date": "2020-01-03",
        "USD": 1.1171,
        "GBP": 0.8495
      }
    ]
  }
}

The solution

What we really want is just two consistent fields: a Date field and a USD field. We want different dates to appear as separate records with these same two fields.

Denormalizing the response transforms the data structure. After denormalization, the response becomes:

{
  "motd": {
    "msg": "If you or your company use this project or like what we doing, please consider backing us so we can continue maintaining and evolving this project.",
    "url": "https://exchangerate.host/#/donate"
  },
  "success": true,
  "timeseries": true,
  "base": "EUR",
  "start_date": "2020-01-01",
  "end_date": "2020-01-07",
  "rates": [
    {
      "Date": "2020-01-01",
      "USD": 1.1221,
      "GBP": 0.8462
    },
    {
      "Date": "2020-01-02",
      "USD": 1.1221,
      "GBP": 0.8466
    },
    {
      "Date": "2020-01-03",
      "USD": 1.1171,
      "GBP": 0.8495
    }
  ]
}

Now all dates appear under a consistent rates.Date field and all USD exchange rates appear under a consistent rates.USD field. This gives us stable field names that we can reliably map to our output table.

How to denormalize the response

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

  2. Under Denormalize Path, enter the JSONPath to where you want to denormalize the data (for the example above, use $.rates).

  3. Enter a Denormalize Field Name for the field that will be added to each record (for the example above, use Date).

Last updated 9 days ago

When mapping this , we get a different field name for each date key under rates. For example, rates.2020-01-01.USD and rates.2020-01-02.USD would be treated as separate fields.

Note: These settings are also available on the .

response using field mapping
Response Field Mapping