Nested JSON Arrays (How to access)

[wraymac]

Any Idea on how to access (loop) through nested arrays? The VBA-JSON is able to access all the data but I have to reference the indices for each level

in this file results.items.offers - both items and offers are arrays and contain arrays (nested) github.txt

What I would like to do is be able to add the field names to excel/access ( without having address each one in code then populate the cells/table with the values.

Maybe I am missing the obvious.

[Sophist-UK]

I doubt that this functionality exists as standard but reference by name rather than by position like something that would be very useful indeed to avoid searching through the JSON every time you want something.

That said, I don't think that you can avoid nesting issues unless you are certain that names are unique in the nesting.

What we need is a VBA helper class or helper functions that would do the searching for us. The base helper functions would need to be nesting aware, but it might be possible to produce some additional functions that are recursive and avoid needing to understand about nesting (providing that the names are unique).

Perhaps this is something that @wraymac could develop as a potential enhancement to VBA-JSON for the benefit of us all.

[xeijin]

+1 this is pretty much what I'm looking for https://github.com/VBA-tools/VBA-Web/issues/302

[zgrose]

Two examples of looping through arrays of known depth. Unclear to from the OP what exactly the issue would be.

Public Sub LoopThroughArrays()

    Dim myNestedArraysJson As String
    myNestedArraysJson = "{ ""table"":[[{""coord"":""(0,0)""},{""coord"":""(0,1)""}], [{""coord"":""(1,0)""},{""coord"":""(1,1)""}]] }"

    Dim jObj As Dictionary
    Set jObj = ParseJson(myNestedArraysJson)

    Debug.Print "Method 1:"
    Dim i As Long, j As Long
    Dim rowsCollection As Collection
    Dim colsCollection As Collection

    Set rowsCollection = jObj("table")
    For i = 1 To rowsCollection.Count
        Set colsCollection = rowsCollection(i)
        For j = 1 To colsCollection.Count
            Debug.Print colsCollection(j)("coord")
        Next j
    Next i

    Debug.Print "Method 2:"
    Dim v1 As Variant, v2 As Variant

    For Each v1 In jObj("table")
        For Each v2 In v1
            Debug.Print v2("coord")
        Next v2
    Next v1

End Sub

[aholden10]

Here's a trivial question that I have been searching for an answer to and cannot find anywhere...

How do I reference a key as a variable rather than a literal in the code above?

For example, in the code you have this line:

For Each v1 In jObj("table")

How would I make it reference a variable called (for instance) keyName, so instead of saying jObj("table") I have tried to make the string variable headerLookup = "table" and then use keyName = "(" & headerLookup & ")" then reference the original code like this: For Each v1 In jObj & keyName

But it does not work :-(

What am I doing wrong?

[zgrose]

You're overthinking the problem. I've added to the example here:

Public Sub LoopThroughArrays()

    Dim myNestedArraysJson As String
    myNestedArraysJson = "{ ""table"":[[{""coord"":""(0,0)""},{""coord"":""(0,1)""}], [{""coord"":""(1,0)""},{""coord"":""(1,1)""}]] }"

    Dim jObj As Dictionary
    Set jObj = ParseJson(myNestedArraysJson)

    Debug.Print "Method 1:"
    Dim i As Long, j As Long
    Dim rowsCollection As Collection
    Dim colsCollection As Collection

    Set rowsCollection = jObj("table")
    For i = 1 To rowsCollection.Count
        Set colsCollection = rowsCollection(i)
        For j = 1 To colsCollection.Count
            Debug.Print colsCollection(j)("coord")
        Next j
    Next i

    Debug.Print "Method 2:"
    Dim v1 As Variant, v2 As Variant

    For Each v1 In jObj("table")
        For Each v2 In v1
            Debug.Print v2("coord")
        Next v2
    Next v1

    Debug.Print "Using variables instead of literals:"
    Dim propertyName1 As String, propertyName2 As String

    propertyName1 = "table"
    propertyName2 = "coord"

    For Each v1 In jObj(propertyName1)
        For Each v2 In v1
            Debug.Print v2(propertyName2)
        Next v2
    Next v1

End Sub

[aholden10]

Wow, thank you very, very much! This works great - I was definitely overthinking.

By way of a follow-up though as I try and take your solution a step further, what I had been trying to do was concatenate the entire string to feed to the parser in a spreadsheet cell to produce something like this: propertyname1 = "(1)("bodyLines")(1)("value")" which I could feed to jObj as a single property (so yes that value is within an array within a list all encapsulated in an array)!

Perhaps all the properties have to be fed in separately?

Thanks again for your first reply, that gets me going again - I was stalled...

[zgrose]

propertyname1 = "(1)("bodyLines")(1)("value")" is definitely not going to work.

That would be like trying to "Range("A1").Font.Name" as a "property" of a worksheet. You need to work with one object at a time, e.g. obj(1)("foo")(2)("bar")

[aholden10]

Ah, got it - that actually makes sense!

Thank you again for your great help.

[orooro]

Hi, I have tried the routine with this Json but I get error in "line":5 I think it is because of the array "failure_reason":{"type": "validation", "fields":["email"]},

Option Explicit

Public Sub IterateOverACollection()

Dim jsonString As String
jsonString = "[{""row"":1, ""a"":""0007"", ""b"":""Blue"", ""c"":""2008""}, {""row"":2, ""a"":null, ""b"":"""", ""c"":""1709""}]"

Dim myCollection As Collection
Set myCollection = ParseJson(jsonString)

Dim myDictionary As Dictionary
Dim i As Long, j As Long

Dim StartCell As Range
Set StartCell = ActiveSheet.Range("A1")

Set myDictionary = myCollection(1)
For j = 1 To myDictionary.Count
    StartCell.Offset(0, j - 1) = myDictionary.Keys(j - 1)
    StartCell.Offset(0, j - 1).Font.Bold = True
Next j

For i = 1 To myCollection.Count
    Set myDictionary = myCollection(i)
    For j = 1 To myDictionary.Count
       StartCell.Offset(i, j - 1) = myDictionary.Items(j - 1)
    Next j
Next i

End Sub

could you help me on this? could i do it with a nested loop and put the header and the results in a sheet? thank you very much in advance

Part of Json {"line":5, "email": "institutcastelldelquer @gmail. com", "sms_phone":null, "result": "failure", "failure_reason":{"type": "validation", "fields":["email"]} the full json is this[{"line":1, "email":"", "sms_phone":null, "result": "failure", "failure_reason": "header", "created_at": "2024-02-13T11:01:19:000Z"},

Json complete

[{"line":1,"email":"","sms_phone":null,"result":"failure","failure_reason":"header","created_at":"2024-02-13T11:01:19.000Z"},{"line":2,"email":"insvoltrera@xtec.cat","sms_phone":"","result":"success","failure_reason":null,"created_at":"2024-02-13T11:01:19.000Z"},{"line":3,"email":"amateo@presentarenys.net","sms_phone":"","result":"success","failure_reason":null,"created_at":"2024-02-13T11:01:19.000Z"},{"line":4,"email":"a8034187@xtec.cat","sms_phone":"","result":"success","failure_reason":null,"created_at":"2024-02-13T11:01:19.000Z"},{"line":5,"email":"institutcastelldelquer @gmail.com","sms_phone":null,"result":"failure","failure_reason":{"type":"validation","fields":["email"]},"created_at":"2024-02-13T11:01:23.000Z"},{"line":6,"email":"a8030224@xtec.cat","sms_phone":"","result":"success","failure_reason":null,"created_at":"2024-02-13T11:01:26.000Z"},{"line":7,"email":"veronica.cardenas@pereviver.cat","sms_phone":"","result":"success","failure_reason":null,"created_at":"2024-02-13T11:01:26.000Z"}]

[zgrose]

You get what error on which of your lines? If you want to re-format your question with a complete code sample with the correct JSON in the variables might help, too.

[aholden10]

As you know, "line":5 is failing because of the embedded arrays.  It's a difficult problem because you are taking a data series that has these arrays and trying to fit it into a two-dimensional spreadsheet.

It can be done, however before you start coding you will need to decide how to represent the data on the spreadsheet. The time I did it I ended up with a jagged spreadsheet, with each line having its own headings...it worked but there was probably (definitely) a better way. Let us know what you come up with! 

On Wednesday, February 21, 2024 at 09:59:51 AM EST, Zoltan Grose ***@***.***> wrote:  

You get what error on which of your lines? If you want to re-format your question with a complete code sample with the correct JSON in the variables might help, too.

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.Message ID: @.***>