Decoding A JSON Result

General
1

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.

https://findthatpostcode.uk/postcodes/SW1A%201AA.json

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
2

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.

3

image
image881×626 42.5 KB

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
4

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.

1 Like
5

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")
1 Like
6

If you happen to have access to MBS plugins, you can use JSON Path queries there as well.

2 Likes
7

Thanks for the further suggestions which I will take a look at.