ParseJSON and dictionary from API

I’m pulling some json from a website thanks to an API. This is an example of what I get:

{ "bmx":{ "series":[ { "idSerie":"SP1", "titulo":"IPC Por objeto del gasto Nacional I n d i c e G e n e r a l", "datos":[ { "fecha":"01/01/2017", "dato":"124.598000000000" }, { "fecha":"01/02/2017", "dato":"125.318000000000" }, { "fecha":"01/03/2017", "dato":"126.087000000000" }, { "fecha":"01/04/2017", "dato":"126.242000000000" }, { "fecha":"01/05/2017", "dato":"126.091000000000" }, { "fecha":"01/06/2017", "dato":"126.408000000000" }, { "fecha":"01/07/2017", "dato":"126.886000000000" }, { "fecha":"01/08/2017", "dato":"127.513000000000" }, { "fecha":"01/09/2017", "dato":"127.912000000000" }, { "fecha":"01/10/2017", "dato":"128.717000000000" }, { "fecha":"01/11/2017", "dato":"130.044000000000" } ] } ] } }

This example only has 1 “series”, the API let me pull up to 5 series at once. I want to start easy. This is the first time I use json, dictionary and an API.

When I parse this json I get array and dictionary inside another array and dictionary (I think). So to get each ‘fecha’ and ‘dato’ I did this:

Dim dict As Xojo.Core.Dictionary dict = Xojo.Data.ParseJSON(TextArea1.Text.ToText) dim bmx As Xojo.Core.Dictionary = dict.Value("bmx") dim Series() As Auto = bmx.Value("series") dim Serie As Xojo.Core.Dictionary = Series(0) dim idSeries As Text = Serie.Value("idSerie") dim datos() As Auto = Serie.Value("datos") dim dict2 As Xojo.Core.Dictionary dim dato,fecha As Text For Each d As Xojo.Core.Dictionary In datos fecha = d.Value("fecha") dato = d.Value("dato") MsgBox(fecha + " " + dato) Next

Is this the correct way to do it?

It’s so long correct as the dictionary doesn’t puke on the JSON. The conversion from dictionary to JSON is nice but in my experience limited.

I have the option to ask for XML, even HTML, instead, do you think that will be better?

I was trying to get some of this information from a webpage (HTML) but I was pointed to use an API when available.

Thank you

Maybe is better to just get the json (that’s the default from the API), then use RegEx to pull the ‘fecha’ and ‘dato’. Or do you know a better option to get that information? Ultimately I’ll put the information into a SQLite database.

Hi @Alberto De Poo.

I think you’ll find that working with JSON API responses is a little easier to work with.

Here’s another way to parse the JSON…

Dim JSON As New JSONItem(TextArea1.Text.ToText) Dim BMX As JSONItem = JSON.Value("bmx") Dim Series As JSONItem = BMX.Value("series") Dim Serie As JSONItem = Series(0) Dim Datos As JSONItem = Serie.Value("datos") For i As Integer = 0 to Datos.Count Dim DatoPoint As JSONItem = Datos(i) Dim Fecha As String = DatoPoint.Value("fecha") Dim Dato As String = DatoPoint.Value("dato") Next

Inside the loop, you could take the Fecha and Dato values and store them in your database.

I hope that helps. Good luck.

Your code seems fine. Does it work?

Note that for long JSON, the new framework function will be significantly slower on Windows and Linux so you’re better off using JSONItem, even if it’s less convenient. For even better performance, and elimination of some native bugs and limitations, use my JSONItem_MTC, a drop-in replacement for JSONItem.

If you are strictly Mac, stick with the new framework functions as posted. It is the fastest, most convenient way to handle it.

Thank you Tim and Kem, I’ll take a look to JSONItem and JSONItem_MTC.

Tim, your code looks easier than what I used. Only had to add a -1 to Datos.Count.

Kem, yes the code works fine. I will use the code on Windows. The ‘datos’ are all historic values, so they will not change once they are in the database and no need to get large JSON. This example has monthly values only the Dollar exchange rate is a daily value.

JSON and dictionary are new to me. I remember using some string commands to find the text I wanted (more than 25 years ago). Using RegEx I coded this:

Dim rxF As New RegEx Dim rxD As New RegEx Dim myFecha,myDato As RegExMatch rxF.SearchPattern = "\\d\\d/\\d\\d/\\d\\d\\d\\d" rxD.SearchPattern = "\\d{1,3}\\.\\d{4,13}" myFecha = rxF.Search(TextArea1.Text) myDato = rxD.Search(TextArea1.Text) Do if myFecha <> Nil and myDato <> Nil Then MsgBox(myFecha.SubExpressionString(0) + " " + myDato.SubExpressionString(0)) Else MsgBox "no encontrado" End if myFecha = rxF.Search myDato = rxD.Search loop until myFecha is Nil

I don’t know if this is slower than JSONItem, my guess is that it is slower.

Yes, use either the new framework function or JSONItem(_MTC), don’t try to parse it yourself.

Thank you Kem.

I changed Tim’s code to this:

Dim JSON As New JSONItem(TextArea1.Text.ToText) Dim Datos As JSONItem = JSON.Child("bmx").Child("series").Child(0).Value("datos") MsgBox Datos.Count.ToText For i As Integer = 0 to Datos.Count - 1 Dim DatoPoint As JSONItem = Datos(i) Dim Fecha As String = DatoPoint.Value("fecha") Dim Dato As String = DatoPoint.Value("dato") Next

The JSONItem.Child documentation is not clear for newbies like me (and I think the example is wrong), but nothing that can’t figure out with some other searches and tests.

JSONItem.Child(Index As Integer), from what I figure, is used when the item is an Array.
JSONItem.Child(Name As String), is used when the item is a Dictionary