Cannot iterate when parsing HTML table using JSON-

Question

This is an example in JSON view of my users table that i want to parse", inluding an aliasList as the title for each column in the HTML table and the remaining users are below it.

{"totalCount":431,"messages":[],"results":[{"aliasList":["User Id","Name","last name"],"results":[[71512,"joe","adams"],[23445,"jack","wilson"],[34566,jill,goodman]],"executionDate":151134568428}],"Class":"com.zoho.controlpanel.reports.ReportsItemVO"}

And this my parse caller that is getting a Compile error:

For Each may only iterate over a collection object or an array

Public Sub exceljson()
Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "http://controlpanel.zoho.verio/rest/reports/search/NONE&offset=0", False
http.send
Set JSON = ParseJson(http.responseText)
i = 2
Dim data As String
data = JSON("results")(2)
Dim parsedData As Dictionary
Set parsedData = ParseJson(data)
For Each item In data
Sheets(5).Cells(i, 1).Value = item("results")(2) 'key is called simply "2"
Sheets(5).Cells(i, 2).Value = item("results")(4)
Sheets(5).Cells(i, 3).Value = item("results")(6)
Sheets(5).Cells(i, 4).Value = item("results")(13)
Sheets(5).Cells(i, 5).Value = item("results")(16)
Sheets(5).Cells(i, 6).Value = item("results")(18)
i = i + 1
Next
MsgBox ("complete")
End Sub

I tried many combinations in the Data declaration and which nested array to parse without success. However, i don't know if i need to use the aliasList in this case, which i don't know how to do it.

What should i modify in my call?

Answer 1

This part of your code says that data is a String:

Dim data As String
data = JSON("results")(2)

Thus, it is not iterable. Try to make data something else and to iterate through it. This is an example of a way to iterate through a string:

Option Explicit

Public Sub TestMe()

    Dim data            As String
    Dim somethingElse   As Variant

    data = "How much should I party today?"
    somethingElse = Split(data)

    Dim cnt             As Long
    For cnt = LBound(somethingElse) To UBound(somethingElse)
        Debug.Print somethingElse(cnt)
    Next cnt

End Sub

Answer 2

Your JSON is malformed, since you can only have one instance of each unique key. When the second "results" is parsed it just overwrites the first.

This worked for me (after fixing the errors in your JSON):

Dim j, k, o

'loading json from worksheet cell...
Set j = JsonConverter.ParseJson(Sheet1.Range("B6").Value)

For Each o In j("results")
    Debug.Print o("1"), o("2"), o("3")
Next

EDIT: updated for your "actual" json:

Sub Tester55()

    Dim j, c, res, v

    'loading json from worksheet cell...
    Set j = JsonConverter.ParseJson(Sheet1.Range("B6").Value)
    Set res = j("results")

    Set c = ActiveSheet.Range("F2")

    WriteCollection c, res(1)("aliasList")

    For Each v In res(1)("results")
        Set c = c.Offset(1, 0)
        WriteCollection c, v
    Next v

End Sub

Sub WriteCollection(rng, col)
    Dim v, i As Long
    For Each v In col
        rng.Offset(0, i).Value = v
        i = i + 1
    Next v
End Sub