Using VBA-JSON to pull from API "error: object does not support this property or method here"?

Question

Trying to pull specific data from an API using VBA-JSON. Get the error: object does not support this property or method here | For Each key In json.Keys. Trying to figure out why.

When I try "For Each Key in json.Key" I get the error code in the title. I know this is because defender_list is an array so I need to define an object, but I'm struggling on how to create a For statement for the object (if that's what I need to do). I took the For each Key part out because I know it is wrong.

Option Explicit

Public Sub WriteOutBattleInfo()
    Dim headers(), r As Long, i As Long, json As Object, key As Variant, ws As Worksheet, defenderList As Object, monsterInfo As Object
    Set ws = ThisWorkbook.Worksheets("Sheet3")
    headers = Array("Username", "Avg BP", "Avg Level", "Opp. Address", "Player ID", "Catch Number", "Monster ID", "Type 1", "Type 2", "Gason Y/N", "Ancestor 1", "Ancestor 2", "Ancestor 3", "Class ID", "Total Level", "Exp", "HP", "PA", "PD", "SA", "SD", "SPD", "Total BP")

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.etheremon.com/api/ema_battle/get_rank_castles?v=8588587&trainer_address=0x2fef65e4d69a38bf0dd074079f367cdf176ec0de", False
        .Send
        Set json = JsonConverter.ParseJson(.ResponseText)("data")("defender_list") 'dictionary of dictionaries
    End With
    r = 2
    ws.Cells(1, 1).Resize(1, UBound(headers) + 1) = headers

Edit: tried this

Dim obj As Variant
Dim monsterInfo, obj2
Dim types, obj3
Dim ancestors, obj4
Dim totalBattleStats, obj5

For Each obj In json
    Debug.Print obj("username")
    Debug.Print obj("avg_bp")
    Debug.Print obj("avg_level")
    Debug.Print obj("player_id")

    Set monsterInfo = obj("monster_info")
    For Each obj2 In monsterInfo
        Debug.Print obj2("create_index")
        Debug.Print obj2("monster_id")

        Set types = obj("types")
        Debug.Print obj3("types")

        Debug.Print obj2("is_gason")

        Set ancestors = obj("ancestors")
        Debug.Print obj4("ancestors")

        Debug.Print obj2("class_ID")
        Debug.Print obj2("total_level")
        Debug.Print obj2("exp")

        Set totalBattleStats = obj("total_battle_stats")
        Debug.Print obj5("total_battle_stats")

        Debug.Print obj2("total_bp")

Next obj

      Cells.Select
      Selection.Columns.AutoFit



  End Sub

I want to pull specifically from "defender_list". Each "username" has 6 mons associated with it. Most importantly I want the stats from each of those mons, but I've listed everything I need pulled in headers=. The variable names for the code are "username", "avg_bp", "avg_level", "player_id" and then within "monster_info" are "create_index", "monster"id" "types" (array type1, type2), "is_gason", "ancestors" (array ancestor 1,2,3), "class_id", "total_level", "exp", total_battle_stats (array hp, pa, pd, sa, sd, spd), "total_bp".

The expected output is:

Where:
Username - "username"
Avg BP - "avg_bp"
Avg Level - "avg_level"
Player ID - "player_id"
Catch Number - "create_index"
Monster ID - "monster"id"
Type 1, Type 2 - "types" (array type1, type2)
Gason - "is_gason"
Ancestor 1, Ancestor 2, Ancestor 3 - "ancestors" (array ancestor 1,2,3)
Class ID - "class_id"
Total Level - "total_level"
Exp - "exp"
HP, PA, PD, SA, SD, SPD - "total_battle_stats" (array hp, pa, pd, sa, sd, spd)
Total BP - "total_bp"

Everything after player_ID in that list is mon specific. Each username has 6 mons which would mean 6 rows per username. The things before player_ID could just be repeated for rows 1-6 that doesn't matter.

Answer 1

You have a few confounding factors.

1) You have nested monsters within each player. You need to account for this and duplicate initial row info for these lines.

2) Due to your headers you have assumed the json structure is both regular and contains the same number of items/keys in each object within the json. This is not the case, for example, MonsterId 47023 has only one Type value and no ancestors. I use some hard coded values to set a loop based on your headers i.e. that there should be 2 Type values and 3 ancestor values. I wrap this in an On Error Resume Next On Error GoTo O to suppress the resultant error when attempting to access a non existant item. Thus a blank get entered.

Example outlier:

---

VBA:

Option Explicit   
Public Sub WriteOutBattleInfo()
    Dim headers(), json As Object, key As Variant, ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet3")
    headers = Array("Username", "Avg BP", "Avg Level", "Opp. Address", "Player ID", "Catch Number", "Monster ID", "Type 1", "Type 2", "Gason Y/N", "Ancestor 1", "Ancestor 2", "Ancestor 3", "Class ID", "Total Level", "Exp", "HP", "PA", "PD", "SA", "SD", "SPD", "Total BP")

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.etheremon.com/api/ema_battle/get_rank_castles?v=8588587&trainer_address=0x2fef65e4d69a38bf0dd074079f367cdf176ec0de", False
        .send
        Set json = JsonConverter.ParseJson(.responseText)("data")("defender_list") 'Collection of dictionaries
    End With
    Dim dict As Object, c As Long, r As Long, item As Object, key2 As Variant, results(), item2 As Long
    ReDim results(1 To json.Count * json(1)("monster_info").Count, 1 To UBound(headers) + 1) '<== +1 here if can establish what catch number is

    Dim j As Long
    For Each dict In json
        r = r + 1: c = 1
        For Each key In dict.keys
            Select Case key
            Case "username", "avg_bp", "avg_level", "address", "player_id"
                results(r, c) = dict(key)
                c = c + 1
            Case "monster_info"
                Dim monsterNumber As Long, temp(1 To 5)
                monsterNumber = 1
                For Each item In dict(key)       'collection of dictionaries               
                    If monsterNumber = 1 Then
                        For j = 1 To 5
                            temp(j) = results(r, j)
                        Next
                    Else
                        r = r + 1
                        For j = 1 To 5
                            results(r, j) = temp(j)
                        Next
                    End If
                    For Each key2 In item.keys
                        Select Case key2
                        Case "create_index", "monster_id", "is_gason", "class_id", "total_level", "exp", "total_bp"
                            results(r, c) = item(key2)
                            c = c + 1
                            Case "types" '<==expecting 2. Can get 1. Maybe different count?
                            For item2 = 1 To 2
                                On Error Resume Next
                                results(r, c) = item(key2).item(item2)
                                c = c + 1
                                On Error GoTo 0
                            Next
                            Case "ancestors" '<== expecting 3. Can get 0. Maybe different number
                            For item2 = 1 To 3
                                On Error Resume Next
                                results(r, c) = item(key2).item(item2)
                                c = c + 1
                                On Error GoTo 0
                            Next
                            Case "battle_stats"
                            For item2 = 1 To item(key2).Count
                                results(r, c) = item(key2).item(item2)
                                c = c + 1
                            Next
                        End Select
                    Next
                    c = 6: monsterNumber = monsterNumber + 1
                Next item
            End Select
        Next
    Next
    ws.Cells(1, 1).Resize(1, UBound(headers) + 1) = headers
    ws.Cells(2, 1).Resize(UBound(results, 1), UBound(results, 2)) = results
End Sub

---

Sample output:

Answer 2

This should work (for example):

Dim obj 'as variant
Dim mInfo, obj2
For Each obj in json
    Debug.Print obj("username")
    Set mInfo = obj("monster_info")
    For Each obj2 in mInfo
        Debug.Print obj2("trainer")
        Debug.Print obj2("monster_id")
    Next obj2
Next obj