Understanding JSON

[Scott_Crick]

Okay, I’m sure this is very much a simple question and I’m sure I am missing something, but I’m still thinking I’m not doing something right here.

I am reading weather data from a Davis weather station via their WeatherLink API. This is all working well. The API returns the data from the weather station as a whole huge block of JSON. Fair enough.

However, where I’m struggling is the proper way to get the data I need out of the JSON. I’m very inexperienced when it comes to parsing JSON. I’ve read through the docs and a few online tutorials, but I still think something isn’t clicking for me. The code I’m using to get what I need seems very kludgy and very “brute force”. This just doesn’t seem right.

Here’s the code I’m using to get where I need to be. Please be kind. I know this is probably very ugly.

Var CurrentData as Dictionary = DataRetriever.JSONDataHolder
// DataRetriever is a custom class that contains other properties.
// JSONDataHolder is the variant that contains the results of the ParseJSON function.
Var Sensors() as Variant = CurrentData.Value( "sensors" )
Var WeatherData as Dictionary = Sensors( 3 )
Var WeatherReadings() as Variant = WeatherData.Value( "data" )
Var WeatherDict as Dictionary = WeatherReadings( 0 )

Var Temp as Double = WeatherDict.Value( "temp" )
Var HeatIndex as Double = WeatherDict.Value( "heat_index" )
Var WindChill as Double = WeatherDict.Value( "wind_chill" )
Var WindSpeed as Double = WeatherDict.Value( "wind_speed_last" )
Var WindDirection as Integer = WeatherDict.Value( "wind_dir_last" )
Var WindGust as Double = WeatherDict.Value( "wind_speed_hi_last_10_min" )
Var WindGustDirection as Integer = WeatherDict.Value( "wind_dir_at_hi_speed_last_10_min" )
Var DewPoint as Double = WeatherDict.Value( "dew_point" )
Var Humidity as Double = WeatherDict.Value( "hum" )

And, for reference, here is an example of the returned JSON (I should point out that this is sample data from the WeatherLink API reference guide, not real data supplied by the weather station in question):

{
    "station_id": 374964,
    "sensors": [
        {
            "lsid": 5271270,
            "data": [
                {
                    "ip_v4_netmask": "255.255.255.0",
                    "battery_voltage": 70,
                    "wifi_rssi": -70,
                    "network_error": 2,
                    "ip_v4_gateway": "192.168.0.1",
                    "rapid_records_sent": 138,
                    "firmware_version": 1559767913,
                    "uptime": 5376092,
                    "touchpad_wakeups": 27,
                    "ip_v4_address": "192.168.0.103",
                    "bootloader_version": 1550707628,
                    "local_api_queries": 4,
                    "rx_bytes": 342707,
                    "health_version": 1,
                    "radio_version": 621020416,
                    "espressif_version": 1534381024,
                    "ip_address_type": 1,
                    "link_uptime": 774880,
                    "network_type": 1,
                    "input_voltage": 4686,
                    "tx_bytes": 3823085,
                    "ts": 1565656200
                }
            ],
            "sensor_type": 504,
            "data_structure_type": 15
        },
        {
            "lsid": 5271273,
            "sensor_type": 45,
            "data_structure_type": 10,
            "data": [{
                "ts": 1558741927,
                "tx_id": 1,
                "wind_speed_hi_last_2_min": 5,
                "hum": 42.7,
                "wind_dir_at_hi_speed_last_10_min": 260,
                "wind_chill": 73.3,
                "rain_rate_hi_last_15_min_clicks": 0,
                "thw_index": 72.2,
                "wind_dir_scalar_avg_last_10_min": 221,
                "rain_size": 1,
                "uv_index": 2.3,
                "wind_speed_last": 4,
                "rainfall_last_60_min_clicks": 0,
                "wet_bulb": 55.8,
                "rainfall_monthly_clicks": 117,
                "wind_speed_avg_last_10_min": 3.56,
                "wind_dir_at_hi_speed_last_2_min": 225,
                "rainfall_daily_in": 0,
                "wind_dir_last": 195,
                "rainfall_daily_mm": 0,
                "rain_storm_last_clicks": 22,
                "rain_storm_last_start_at": 1558428061,
                "rain_rate_hi_clicks": 0,
                "rainfall_last_15_min_in": 0,
                "rainfall_daily_clicks": 0,
                "dew_point": 49.3,
                "rainfall_last_15_min_mm": 0,
                "rain_rate_hi_in": 0,
                "rain_storm_clicks": 0,
                "rain_rate_hi_mm": 0,
                "rainfall_year_clicks": 117,
                "rain_storm_in": 0,
                "rain_storm_last_end_at": 1558576860,
                "rain_storm_mm": 0,
                "wind_dir_scalar_avg_last_2_min": 222,
                "heat_index": 72.2,
                "rainfall_last_24_hr_in": 0,
                "rainfall_last_60_min_mm": 0,
                "rainfall_last_60_min_in": 0,
                "rain_storm_start_time": null,
                "rainfall_last_24_hr_mm": 0,
                "rainfall_year_in": 1.17,
                "wind_speed_hi_last_10_min": 6,
                "rainfall_last_15_min_clicks": 0,
                "rainfall_year_mm": 29.718,
                "wind_dir_scalar_avg_last_1_min": 214,
                "temp": 73.3,
                "wind_speed_avg_last_2_min": 3.18,
                "solar_rad": 598,
                "rainfall_monthly_mm": 29.718,
                "rain_storm_last_mm": 5.588,
                "wind_speed_avg_last_1_min": 3.37,
                "thsw_index": 72.6,
                "rainfall_monthly_in": 1.17,
                "rain_rate_last_mm": 0,
                "rain_rate_last_clicks": 0,
                "rainfall_last_24_hr_clicks": 0,
                "rain_storm_last_in": 0.22,
                "rain_rate_last_in": 0,
                "rain_rate_hi_last_15_min_mm": 0,
                "rain_rate_hi_last_15_min_in": 0
            }]
        },
        {
            "lsid": 5271271,
            "sensor_type": 242,
            "data_structure_type": 12,
            "data": [{
                "ts": 1558741927,
                "bar_absolute": 29.515,
                "bar_sea_level": 29.61,
                "bar_offset": -0.001,
                "bar_trend": -0.063
            }]
        },
        {
            "lsid": 5271399,
            "sensor_type": 37,
            "data_structure_type": 10,
            "data": [{
                "ts": 1558741927,
                "tx_id": 2,
                "wind_speed_hi_last_2_min": 3.93,
                "hum": 53.7,
                "wind_dir_at_hi_speed_last_10_min": 266,
                "wind_chill": 73.6,
                "rain_rate_hi_last_15_min_clicks": 0,
                "thw_index": 73.5,
                "wind_dir_scalar_avg_last_10_min": 242,
                "rain_size": 1,
                "uv_index": null,
                "wind_speed_last": 3.37,
                "rainfall_last_60_min_clicks": 0,
                "wet_bulb": 60.6,
                "rainfall_monthly_clicks": 238,
                "wind_speed_avg_last_10_min": 2.81,
                "wind_dir_at_hi_speed_last_2_min": 223,
                "rainfall_daily_in": 0,
                "wind_dir_last": 233,
                "rainfall_daily_mm": 0,
                "rain_storm_last_clicks": 22,
                "rain_storm_last_start_at": 1558428661,
                "rain_rate_hi_clicks": 0,
                "rainfall_last_15_min_in": 0,
                "rainfall_daily_clicks": 0,
                "dew_point": 55.8,
                "rainfall_last_15_min_mm": 0,
                "rain_rate_hi_in": 0,
                "rain_storm_clicks": 0,
                "rain_rate_hi_mm": 0,
                "rainfall_year_clicks": 238,
                "rain_storm_in": 0,
                "rain_storm_last_end_at": 1558555260,
                "rain_storm_mm": 0,
                "wind_dir_scalar_avg_last_2_min": 237,
                "heat_index": 73.5,
                "rainfall_last_24_hr_in": 0,
                "rainfall_last_60_min_mm": 0,
                "rainfall_last_60_min_in": 0,
                "rain_storm_start_time": null,
                "rainfall_last_24_hr_mm": 0,
                "rainfall_year_in": 2.38,
                "wind_speed_hi_last_10_min": 5.37,
                "rainfall_last_15_min_clicks": 0,
                "rainfall_year_mm": 60.452,
                "wind_dir_scalar_avg_last_1_min": 231,
                "temp": 73.6,
                "wind_speed_avg_last_2_min": 2.56,
                "solar_rad": null,
                "rainfall_monthly_mm": 60.452,
                "rain_storm_last_mm": 5.588,
                "wind_speed_avg_last_1_min": 2.75,
                "thsw_index": null,
                "rainfall_monthly_in": 2.38,
                "rain_rate_last_mm": 0,
                "rain_rate_last_clicks": 0,
                "rainfall_last_24_hr_clicks": 0,
                "rain_storm_last_in": 0.22,
                "rain_rate_last_in": 0,
                "rain_rate_hi_last_15_min_mm": 0,
                "rain_rate_hi_last_15_min_in": 0
            }]
        }
    ],
    "generated_at": 1558741957
}

That first section of my code is the only way I can figure out how to drill down to the section of the JSON I need. But, it seems very much ugly to me and I’m sure I’m missing something.

I hope this makes sense. Does anyone have any feedback here or suggestions for improvements?

[Kem_Tekinay]

Nope, that’s the nature of JSON, so the way you’re doing it is the way it’s done, unfortunately.

If the structure of this data doesn’t change, you could create classes to hold each section type with a parser that goes through the data and creates those classes in a nested way. That would make it somewhat easier to deal with.

[Andrew_Paul_Dickey]

I prefer using JSONItem see JSONItem — Xojo documentation, and make good use of HasKey and Lookup. Either way, Kem is correct, using JSON requires some data gymnastics. It becomes second nature after a while.

Kind regards, Andrew

[Scott_Crick]

Oof. That just makes my brain hurt. But, fair enough.

I’ll consider your suggestion and see if I can figure something out. But, I really only need to get data from the one section of JSON that I’m diving into now.

[Scott_Crick]

I looked into JSONItem and I don’t remember why I decided to go the way I went (the code above was written a few months ago and I was revisiting it for unrelated reasons when I got to thinking how bad it looked). Maybe I should revisit.

Thank you!

[Thom_McGrath]

Definitely use JSONItem. It is dramatically easier to use when dealing with arrays.

[MarkusR]

@Scott_Crick

JSON

maybe you could put the json data into a sqlite database in case you will create a chart.
https://sqlite.org/json1.html

to have it nice i would use one ore more classes with a constructor that take a JSONItem and map the data to properties.

[Rick_Araujo]

Var js As New JSONItem(JSONWeatherText) // your sample 

Var sensors, sensor, readings, data As JSONItem

sensors = js.Value("sensors")

Var lastSensor As Integer = sensors.LastRowIndex

For sensorIndex As Integer = 0 to lastSensor 
  
  data = sensors(sensorIndex)
  
  Var lsid As String = data.Lookup("lsid", "???")
  Var sensorType As String = data.Lookup("sensor_type", "???")
  Var dataStruct As String = data.Lookup("data_structure_type", "???")
  readings = data.Value("data")
  
  Var lastReading As Integer = readings.LastRowIndex
  
  For readingIndex As Integer = 0 to lastReading
    
    data = readings(readingIndex)
    
    // data contains values as:
    
    // {
    //    "ip_v4_netmask":"255.255.255.0",
    //    "battery_voltage":70,
    //    "wifi_rssi":-70,
    //    "network_error":2,
    //    "ip_v4_gateway":"192.168.0.1",
    //    "rapid_records_sent":138,
    //    "firmware_version":1559767913,
    //    "uptime":5376092,
    //    "touchpad_wakeups":27,
    //    "ip_v4_address":"192.168.0.103",
    //    "bootloader_version":1550707628,
    //    "local_api_queries":4,
    //    "rx_bytes":342707,
    //    "health_version":1,
    //    "radio_version":621020416,
    //    "espressif_version":1534381024,
    //    "ip_address_type":1,
    //    "link_uptime":774880,
    //    "network_type":1,
    //    "input_voltage":4686,
    //    "tx_bytes":3823085,
    //    "ts":1565656200
    // }
    
    
    ///// Process it as you wish
    
    Var bat As Variant = data.Lookup("battery_voltage", Nil)
    
    Var someLine As String
    
    someLine = "Sensor " + lsid + EndOfLine
    
    someLine = someLine + "Sensor Type: " + sensorType + EndOfLine
    someLine = someLine + "Data Structure: " + dataStruct + EndOfLine
    
    If bat is Nil Then 
      someLine = someLine + "Battery not found"
    Else
      someLine = someLine + "Battery voltage: "+bat.StringValue
    End
    
    MessageBox someLine
    
  Next
  
Next

Quit

[Andrew_Paul_Dickey]

Hello @Scott_Crick,

Here is a script that takes your json and outputs your chosen data nodes to a WebListBox.

It shows most of the techniques required.

This demonstrates the use of HasKey to check whether a node exists before outputting data.

ListBox1.RemoveAllRows // reset the listbox

Var jsonFeed As New JSONItem(Session.pJson) // json from api call (stored for convenience in a session prpoerty (string)
jsonFeed.Compact = True // compact json array
Var jsonSensors As JSONItem = jsonFeed.Lookup("sensors", New JSONItem) // drill down to node "sensors"
Var vSensorsCount As Integer = jsonSensors.Count // count the number of sensors
tfSensorsCount.Text = vSensorsCount.ToString // display the number of sensors in a text field

' targeted data (initiate vars)
Var vLsid As String
Var vHeatIndex As String
Var vWindChill As String
Var vWindSpeed As String
Var vWindDirection As String
Var vWindGust As String
Var vWindGustDirection As String
Var vDewPoint As String
Var vHumidity As String



// loop the array of sensors
For i As Integer = 0 To (vSensorsCount-1)
  Var jsonThisSensor As JSONItem = jsonSensors.ChildAt(i) // drill down to sensors i
  Var jsonDataArray As JSONItem = jsonThisSensor.Lookup("data", New JSONItem) // drill down to node "data" which is array
  Var jsonData As JSONItem = jsonDataArray.ChildAt(0) // data is contained in the first item in the array
  ' sensor id "lsid"
  If jsonThisSensor.HasKey("lsid") Then
    vLsid = jsonThisSensor.Value("lsid")
  Else
    vLsid = "NA"
  End
  ' Heat Index "heat_index"
  If jsonData.HasKey("heat_index") Then
    vHeatIndex = jsonData.Value("heat_index").DoubleValue.ToString
  Else
    vHeatIndex = "NA"
  End
  ' Wind Chill "wind_chill"
  If jsonData.HasKey("wind_chill") Then
    vWindChill = jsonData.Value("wind_chill").DoubleValue.ToString
  Else
    vWindChill = "NA"
  End
  ' Wind Speed "wind_speed_last"
  If jsonData.HasKey("wind_speed_last") Then
    vWindSpeed = jsonData.Value("wind_speed_last").DoubleValue.ToString
  Else
    vWindSpeed = "NA"
  End
  ' Wind Direction "wind_dir_last"
  If jsonData.HasKey("wind_dir_last") Then
    vWindDirection = jsonData.Value("wind_dir_last").DoubleValue.ToString
  Else
    vWindDirection = "NA"
  End
  ' Wind Gust "wind_speed_hi_last_10_min"
  If jsonData.HasKey("wind_speed_hi_last_10_min") Then
    vWindGust = jsonData.Value("wind_speed_hi_last_10_min").DoubleValue.ToString
  Else
    vWindGust = "NA"
  End
  ' Wind Gust Direction "wind_dir_at_hi_speed_last_10_min"
  If jsonData.HasKey("wind_dir_at_hi_speed_last_10_min") Then
    vWindGustDirection = jsonData.Value("wind_dir_at_hi_speed_last_10_min").DoubleValue.ToString
  Else
    vWindGustDirection = "NA"
  End
  ' Dew Point "dew_point"
  If jsonData.HasKey("dew_point") Then
    vDewPoint = jsonData.Value("dew_point").DoubleValue.ToString
  Else
    vDewPoint = "NA"
  End
  ' Humidity "hum"
  If jsonData.HasKey("hum") Then
    vHumidity = jsonData.Value("hum").DoubleValue.ToString
  Else
    vHumidity = "NA"
  End
  ListBox1.AddRow(vLsid,vHeatIndex,vWindChill,vWindSpeed,vWindDirection,vWindGust,vWindGustDirection,vDewPoint,vHumidity)
Next

The output below, note if the node/key doesn’t exist for that sensor I default to “NA”.

Happy to share the project but the button script above contains all the logic.

Kind regards, Andrew

[Scott_Crick]

Thank you for this!

Although I haven’t had a chance to look into JSONItem again yet as suggested earlier, I looked through your code and I think I can kinda see how it works. I’m going to play around with this here soon to make sure I really understand the fundamentals here. At first glance though, this seems to work closer to what I would have expected.

I’ll follow up more when I have a chance to dive into this more (right now, focusing on another part of the project). Probably in a day or two.

Thank you again!

[Scott_Crick]

This was hugely helpful and I think I understand how to use the JSON classes, including JSONItem much better than I did before.

Thank you so much for this!