Function for recursively exploring Dictionaries and Collections in Parsed JSON?

[xeijin]

@timhall (or indeed anyone else)

I have some fairly complex JSON (can't post from work unfortunately due to restrictions) which ends-up being parsed like so by VBA-JSON:

Dict > Coll > Dict > String

Unfortunately I have about 273 items like this in my JSON, at varying depths (some deeper than the above) and of course a mix of Collections and Dictionaries.

Could someone point me in the right direction with regards to how best to write a function to access the information I need in a pragmatic or exploratory way? I was thinking along the lines of a recursive For Loop that checks the type of key (Dictionary, Collection or Other) continuing to delve deeper as-necessary. Unfortunately my experiments don't seem to have worked out (specifically: https://codereview.stackexchange.com/questions/63353/traversing-and-printing-complex-dictionary-types-scripting-dictionary-dictio I am unfortunately getting Type mismatch errors when I eventually get to the combination of key that is a dictionary, but sits within a collection (e.g. c(key)).

Thanks in advance!

[zgrose]

Complexity doesn't require content.

Dict > Coll > Dict > String

An object, containing an array of objects with string properties, yes? So like:

{ "p1": [ {"p1":""}, {"p1":""}, {"p1":""} ] }

Check out the ConvertToJson() function since it's pretty much doing what you're doing by definition.

[xeijin]

Sorry I should have been clearer.

I am going JSON -> VBA, not the other way around. I am using the VBA-JSON library to parse my JSON into Dictionaries and Collections. But I want to be able to recurse all the way through the parsed JSON, to the deepest level, which I haven't managed to do yet because of the combination of Dictionaries and Collections.

[zgrose]

ConvertToJson walks the same Collection/Dictionary tree. You don't want to output a string, I get that, but you're traversing the object tree in the exact same manner. So you want to look at the ConvertToJson source and do what it does.

[xeijin]

I have just had a look, and will take another tomorrow morning, but the code in ConvertToJson is pretty sparsely commented, whilst I'm sure it's written extremely well I suspect I'm not quite at the stage where I can make sense of it.

What would be helpful is if someone could shed some light on dealing with Collections vs Dictionaries. I can't seem to put together a recursive function that doesn't trip-up one or both of them despite the fact that I'm trying to hand-hold it by checking for types etc (realise this is not helpful without some example code, will try and see if I get some free time this weekend to mock something up...)

[zgrose]

Minimizing ConvertToJson for you, it works on JsonValue (a Variant) in this manner:

Select Case VBA.VarType(JsonValue)
    ' Dictionary or Collection
    Case VBA.vbObject
        ' Dictionary
        If VBA.TypeName(JsonValue) = "Dictionary" Then
             ' do something
        ' Collection
        ElseIf VBA.TypeName(JsonValue) = "Collection" Then
             ' do something else
        End If
End Select

So I'd have to assume you just need to add one more level of hand-hold to your code.