Access nested values in JSON-Object in VBA

Question

I would like to get data from a JSON-Object, that I got from a Rest-API, with VBA to display some data into an Excel-Worksheet. I'm using the library (VBA-JSON v2.3.1 JsonConverter).

I have the following JSON-Object:

{
  "devices": [
    {
      "data": [
        {
          "id": 0,
          "name": "Hello"
        },
    {
          "id": 1,
          "name": "How are you?"
        },
    {
          "id": 2,
          "name": "Bye"
        }
      ],
      "type": "LORA"
    }
  ],
  "includedTypes": [
    "LORA"
  ]
}

I want to get the objects in the array from "data".

My VBA-Code is this:

Dim js1Object As Object
        Dim response1 As String
        strUrl = "https://XXXXXXXXXXXdevices
        Set hReq = CreateObject("MSXML2.XMLHTTP")
    With hReq
        .Open "GET", strUrl, False
        .SetRequestHeader "Authorization", "Bearer " & apitoken
        .Send
        response1 = hReq.responseText
        MsgBox response1
    Set js1Object = JsonConverter.ParseJson(response1)
    j = 31
        For Each item In js1Object("devices")
        ws.Cells(j, 7) = item("id")
        ws.Cells(j, 10) = item("name")
        j = j + 1
    Next
        MsgBox (response1)
    End With

How can I access the values from "data"?

If the JSON would look like the object below, my code would work. But my problem is, that the response that I get, is more nested and I can't directly access "data".

{
      "devices": [
        {
          "id": 0,
          "name": "Hello"
        },
    {
          "id": 1,
          "name": "How are you?"
        },
    {
          "id": 2,
          "name": "Bye"
        }
      ]
    }

I just don't know, how to access deeper values in JSON-Object. The solutions from similar questions with print are not working with my code.

Thanks for helping me!

score 4 · Accepted Answer · answered Feb 07 '21 at 19:51

Your "root" json object is a Dictionary - the key "devices" is a Collection object, and the first element is another dictionary with two keys "data" and "type".

"data" is another Collection of Dictionaries, so you can do this to get to the contained id and name values:

Dim Json As Object, data, d
    
'reading json from a worksheet cell...
Set Json = JsonConverter.ParseJson(Range("A5").Value)
    
Set data = Json("devices")(1)("data") 'Dictionary key->Collection index->Dictionary key
    
For Each d In data
    Debug.Print d("id"), d("name")
Next d

Output:

 0            Hello
 1            How are you?
 2            Bye

Hi there, thank you so much for your answer! The code is working! — Ivan1102, Feb 08 '21 at 10:57

Access nested values in JSON-Object in VBA

1 Answers1