Using VBA and VBA-JSON to access JSON data from WordPress API

The JsonConverter is returning a collection of VBA.Collections Scripting.Dictionaries, and Values. In order to understand the output you will have to test the TypeName of all the returned values.

The real question is “How to navigate through a json object (or any unknown object for that matter) and access the values within.

Immediate Window

Using the Immediate Window and the json object from the OP’s post I will try to describe the thought process (in the style of the must read book: The Little Schemer)

' What is json?
?TypeName(JSON)
Collection

'json is a collection
'How big is JSON
?JSON.Count
 1 

'JSON is a collection of 1 Item
'What is Type that Item?
?TypeName(JSON(1))
Dictionary

'JSON(1) is a Dictionary
'What is the first key in the JSON(1) Dictionary?
?JSON(1).Keys()(0)
id

'The first key in the JSON(1) Dictionary is "id"
'What is the Type of the value of "id"?
?TypeName(JSON(1)("id"))
Double

'JSON(1)("id") is a number
'What is its value
?JSON(1)("id")
 1 

Of course this process can get tedious consider the amount of nesting in this JSON Object.

JSON(1)(“_links”)(“curies”)(1)(“templated”)

Collection|Dictionary|Dictionary|Collection|Boolean Value

So I guess the best thing to do is write a function that will print all the accessor to the Immediate Window and go from there.

PrintJSONAccessors:Sub

Sub PrintJSONAccessors(JSON As Variant, Optional Prefix As String)
    Dim data As Variant, Key As Variant, Value As Variant
    Dim Accessor As String, ArrayAccessor As String
    Dim n As Long
    If TypeName(JSON) = "Collection" Then
        For n = 1 To JSON.Count
            Accessor = Prefix & "(" & n & ")"
            If TypeName(JSON(n)) = "Dictionary" Or TypeName(JSON(n)) = "Collection" Then
                PrintJSONAccessors JSON(n), Accessor
            Else
                Debug.Print Accessor
            End If
        Next
    Else
        For Each Key In JSON
            If TypeName(Key) = "Dictionary" Or TypeName(Key) = "Collection" Then
                PrintJSONAccessors Key, Prefix
            ElseIf TypeName(JSON(Key)) = "Dictionary" Or TypeName(JSON(Key)) = "Collection" Then
                Accessor = Prefix & "(" & Chr(34) & Key & Chr(34) & ")"
                PrintJSONAccessors JSON(Key), Accessor
            ElseIf TypeName(JSON(Key)) = "Dictionary" Then
                Accessor = Prefix & "(" & Chr(34) & Key & Chr(34) & ")"
                PrintJSONAccessors JSON(Key), Accessor
            ElseIf TypeName(JSON(Key)) = "Variant()" Then
                data = JSON(Key)
                For n = LBound(data) To UBound(data)
                    Accessor = Prefix & "(" & Chr(34) & Key & Chr(34) & ")"
                    ArrayAccessor = Prefix & "(" & Chr(34) & Key & Chr(34) & ")" & "(" & n & ")"
                    If TypeName(data(n)) = "Dictionary" Then
                        PrintJSONAccessors data(n), ArrayAccessor
                    Else
                        Debug.Print ArrayAccessor
                    End If
                Next
            Else
                Accessor = Prefix & "(" & Chr(34) & Key & Chr(34) & ")"
                Debug.Print Accessor
            End If
        Next
    End If
End Sub

Usage:

 PrintJSONAccessors JSON, "?JSON"

---

It appears that the MSScriptControl.ScriptControl only works on 32 bit systems. I guess that is what SIM was alluding to in his comments. Although, my answer is IMO correct, you should ignore the next section of comments.

FYI: I posted a function that parses the JSON into Arrays and Dictionaries Function to Return a JSON Like Objects Using VBA Collections and Arrays on Code Review. It is not a replacement for JsonConverter or omegastripes’s JSON.Bas. It demonstrates that you can add JScript code to CreateObject("MSScriptControl.ScriptControl") and use it to process the JSON.