Store json response as data table

[Asanka]

Hello,

I’m using the Bank of Canadas API service to grab all major exchange rates. I’m able to get the response i want but I’m not sure how to take the info and store it in a data table. As an example I would want a data table with the following two columns (used USD as example but would want them all):
Currency Pair Rate
USDCAD 1.2523

Here is the endpoint and json response(actual observations at the bottom):
endpoint: https://www.bankofcanada.ca/valet/observations/group/FX_RATES_DAILY/json?recent=1

Response:

{
“groupDetail”: {
“label”: “Daily exchange rates”,
“description”: “Daily average exchange rates - published once each business day by 16:30 ET. All Bank of Canada exchange rates are indicative rates only.”,
“link”: null
},
“terms”: {
“url”: “Terms of Use and Disclaimers - Bank of Canada”
},
“seriesDetail”: {
“FXAUDCAD”: {
“label”: “AUD/CAD”,
“description”: “Australian dollar to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXBRLCAD”: {
“label”: “BRL/CAD”,
“description”: “Brazilian real to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXCNYCAD”: {
“label”: “CNY/CAD”,
“description”: “Chinese renminbi to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXEURCAD”: {
“label”: “EUR/CAD”,
“description”: “European euro to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXHKDCAD”: {
“label”: “HKD/CAD”,
“description”: “Hong Kong dollar to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXINRCAD”: {
“label”: “INR/CAD”,
“description”: “Indian rupee to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXIDRCAD”: {
“label”: “IDR/CAD”,
“description”: “Indonesian rupiah to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXJPYCAD”: {
“label”: “JPY/CAD”,
“description”: “Japanese yen to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXMYRCAD”: {
“label”: “MYR/CAD”,
“description”: “Malaysian ringgit to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXMXNCAD”: {
“label”: “MXN/CAD”,
“description”: “Mexican peso to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXNZDCAD”: {
“label”: “NZD/CAD”,
“description”: “New Zealand dollar to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXNOKCAD”: {
“label”: “NOK/CAD”,
“description”: “Norwegian krone to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXPENCAD”: {
“label”: “PEN/CAD”,
“description”: “Peruvian new sol to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXRUBCAD”: {
“label”: “RUB/CAD”,
“description”: “Russian ruble to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXSARCAD”: {
“label”: “SAR/CAD”,
“description”: “Saudi riyal to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXSGDCAD”: {
“label”: “SGD/CAD”,
“description”: “Singapore dollar to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXZARCAD”: {
“label”: “ZAR/CAD”,
“description”: “South African rand to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXKRWCAD”: {
“label”: “KRW/CAD”,
“description”: “South Korean won to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXSEKCAD”: {
“label”: “SEK/CAD”,
“description”: “Swedish krona to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXCHFCAD”: {
“label”: “CHF/CAD”,
“description”: “Swiss franc to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXTWDCAD”: {
“label”: “TWD/CAD”,
“description”: “Taiwanese dollar to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXTHBCAD”: {
“label”: “THB/CAD”,
“description”: “Thai baht to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXTRYCAD”: {
“label”: “TRY/CAD”,
“description”: “Turkish lira to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXGBPCAD”: {
“label”: “GBP/CAD”,
“description”: “UK pound sterling to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXUSDCAD”: {
“label”: “USD/CAD”,
“description”: “US dollar to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
},
“FXVNDCAD”: {
“label”: “VND/CAD”,
“description”: “Vietnamese dong to Canadian dollar daily exchange rate”,
“dimension”: {
“key”: “d”,
“name”: “date”
}
}
},
“observations”: [
{
“d”: “2019-12-31”,
“FXVNDCAD”: {
“v”: “0.000056”
},
“FXMYRCAD”: {
“v”: “0.3175”
},
“FXTHBCAD”: {
“v”: “0.04362”
}
},
{
“d”: “2021-04-21”,
“FXAUDCAD”: {
“v”: “0.9696”
},
“FXBRLCAD”: {
“v”: “0.2248”
},
“FXCNYCAD”: {
“v”: “0.1929”
},
“FXEURCAD”: {
“v”: “1.5056”
},
“FXHKDCAD”: {
“v”: “0.1613”
},
“FXINRCAD”: {
“v”: “0.01660”
},
“FXIDRCAD”: {
“v”: “0.000086”
},
“FXJPYCAD”: {
“v”: “0.01158”
},
“FXMXNCAD”: {
“v”: “0.06295”
},
“FXNZDCAD”: {
“v”: “0.9018”
},
“FXNOKCAD”: {
“v”: “0.1499”
},
“FXPENCAD”: {
“v”: “0.3382”
},
“FXRUBCAD”: {
“v”: “0.01634”
},
“FXSARCAD”: {
“v”: “0.3339”
},
“FXSGDCAD”: {
“v”: “0.9421”
},
“FXZARCAD”: {
“v”: “0.08785”
},
“FXKRWCAD”: {
“v”: “0.001120”
},
“FXSEKCAD”: {
“v”: “0.1486”
},
“FXCHFCAD”: {
“v”: “1.3648”
},
“FXTWDCAD”: {
“v”: “0.04457”
},
“FXTRYCAD”: {
“v”: “0.1529”
},
“FXGBPCAD”: {
“v”: “1.7438”
},
“FXUSDCAD”: {
“v”: “1.2523”
}
}
]
}

[Asanka]

Was able to grab just the observations using: currencyJsonOutputObject(“observations”).ToString

However, still can’t grab the Currency pair and rate. Please help!

[rodenweller (Richard O)]

I can’t give you all the details, but I can get you started. First of all remember that JSON is used to store nested data while a datatable is used to store flat data (rows). There is a package called JsonFlatten that can do that for you. After flattening you’ll have a dictionary (key=value pairs). Ignore any entries that aren’t of interest and add all the entries with the exchange rate to a datatable.

[Asanka]

For some reason that package won’t install properly. Any other way to do it?

[rodenweller (Richard O)]

Try adding Json.NET first as JsonFlatten has dependencies on it. Other ways would be to load it up as a regular Json object using one of the packages and parse through the tree to extract the rates.

[Asanka]

yeah how do i parse through the trees? Very new to json

[rodenweller (Richard O)]

Now you’re moving out of my area of experience. There should be plenty of samples for Json.NET in C# of VB. Json.NET is a popular toolset.

[ppr (Peter Preuss)]

@Asanka
Have a look on following flow

getting Following Result:

We can do it with a LINQ:

(From o In myJObject("observations").Cast(Of JObject)
From p In o.Properties
Where Not p.Name.Equals("d")
Let ra = New Object(){p.Name, o(p.Name)("v").Value(Of String)}
Select dtData.Rows.Add(ra)).CopyToDataTable

But we can also decompose the LINQ to essential activites and doing it e.g. by a nested for each

Find starter Help here:
GetWSResponse_LoopArray.xaml (8.8 KB)

[Asanka]

This is absolutely incredible! Thank you for taking the time to do this. May I ask one more thing? I’m very interested to learn what you have done here. If its not too much trouble, can you please walk me through the LINQ. I’m not really sure whats going on with it. As I mentioned, very new to json.

Thanks!

[ppr (Peter Preuss)]

Learn LINQ using Step-by-Step Tutorials

Introduction