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:
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.
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:
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
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'.
Last updated
