Help with Json
Help with Json
(OP)
I have the following JSON output from a URL
{
"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,
"base": "AUD",
"date": "2022-12-10",
"rates": {
"AED": 2.496891,
"AFN": 59.467886,
"ALL": 74.793316,
"AMD": 268.658185,
"ANG": 1.225349,
"AOA": 342.648579,
"ARS": 115.280112,
"AUD": 1,
"AWG": 1.223333,
}
}
My code below extracts the rates information but with the three letter cod and the value into a string like:
How can I modify my vb code to get the three letter code and the value seperated into seperate columns for example to put into a database table
{
"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,
"base": "AUD",
"date": "2022-12-10",
"rates": {
"AED": 2.496891,
"AFN": 59.467886,
"ALL": 74.793316,
"AMD": 268.658185,
"ANG": 1.225349,
"AOA": 342.648579,
"ARS": 115.280112,
"AUD": 1,
"AWG": 1.223333,
}
}
My code below extracts the rates information but with the three letter cod and the value into a string like:
CODE
Dim url As String = "https://api.exchangerate.host/latest?base=AUD" Dim request As HttpWebRequest Dim WebResponse As HttpWebResponse = Nothing Dim reader As StreamReader request = DirectCast(WebRequest.Create(url), HttpWebRequest) WebResponse = DirectCast(request.GetResponse(), HttpWebResponse) reader = New StreamReader(WebResponse.GetResponseStream()) Dim RawResponse = reader.ReadToEnd() Dim json As String = RawResponse Dim jsonObject As Newtonsoft.Json.Linq.JObject = Newtonsoft.Json.Linq.JObject.Parse(json) Label1.Text = jsonObject.SelectToken("rates").ToString
CODE
{ "AED": 2.495872, "AFN": 59.443631, "ALL": 74.537735, "AMD": 269.1604, "ANG": 1.225355, "AOA": 342.552248, "ARS": 115.456501, "AUD": 1, "AWG": 1.223042, "AZN": 1.155178, "BAM": 1.260642, "BBD": 1.359196, "BDT": 70.269386, "BGN": 1.260442, "BHD": 0.256184, "BIF": 1401.68335, "BMD": 0.67943, "BND": 0.918869, "BOB": 4.698483, "BRL": 3.559331, "BSD": 0.679603, "BTC": 3.9E-05, "BTN": 55.986922, "BWP": 8.762125, "BYN": 1.717008, "BZD": 1.370884, "CAD": 0.932719, "CDF": 1397.217232, "CHF": 0.63587, "CLF": 0.021571, "CLP": 585.595319, "CNH": 4.731697, "CNY": 4.727379, "COP": 3278.8834, "CRC": 398.76697, "CUC": 0.679886, "CUP": 17.495448, "CVE": 71.036587, "CZK": 15.655931, "DJF": 121.03866, "DKK": 4.798556, "DOP": 37.384907, "DZD": 93.763028, "EGP": 16.692146, "ERN": 10.191969, "ETB": 36.369497, "EUR": 0.644695, "FJD": 1.491565, "FKP": 0.554593, "GBP": 0.554517, "GEL": 1.807457, "GGP": 0.554687, "GHS": 8.821982, "GIP": 0.554671, "GMD": 42.414134, "GNF": 5962.079622, "GTQ": 5.377711, "GYD": 142.323738, "HKD": 5.290909, "HNL": 16.78467, "HRK": 4.872813, "HTG": 96.510913, "HUF": 270.022996, "IDR": 10601.290905, "ILS": 2.323138, "IMP": 0.554417, "INR": 56.013894, "IQD": 992.347569, "IRR": 28094.812915, "ISK": 96.426679, "JEP": 0.554837, "JMD": 104.621903, "JOD": 0.482642, "JPY": 92.795334, "KES": 83.52796, "KGS": 57.718952, "KHR": 2803.970113, "KMF": 316.87418, "KPW": 611.49584, "KRW": 886.172632, "KWD": 0.208819, "KYD": 0.566868, "KZT": 319.803998, "LAK": 11757.475046, "LBP": 1027.999431, "LKR": 249.868602, "LRD": 104.633831, "LSL": 11.758275, "LYD": 3.296476, "MAD": 7.176456, "MDL": 13.173462, "MGA": 3005.199144, "MKD": 39.668915, "MMK": 1427.81879, "MNT": 2314.826039, "MOP": 5.449939, "MRU": 25.755219, "MUR": 29.671919, "MVR": 10.429459, "MWK": 697.884998, "MXN": 13.437892, "MYR": 2.992467, "MZN": 43.416532, "NAD": 11.659568, "NGN": 301.539681, "NIO": 24.731812, "NOK": 6.798568, "NPR": 89.578942, "NZD": 1.059807, "OMR": 0.261433, "PAB": 0.679757, "PEN": 2.627768, "PGK": 2.395946, "PHP": 37.624472, "PKR": 152.776964, "PLN": 3.025923, "PYG": 4855.481002, "QAR": 2.488362, "RON": 3.176028, "RSD": 75.63698, "RUB": 42.475591, "RWF": 728.936617, "SAR": 2.555519, "SBD": 5.569607, "SCR": 9.244687, "SDG": 388.300175, "SEK": 7.033013, "SGD": 0.921544, "SHP": 0.554676, "SLL": 12002.294297, "SOS": 386.539849, "SRD": 21.465205, "SSP": 88.504367, "STD": 15507.515029, "STN": 15.783755, "SVC": 5.949858, "SYP": 1707.111441, "SZL": 11.757957, "THB": 23.526181, "TJS": 6.867343, "TMT": 2.382163, "TND": 2.180303, "TOP": 1.591945, "TRY": 12.672378, "TTD": 4.613765, "TWD": 20.865351, "TZS": 1585.556628, "UAH": 24.983723, "UGX": 2508.888349, "USD": 0.679936, "UYU": 26.574618, "UZS": 7650.465445, "VES": 9.226743, "VND": 16170.654069, "VUV": 80.204053, "WST": 1.833321, "XAF": 422.789363, "XAG": 0.029254, "XAU": 0.001095, "XCD": 1.836708, "XDR": 0.513675, "XOF": 422.789207, "XPD": 0.00104, "XPF": 76.914429, "XPT": 0.001198, "YER": 170.030078, "ZAR": 11.791482, "ZMW": 11.827688, "ZWL": 218.779657 }
How can I modify my vb code to get the three letter code and the value seperated into seperate columns for example to put into a database table
RE: Help with Json
Does jsonObject.SelectToken("rates") return a dictionary or do you have to access the elements individually like when using JSON in jscript running in vbscript code. Bsically, can you do this with the object returned
CODE
RE: Help with Json
I usually work in C#, so you will need to convert this to VB, which should be relatively simple.
Whenver I do something like this, I create a C# object to hold the data, something like this:
CODE
I realize there are more exchange rates, I added a couple so you can see how it works.
Then, I use Newtonsoft.Json to convert the json string to an object, like this.
CODE
You can access each item like this:
CODE
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom