I am trying to get two pieces of information from a JSON returned file (which is quite large). I am able to get the top level piece quite easily, namely the nearest_postcode. As you will see from the code, to retrieve the ‘ward’ I beat the file with a large stick and extract it. Can someone show or suggest how to traverse the JSON file and get to the ward_name.
My clumsy code (which i pass a latitude and longitude to) is below:
Var results As New Dictionary
Var url As String = “https://findthatpostcode.uk/points/” + latlon + “.json”
Var ward As String
Var socket As New URLConnection
Try
// 1. Fetch the data synchronously (timeout 10s)
Var response As String = socket.SendSync(“GET”, url, 5)
Var root As New JSONItem(response)
If root.HasKey(“data”) Then
Var data As JSONItem = root.Value(“data”)
// 2. Extract Distance from attributes
If data.HasKey("attributes") Then
Var attr As JSONItem = data.Value("attributes")
results.Value("distance") = attr.Lookup("distance_from_postcode", 0.0)
End If
// 3. Extract Postcode from relationships
If data.HasKey("relationships") Then
Var rel As JSONItem = data.Value("relationships")
Var nearest As JSONItem = rel.Value("nearest_postcode")
Var postData As JSONItem = nearest.Value("data")
results.Value("postcode") = postData.Lookup("id", "Unknown")
End If
End If
Var source As String=response
Var searchfor As String=“ward_name”
// Find the starting position of your search string
Dim startPos As Integer = source.IndexOf(searchFor)
If startPos <> -1 Then
// Adjust startPos to the end of the search term if you don’t want the label included
startPos = startPos + searchFor.Length
// Find the next comma starting from our search position
Dim commaPos As Integer = source.IndexOf(startPos,",")
If commaPos <> -1 Then
// Extract between startPos and the comma
ward = source.Middle(startPos, commaPos - startPos)
Else
// No comma found, so take everything to the end of the string
ward = source.Middle(startPos)
End If
ward=ward.ReplaceAll(":","")
ward=ward.ReplaceAll(Chr(34),"")
results.Value("ward") = ward.Trim
End If
Catch e As RuntimeException
// Handle network or parsing errors
results.Value(“error”) = e.Message
End Try
app.MouseCursor=System.Cursors.StandardPointer
Return Format(results.value(“distance”),“#####.##”)+" metres from centre of "+results.value(“postcode”)+EndOfLine+ward
I can’t easily see the part of the JSON you’re trying to parse, but the top section of the code utilizing JSONItem is on the right track. Best advice I can give is general advice.
To iterate a JSON array, you would use LastRowIndex with either ChildAt or ValueAt. ChildAt always returns a JSONItem, even if the member is null. ValueAt returns a Variant, which will properly be null, so I recommend using that method.
To iterate a JSON structure, which is less common, you use the Keys method to get a list of all the keys, then loop over that like any other string array.
Don’t be afraid to split this into multiple methods. If you have an array to search, you could write a method that searches one member of the array and only return a non-nil result if something was found. This will help compartmentalize logic and keep each step easier to follow when you return to the code in the future.
Var js As JSONItem
Var results As New Dictionary
Var url As String = "https://findthatpostcode.uk/postcodes/SW1A%201AA.json"
Var conn As New URLConnection
Try
Var response As String = conn.SendSync("GET", url, 6)
js = New JSONItem(response)
Var data As JSONItem = js.Value("data")
Var attr As JSONItem = data.Value("attributes")
results.Value("ward") = attr.Value("ward")
results.Value("ward_name") = attr.Value("ward_name")
Catch e As RuntimeException
// Handle network or parsing errors
results.Value("error") = e.Message
End Try
Break
Thank you @Thom_McGrath for the pointers and @Rick_Araujo for the code which works flawlessly. I have a long way to go to understand JSON and your help is appreciated.
A while back I put together a JSONPath extension, it’s not perfect (has some issues with arrays and is probably in need of a v2 sometime soon), but it could be helpful for this. Once you add it to your project you could modify @Rick_Araujo’s code to pull the strings out using the JSON like this:
var ward as string = js.Query("$.data.attributes.ward")
var wardName as string = js.Query("$.data.attributes.ward_name")
It seems I have spoken to soon, when retrieving the data with a latitude and longitude the file format seems to be different and it is now not so easy to get to the ‘ward_name’ as it was with a postcode. From looking at what gemini AI suggests the data is now in some sort of array that has to be parsed to get to the correct endpoint. https://findthatpostcode.uk/points/51.501009,-0.141588.json is an example that does not return the correct value.
You need to learn JSON data access first, later you can play with AI and learn how to say “that’s incorrect, because this and that…” and AI “I apologize for the mistake, thank you for calling my attention…”
Other day I asked Gemini a financial question, and it said something stupid, instead of correcting it, I just said “that’s incorrect, the term is not xxx xxxx xxxx” and it thought a bit more and said something “I’m sorry, I’ve confused xxxx with xxxxx because they looked similar, the correct should be yyyy yyyyy yyyyy …” and another long and wrong answer. I finally just said a dry line like “zzzz”.
And Gemini: More apologies, more “I’ve mistanken because…” More “Thank you, here is the correct answer …” and now it said the correct answer, my answer, in a long descriptive way.
Looking for the ward_name I see a very confuse structure with something that looks an insane composition. E.g. There’s ward_name and wd_name, side by side, same data.
Better to get the API docs.
The path for ward_name, was x.included[0].attributes.ward_name
Var js As JSONItem
Var results As New Dictionary
Var url As String = "https://findthatpostcode.uk/points/51.501009,-0.141588.json"
Var conn As New URLConnection
Try
Var response As String = conn.SendSync("GET", url, 6)
js = New JSONItem(response)
Var inc As JSONItem = js.Value("included")
Var inc0 As JSONItem = inc.ValueAt(0)
Var attr As JSONItem = inc0.Value("attributes")
results.Value("ward") = attr.Value("ward")
results.Value("ward_name") = attr.Value("ward_name")
Catch e As RuntimeException
// Handle network or parsing errors
results.Value("error") = e.Message
End Try
Break
Thank you Rick for the additional suggestions and code, also the really handy tip as regards firefox which is really useful for future reference as it is my browser of choice.
