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
}
}
}
When mapping this response using field mapping, 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.
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
On the create screen, scroll to the bottom and click to open the Advanced Settings.
Under Denormalize Path, enter the JSONPath to where you want to denormalize the data (for the example above, use
$.rates
).Enter a Denormalize Field Name for the field that will be added to each record (for the example above, use
Date
).
Note: These settings are also available on the Response Field Mapping.
Last updated