How to access a collection in VBA-JSON?

[wanderleihuttel]

Hello Guys

How can I get data from Json "Name" and "Options"? How Option keys are always different, I would like to access using maybe the index?

I've read some another issues, but I didn't understand very well.

It would be interesting to include more examples in github, this could clarify how to use and avoid new issues.

My Json

{
    "Name": "Report001",
    "Fields": [
        { "Name": "pCompetInicial" },
        { "Name": "pCompetFin" },
        { "Name": "pGrupoProcessamento" },
        { "Name": "pCodigoEmpresa" },
        { "Name": "pQuebra", 
          "Options": [
                { "Option XPTO": 0 },
                { "Option X": 1 },
                { "Option FOO": 2 },
                { "Option BAR": 3 }
        ]},
        { "Name": "pOrdenar",
          "Options": [
                { "Some Informarmation": 0 },
                { "Another Informarmation": 1 },
                { "Test": 2 }
        ]},
        { "Name": "pCodigoIdioma" }
    ],
    "Version": "23.5.0.0"
}

Sub TestJson()
    Dim JSON As Scripting.Dictionary
    Dim vItem As Scripting.Dictionary
    Dim vResponse As String
    Dim vArray As VBA.Collection
    Dim vDict As Scripting.Dictionary
    Dim vChild As Scripting.Dictionary

    'Response from Http Request
    vResponse = "{'Name':'Report001','Fields':[{'Name':'pCompetInicial'},{'Name':'pCompetFin'},{'Name':'pGrupoProcessamento'},{'Name':'pCodigoEmpresa'},{'Name':'pQuebra','Options':[{'Option XPTO':0},{'Option X':1},{'Option FOO':2},{'Option BAR':3}]},{'Name':'pOrdenar','Options':[{'Some Informarmation':0},{'Another Informarmation':1},{'Test':2}]},{'Name':'pCodigoIdioma'}],'Version':'23.5.0.0'}"
    vResponse = Replace(vResponse, "'", """")

    Set JSON = JsonConverter.ParseJson(vResponse)

    'Access single node
    Debug.Print JSON("Version")

    'Access a node with children
    For Each vItem In JSON("Fields")
        If vItem.Exists("Options") Then

            If TypeOf vItem("Options") Is VBA.Collection Then
                'How do I Access "Options" (all indexes and values) (by Key or Value or index)
            End If

            Debug.Print vItem("Name") & " - Exist Options"

        Else
            Debug.Print vItem("Name")
        End If
    Next vItem

End Sub

Thanks

[Nick-vanGemeren]

You process Options in the same way that you process Fields. Each option is (should be) a Dictionary. You get the list of keys with the Keys method.

Change the loop contents to ...

        If vItem.Exists("Options") Then
            Debug.Print vItem("Name") & " - Exist Options"
            ProcessOptions vItem("Options")
        Else
            Debug.Print vItem("Name")
        End If

and then add ...

Sub ProcessOptions(vOptions As Variant)
    Dim vDict As Scripting.Dictionary
    Dim vKey As Variant
    If Not TypeOf vOptions Is VBA.Collection Then
        Debug.Print "*** Options not a collection = "; TypeName(vOptions)
        Exit Sub
    End If
    For Each vDict In vOptions
        For Each vKey In vDict.Keys
            Debug.Print "", vKey, vDict(vKey)
        Next vKey
    Next vDict
End Sub