how to Parse multi level json with vba?

Question

how would I parse this Json String?

'{ ' "territory": { ' "RSC": { ' "sector": 1, ' "size": 3, ' "density": 2, ' "slots": 18, ' "daily_respect": 113, ' "faction": 13784, ' "coordinate_x": "3199.2", ' "coordinate_y": "2828.32", ' }, ' "HSC": { ' "sector": 1, ' "size": 4, ' "density": 3, ' "slots": 26, ' "daily_respect": 197, ' "faction": 13784, ' "coordinate_x": "3457.41", ' "coordinate_y": "2785.98", ' }, ' "JSC": { ' "sector": 1, ' "size": 2, ' "density": 3, ' "slots": 8, ' "daily_respect": 141, ' "faction": 13784, ' "coordinate_x": "3479.67", ' "coordinate_y": "2819.67", ' }, ' "NTC": { ' "sector": 1, ' "size": 3, ' "density": 2, ' "slots": 21, ' "daily_respect": 113, ' "faction": 13784, ' "coordinate_x": "3416.45", ' "coordinate_y": "2868.95", ' }, ' "OTC": { ' "sector": 1, ' "size": 4, ' "density": 3, ' "slots": 24, ' "daily_respect": 197, ' "faction": 13784, ' "coordinate_x": "3417.68", ' "coordinate_y": "2904.79", ' }, ' "QTC": { ' "sector": 1, ' "size": 3, ' "density": 2, ' "slots": 12, ' "daily_respect": 113, ' "faction": 13784, ' "coordinate_x": "3395.34", ' "coordinate_y": "3039.22", ' }, ' "RTC": { ' "sector": 1, ' "size": 3, ' "density": 2, ' "slots": 16, ' "daily_respect": 113, ' "faction": 13784, ' "coordinate_x": "3366.33", ' "coordinate_y": "3012.11", ' }, ' "TTC": { ' "sector": 1, ' "size": 4, ' "density": 3, ' "slots": 28, ' "daily_respect": 197, ' "faction": 13784, ' "coordinate_x": "3244.33", ' "coordinate_y": "2799.8", ' }, ' "UTC": { ' "sector": 1, ' "size": 3, ' "density": 2, ' "slots": 12, ' "daily_respect": 113, ' "faction": 13784, ' "coordinate_x": "3278.81", ' "coordinate_y": "2756.83", ' "racket": { ' "name": "Truck Stop II", ' "level": 2, ' "reward": "20x Can of Munster daily", ' "created": 1604663824, ' "changed": 1645588625, ' }, ' },

Dim response As Object
Set response = JsonConverter.ParseJson(request.responseText)
Dim iCounter            As Integer
Dim RCounter            As Integer
Dim CCounter            As Integer
iCounter = 1
Dim territory As Dictionary
Set territory = response("territory")
Dim Block_id As Variant
For Each Block_id In territory

Debug.Print Block_id

Sheet7.Range("a115").Offset(iCounter, 0).Value = Block_id
    For Each sector In Block_id
Sheet7.Range("a115").Offset(iCounter, 1).Value = sector
        Next sector
iCounter = iCounter + 1
Next Block_id

Block Id is printed correctly, but not "sector". I would have to the same for size,density, slots etc etc, with another level for "racket" and its items. I have been looking into dictionaries and collections, but cannot figure it out. please help!

If it is an option, you can do this pretty easily in Power Query — Ron Rosenfeld, Feb 27 '22 at 15:04
The JSON string isn't valid. Did you add the extra single quotes? — TinMan, Feb 27 '22 at 15:31

score 2 · Answer 1 · answered Feb 27 '22 at 10:57

So, after reformatting and fixing up your JSON, I've managed to provide a basic example for how to process the data.

I haven't built any recursion into this but depending on how dynamic you want it to be, you'll need to do some further work.

I copied your JSON into a text file and then read it and traversed the data. This is an example of how to read all of that data and write it out to the Immediate window in the VBA editor.

I used a mix of For Each and For to show you how it can work.

Public Sub Test()
    Dim objFSO As Scripting.FileSystemObject, objStream As Scripting.TextStream
    Dim strJSON As String, i As Long, x As Long, y As Long
    
    Dim objData As Scripting.Dictionary
    Dim objTerritories As Scripting.Dictionary, strTerritory As Variant
    Dim objTerritory As Scripting.Dictionary, strProperty As Variant
    Dim varPropertyValue As String
    Dim objRacket As Scripting.Dictionary
    
    Set objFSO = New Scripting.FileSystemObject
    Set objStream = objFSO.OpenTextFile("c:\temp\json.txt")
    
    strJSON = objStream.ReadAll
    
    objStream.Close
    
    Set objStream = Nothing
    Set objFSO = Nothing
    
    Set objData = JsonConverter.ParseJson(strJSON)
    Set objTerritories = objData("territory")
    
    For Each strTerritory In objTerritories.Keys
        Debug.Print strTerritory
        
        Set objTerritory = objTerritories(strTerritory)
        
        For Each strProperty In objTerritory.Keys
            Select Case TypeName(objTerritory(strProperty))
                Case "Dictionary"
                    Set objRacket = objTerritory(strProperty)
                    Debug.Print "... " & strProperty & ":"
                    
                    For y = 0 To objRacket.Count - 1
                        strProperty = objRacket.Keys(y)
                        
                        varPropertyValue = objRacket(strProperty)
                        Debug.Print "...... " & strProperty & " = " & varPropertyValue
                    Next
                    
                Case Else
                    varPropertyValue = objTerritory(strProperty)
                    Debug.Print "... " & strProperty & " = " & varPropertyValue
                    
            End Select
        Next
    Next
End Sub

I can't give you an exact answer because I don't know what you want to do with it but you should be able to adapt this as need be.

For reference purposes, this was the JSON once reformatted and beautified ...

{
  "territory": {
    "RSC": {
      "sector": 1,
      "size": 3,
      "density": 2,
      "slots": 18,
      "daily_respect": 113,
      "faction": 13784,
      "coordinate_x": "3199.2",
      "coordinate_y": "2828.32"
    },
    "HSC": {
      "sector": 1,
      "size": 4,
      "density": 3,
      "slots": 26,
      "daily_respect": 197,
      "faction": 13784,
      "coordinate_x": "3457.41",
      "coordinate_y": "2785.98"
    },
    "JSC": {
      "sector": 1,
      "size": 2,
      "density": 3,
      "slots": 8,
      "daily_respect": 141,
      "faction": 13784,
      "coordinate_x": "3479.67",
      "coordinate_y": "2819.67"
    },
    "NTC": {
      "sector": 1,
      "size": 3,
      "density": 2,
      "slots": 21,
      "daily_respect": 113,
      "faction": 13784,
      "coordinate_x": "3416.45",
      "coordinate_y": "2868.95"
    },
    "OTC": {
      "sector": 1,
      "size": 4,
      "density": 3,
      "slots": 24,
      "daily_respect": 197,
      "faction": 13784,
      "coordinate_x": "3417.68",
      "coordinate_y": "2904.79"
    },
    "QTC": {
      "sector": 1,
      "size": 3,
      "density": 2,
      "slots": 12,
      "daily_respect": 113,
      "faction": 13784,
      "coordinate_x": "3395.34",
      "coordinate_y": "3039.22"
    },
    "RTC": {
      "sector": 1,
      "size": 3,
      "density": 2,
      "slots": 16,
      "daily_respect": 113,
      "faction": 13784,
      "coordinate_x": "3366.33",
      "coordinate_y": "3012.11"
    },
    "TTC": {
      "sector": 1,
      "size": 4,
      "density": 3,
      "slots": 28,
      "daily_respect": 197,
      "faction": 13784,
      "coordinate_x": "3244.33",
      "coordinate_y": "2799.8"
    },
    "UTC": {
      "sector": 1,
      "size": 3,
      "density": 2,
      "slots": 12,
      "daily_respect": 113,
      "faction": 13784,
      "coordinate_x": "3278.81",
      "coordinate_y": "2756.83",
      "racket": {
        "name": "Truck Stop II",
        "level": 2,
        "reward": "20x Can of Munster daily",
        "created": 1604663824,
        "changed": 1645588625
      }
    }
  }
}

how to Parse multi level json with vba?

1 Answers1