Help with Json

[G12Consult]

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:

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

{ "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]

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

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

 

[gmmastros]

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:

    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.

    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:

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