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
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.
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.
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.
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