Help with Json

G12Consult · Dec 10, 2022

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:

Code:

Dim url As String = "[URL unfurl="true"]https://api.exchangerate.host/latest?base=AUD"[/URL]

            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

xwb · Dec 26, 2022

Unless someone has any inkling of what Newtonsoft.Json does, you probably won't get a reply here.

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:

set rates = jsonObject.selectToken("rates")
for each key in rates
    debug.print key & " " & rates(key)
next

gmmastros · Jan 3, 2023

This may not be the best solution, but...

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:

    public class ApiData
    {
        public bool success { get; set; }
        public Rate rates { get; set; }

        public class Rate
        {
            public float AED { get; set; }
            public float AFN { get; set; }
            public float ALL { get; set; }
            public float AMD { get; set; }
            public float ANG { get; set; }
            public float AOA { get; set; }
            public float ARS { get; set; }
            public float AUD { get; set; }
            public float AWG { get; set; }
            public float AZN { get; set; }
            public float BAM { get; set; }
            public float BBD { get; set; }
            public float BDT { get; set; }
            public float BGN { get; set; }
        }
    }

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:

    public ApiData ExchangeRate()
    {
        ApiData output = new ApiData();
        string url = "[URL unfurl="true"]https://api.exchangerate.host/latest?base=AUD";[/URL]

        HttpWebRequest request;
        HttpWebResponse WebResponse;
        StreamReader reader;

        request = HttpWebRequest.CreateHttp(url);
        request.Method = "GET";
        WebResponse = (HttpWebResponse)request.GetResponse();

        reader = new StreamReader(WebResponse.GetResponseStream());

        string RawResponse = reader.ReadToEnd();
        output = JsonConvert.DeserializeObject<ApiData>(RawResponse);
        
        return output;
    }

You can access each item like this:

Code:

ApiData yourData = ExchangeRate();
Console.WriteLine(yourData.rates.AED);
Console.WriteLine(yourData.rates.AFN);

-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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Help with Json

G12Consult

Programmer

xwb

Programmer

gmmastros

Programmer

Similar threads

Part and Inventory Search

Sponsor