Trouble parsing JSON with vba

Question

I can get what appears to be a valid JSON string from a web query, however, I cannot set items correctly for the life of me. Need to confirm that I'm not losing my mind...

'Call for available reports

Dim URLReporta As String
Dim JSONa As Object
Dim var As Object  
Set myrequesta = CreateObject("winhttp.winhttprequest.5.1")
URLReporta = ("https://secure.saashr.com:443/ta/rest/v1/reports?type=Saved&company%3shortname=" & Company)
myrequesta.Open "GET", URLReporta, False
myrequesta.setRequestHeader "Accept", "application/json"
myrequesta.setRequestHeader "Authentication", "Bearer " & Token
myrequesta.setRequestHeader "Content-Type", "application/json"
myrequesta.Send
Set JSONa = JsonConverter.ParseJson(myrequesta.responseText)
Set var = JSONa("SavedName")
Debug.Print var.Count

I get an error on the line Set var = JSONa("SavedName"):

run-time error '424': object required

myrequesta.responseText value is as follows:

{"reports":[{"SavedName":"This Year","SettingId":18959322},{"SavedName":"Time Off Requests","SettingId":18960210},{"SavedName":"Calc Hours Summary","SettingId":18960209},{"SavedName":"roster","SettingId":18960211},{"SavedName":"E/D/T","SettingId":18823042},{"SavedName":"TestZDR","SettingId":18957188}]}

Solution 1

The structure returned by JsonConverter.ParseJson function doesn't work such way. For your particular JSON it contains 3 levels: Root-level object has only one property reports, which contains second-level array, which in turn contains 6 third-level objects, having properties SavedName and SettingId. You are trying to get third-level's object property value from root-level object.

First you need to get second-level array, then loop through it's elements, containing objects, and retrieve the SavedName properties' values of that objects. Here is the example:

'Call for available reports

Dim URLReporta As String
Dim JSONa As Object
Dim var As Object
Dim rep As Variant
Set myrequesta = CreateObject("winhttp.winhttprequest.5.1")
URLReporta = ("https://secure.saashr.com:443/ta/rest/v1/reports?type=Saved&company%3shortname=" & Company)
myrequesta.Open "GET", URLReporta, False
myrequesta.setRequestHeader "Accept", "application/json"
myrequesta.setRequestHeader "Authentication", "Bearer " & Token
myrequesta.setRequestHeader "Content-Type", "application/json"
myrequesta.Send
Set JSONa = JsonConverter.ParseJson(myrequesta.responseText) ' root level object
Set var = JSONa("reports") ' second level array
For Each rep In var ' third level objects
    Debug.Print rep("SavedName") ' property "SavedName" value of current third level object
Next

Here is the output:

If you want just to get the number of reports, then get the array and the number of elements in it:

Debug.Print JSONa("reports").Count

Solution 2

JSON objects can be thought of as a collection of dictionaries. So you have to walk through the inner values such as SavedName to retrieve whole dictionary objects (all SavedName values) or specific string values at indexed locations (one SavedName value):

Public Sub GetJSONRequest()
    Dim jsonStr As String

    jsonStr = "{" _
         & "     ""reports"": [{" _
         & "         ""SavedName"": ""This Year""," _
         & "         ""SettingId"": 18959322" _
         & "     }, {" _
         & "         ""SavedName"": ""Time Off Requests""," _
         & "         ""SettingId"": 18960210" _
         & "     }, {" _
         & "         ""SavedName"": ""Calc Hours Summary""," _
         & "         ""SettingId"": 18960209" _
         & "     }, {" _
         & "         ""SavedName"": ""roster""," _
         & "         ""SettingId"": 18960211" _
         & "     }, {" _
         & "         ""SavedName"": ""E/D/T""," _
         & "         ""SettingId"": 18823042" _
         & "     }, {" _
         & "         ""SavedName"": ""TestZDR""," _
         & "         ""SettingId"": 18957188" _
         & "  }]" _
         & "  }"

    Dim JSONa As Object, element As Object, e As Variant, i As Variant, var As Variant

    Set JSONa = ParseJson(jsonStr)

    ' DICTIONARY OBJECT
    Set element = CreateObject("Scripting.Dictionary")
    Set element = JSONa("reports")

    ' OUTER DICTIONARY
    For Each e In element
        ' INNER COLLECTION OF DICTIONARIES
        For Each i In e
            Debug.Print i & " " & e(i)
        Next i
    Next e            

    ' STRING VALUE OF FIRST SAVEDNAME VALUE
    var = JSONa("reports")(1)("SavedName")
    Debug.Print var

    Set element = Nothing
    Set JSONa = Nothing
End Sub

Output

SavedName This Year
SettingId 18959322
SavedName Time Off Requests
SettingId 18960210
SavedName Calc Hours Summary
SettingId 18960209
SavedName roster
SettingId 18960211
SavedName E/D/T
SettingId 18823042
SavedName TestZDR
SettingId 18957188
This Year

Comments

[Zach Rochon]

Thank you! That did the trick in unwinding how that parser is intended to work.

[S Meaden]

Did you know ScriptControl.Eval("("+ json + ")" ) will parse object and one can use CallByName to access elements. See my answer here stackoverflow.com/questions/37711034/…

[omegastripes]

@SMeaden that approach makes the system vulnerable in some cases (malicious code from the web query may be executed), take a look at this answer, especially update part.