Denormalize response
Some API endpoints will return data where each value is nested under a different key, making it hard to map these fields to our output table. e.g. https://api.exchangerate.host/timeseries?start_date=2020-01-01&end_date=2020-01-07&symbols=USD gives us the following data:
1
{
2
"motd": {
3
"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.",
4
"url": "https://exchangerate.host/#/donate"
5
},
6
"success": true,
7
"timeseries": true,
8
"base": "EUR",
9
"start_date": "2020-01-01",
10
"end_date": "2020-01-07",
11
"rates": {
12
"2020-01-01": {
13
"USD": 1.1221,
14
"GBP": 0.8462
15
},
16
"2020-01-02": {
17
"USD": 1.1221,
18
"GBP": 0.8466
19
},
20
"2020-01-03": {
21
"USD": 1.1171,
22
"GBP": 0.8495
23
},
24
"2020-01-04": {
25
"USD": 1.1171,
26
"GBP": 0.8495
27
},
28
"2020-01-05": {
29
"USD": 1.1171,
30
"GBP": 0.8495
31
},
32
"2020-01-06": {
33
"USD": 1.1162,
34
"GBP": 0.8533
35
},
36
"2020-01-07": {
37
"USD": 1.1198,
38
"GBP": 0.85
39
}
40
}
41
}
42
Copied!
When we try to map this to our base, we will get a different field name for each date object under rates. e.g. rates.2020-01-01.USD and rates.2020-01-02.USD would be different fields.
What we really want is these to be different records with a Date field and a Rates USD field. We can acheive this by setting a Denormalize Path and Denormalize Field Name.

Denormalize the Response

  • On the create request screen, click
    to open the advanced settings.
  • Scroll down to Denormalise Path and 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'.
  • When we run the request, the response will be transformed to denormalize the data:
1
{
2
"motd": {
3
"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.",
4
"url": "https://exchangerate.host/#/donate"
5
},
6
"success": true,
7
"timeseries": true,
8
"base": "EUR",
9
"start_date": "2020-01-01",
10
"end_date": "2020-01-07",
11
"rates":
12
[
13
{
14
"Date": "2020-01-01",
15
"USD": 1.1221,
16
"GBP": 0.8462
17
},
18
{
19
"Date": "2020-01-02",
20
"USD": 1.1221,
21
"GBP": 0.8466
22
},
23
{
24
"Date": "2020-01-03",
25
"USD": 1.1171,
26
"GBP": 0.8495
27
},
28
{
29
"Date": "2020-01-04",
30
"USD": 1.1171,
31
"GBP": 0.8495
32
},
33
{
34
"Date": "2020-01-05",
35
"USD": 1.1171,
36
"GBP": 0.8495
37
},
38
{
39
"Date": "2020-01-06",
40
"USD": 1.1162,
41
"GBP": 0.8533
42
},
43
{
44
"Date": "2020-01-07",
45
"USD": 1.1198,
46
"GBP": 0.85
47
}
48
]
49
}
50
}
51
Copied!
Copy link