Denormalize response

Some API endpoints will return JSON data where each value is nested under a different key, and these keys can change between responses. This can cause problems when mapping response fields to an output table.

For example, calling the exchangerate.host API between 2020-01-01 and 2020-01-03 (https://api.exchangerate.host/timeseries?start_date=2020-01-01&end_date=2020-01-07&symbols=USD) 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
    }
  }
}

When we try to map this to our base using the response field mapping, we will get a different field name for each date key under rates. e.g. rates.2020-01-01.USD and rates.2020-01-02.USD would be different fields.

Furthermore, if we were to change the start_date and end_date parameters on the request to 2021-01-01 and 2021-01-03, we would get totally new keys under rates in the JSON, which would not be mapped to any of our 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
      }
    ]
  }
}

You can see that all the dates are now under a rates.Date field and all the USD exchange rates are now under a rates.USD field. So now we have just two constant fields that we can map to two fields in our output table.

Denormalize the Response

• On the create request screen, click to open the advanced settings.
• Under Denormalise Path, enter the JSONPath to where you want to denormalize the data. e.g. for the above JSON, it is $.rates.
• Enter a Denormalize Field Name for the field that will be added to each record. e.g for the above JSON, we could enter 'Date'.