how to access to the data after parsing JSON data in VBA by JsonParse?

Question

I would like to parse a JSON data in VBA and check how many books there are. With the hint from GPT, I use this code to parse JSON,

Public Function JsonParse(jsonStr As String) As Object
    With CreateObject("HTMLFile")
        Set Window = .parentWindow
        Window.execScript "var json = " & jsonStr, "JScript"
        Set JsonParse = Window.json
    End With
End Function

It works fine at first, showed in attached pic, for example, I can get the person's name by debug.print jsonObject.person.name

VBA Locals pic

However, if there are some sub structures like "book", it turns complicated. I tried debug.print jsonObject.book0.title, jsonObject.book[0].title,jsonObject.book.0.title,jsonObject.book(0).title none of them work. UBound() doesn't work neither, error message shows that it needs an array...

could you kindly please teach me the syntax on this issue? I would like to know the count of books in this Json so that I could print each title of books.

Thanks in advance!

Here is the demo JSON below:

{
    "person": {
        "name": "Bob",
        "age": 30,
        "address": {
            "city": "New York",
            "zip": 10001
        }
    },
    "book": [
        {
            "title": "The Catcher in the Rye",
            "author": "J.D. Salinger",
            "price": 19.99
        },
        {
            "title": "The Lord of the Rings",
            "author": "J.R.R. Tolkien",
            "price": 29.99
        },
        {
            "title": "Harry Potter and the Philosopher's Stone",
            "author": "J.K. Rowling",
            "price": 14.99
        }
    ]
}

Answer 1

Prefacing this by noting that this approach to json parsing can open you up to certain security risks if you can't trust the content of the text you're parsing.

You can re-work your original code a little to make it work. The issue with this approach is that certain json object property names ("name" is an example) can't be accessed when called from VBA.

You can instead keep the parsed json object cached inside the HTMLFile object, along with a function to return information from that json object.

Public Function JsonParse(txt As String)
    Static html As Object
    If txt = "clear" Then 'resets the state
        Set html = Nothing
        Exit Function
    End If
    If html Is Nothing Then 'sets up the JSON
        Set html = CreateObject("HTMLFile")
        With html
            .parentWindow.execscript "var json = " & txt, "JScript"
            'a function to return properties of the json object
            .parentWindow.execscript "var parse = function(s){return eval('json.' + s);}", "JScript"
        End With
    Else
        'call the "parse" function on the json object
        JsonParse = html.parentWindow.eval("parse('" & txt & "')")
    End If
End Function

Sub Tester()
    Dim numBooks, n
    
    JsonParse "clear"  'clear any previous parsed json
    JsonParse [A1]     'first call after "clear" parses the JSON

    'any further calls evaluate properties of the parsed json object
    Debug.Print "Person:" & vbLf & "----------------"
    Debug.Print JsonParse("person.name"), JsonParse("person.age")
    Debug.Print JsonParse("person.address.city"), JsonParse("person.address.zip")
    
    numBooks = JsonParse("book.length") 
    Debug.Print "Books:" & vbLf & "----------------"
    For n = 1 To numBooks
        Debug.Print n, JsonParse("book[" & n - 1 & "].title") '-1 because js arrays are zero-based
        Debug.Print n, JsonParse("book[" & n - 1 & "].author")
        Debug.Print n, JsonParse("book[" & n - 1 & "].price")
    Next

End Sub

Answer 2

• JsonConverter is a good tool to extract json data

https://github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas

Option Explicit
Sub GetBooks()
    Dim jsonStr As String
    Dim JsonParse As Object
    Dim booksDict As Object
    Dim i As Integer
    jsonStr = [a1] ' JSON string is in cell A1
    Set JsonParse = JsonConverter.ParseJson(jsonStr)
    If JsonParse.Exists("book") Then
        Set booksDict = JsonParse("book")
        Debug.Print "There are " & booksDict.Count & " books"
        For i = 1 To booksDict.Count
            Debug.Print "Book " & i & " Price: " & booksDict(i)("price")
        Next i
    Else
        Debug.Print "No 'book' found in JSON."
    End If
End Sub

Output:

There are 3 books
Book 1 Price: 19.99
Book 2 Price: 29.99
Book 3 Price: 14.99

Answer 3

In powerquery, count of books

//returns 3 for 3 books
let Source = List.Count(Json.Document(File.Contents("C:\temp2\a.json"))[book])
in  Source

or, a list of books

let Source = Json.Document(File.Contents("C:\temp2\a.json")),
books= Table.Combine(List.Transform(Source[book], each Table.PromoteHeaders(Table.Transpose(Record.ToTable(_)))))
in books