Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

JSON Data pulling with nested arrays

Potentially been asked before, however I’m after a quick solution to a specific problem with these nested arrays! I have the following data being pulled using Excel into a text file from a url (edited due to confidential info) The data is as follows:

{
  "ret": {
    "getDetailsOutput": {
      "searchTime": "1717399860000",
      "startTime": "1717316816470",
      "endTime": "1717403216470",
      "cptList": null,
      "routeDispatchDetailMap": {
        "Lane1": {
          "allSfs": [],
        },
        "Lane2": {
          "allSfs": [],
        },
        "Lane3": {
          "allSfs": [],
        },
        "Lane4": {
          "allSfs": [],
        },
        "Lane5": {
          "allSfs": [
            "Nested1",
            "Nested2",
            "Nested3",
            "Nested4"
          ],
        },
        "Lane6": {
          "allSfs": [
            "Nested1",
            "Nested2"
          ],
        },
        "Lane7": {
          "allSfs": [
            "Nested1",
            "Nested2",
            "Nested3",
            "Nested4",
            "Nested5",
            "Nested6"
          ],
        },
      }
    }
  },
  "ok": true,
  "message": "success"
}

I am currently toying with following VBA code to access the Nested values in and the Lane arrays, under the allSfs section of each of the lanes. Sometimes these have multiple values, sometimes they are empty:

Set J = ParseJson(H.responseText)
Set J = J("ret")("getDetailsOutput")("routeDispatchDetailMap")
write_to_text H.responseText

WS.Range("A1:G200").Clear

i = 0

On Error Resume Next
For Each Data In J
WS.[A2].Offset(i) = Data
    Set X = WS.[A2].Offset(i)
    For Each X In J("ret")("getDetailsOutput")("routeDispatchDetailMap")(WS.[A2].Offset(i).Value)(0)
    Debug.Print WS.[A2].Offset(i).Value
        WS.[A2].Offset(i) = X
    i = i + 1
    Next X
i = i + 1

Next Data

My end game goal is to have the list of Nested Values in column A and then the respective Lane name in column B…. as later on in my plans I will need to use a VLOOKUP formula to access the Lane, using the specific Nested value, if that makes any sense? I’ve tried quite a lot of things but I just can’t seem to access the Nested values… Any advice? Thanks in advance

Tags:

2

Answers


  1. 0 
    Option Explicit

Sub demo()

    ' load json from text file
    Dim fso As Object, ts As Object, s As String
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.OpenTextFile("78569173.json")
    s = ts.ReadAll
    ts.Close

    ' load from url
    's = H.responseText
    'write_to_text s
    
    Dim J As Object, ws As Worksheet, lane, v, r As Long
    Set J = ParseJson(s)
    Set J = J("ret")("getDetailsOutput")("routeDispatchDetailMap")
    
    Set ws = ThisWorkbook.Sheets(1)
    With ws
        .Range("A1:G200").Clear
        For Each lane In J.Keys
            For Each v In J(lane)("allSfs")
                r = r + 1
                .Cells(r, 1) = v
                .Cells(r, 2) = lane
            Next
        Next
    End With
    MsgBox r & " rows written to " & ws.Name, vbInformation
  
End Sub
    Login or Signup to reply.
  2. 0

    When I deal with complex nested structures, I usually split the complexity by defining variables for every level – this makes it much easier to read and debug (and no, this doesn’t slow down the code).

    I ended up with something like this:

    ws.Range("A2").CurrentRegion.Clear
row = 2
Dim ret As Dictionary
Set ret = json("ret")
Dim details As Dictionary
Set details = ret("getDetailsOutput")
Dim detailMap As Dictionary
Set detailMap = details("routeDispatchDetailMap")
Dim laneName As Variant, lane As Dictionary
For Each laneName In detailMap.Keys
    Set lane = detailMap(laneName)
    Dim allSfs As Collection
    Set allSfs = lane("allSfs")
    Dim sfsEntry As Variant
    For Each sfsEntry In allSfs
        ws.Cells(row, 1) = sfsEntry
        ws.Cells(row, 2) = laneName
        row = row + 1
    Next
Next

    Using the debugger and the locals window (View->Locals), it shows step by step the content of the JSon. It reveals for example that "arrays" are stored in Collections.

    Note: I assume that you use the JSonConverter (https://github.com/VBA-tools/VBA-JSON). You should mention that in your question. My version didn’t like the commas that you have behind the array "allSfs": [], and I had to remove them manually.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search