Nested JSON VBA (records and fields)

Question

I have this JSON file but I can't figure out if it's nested or what, if yes should I use collections? My goal is to parse this to an Excel sheet with VBA. So far I've been only able to parse the names. Any useful links for this type of data? I only found normal JSON but nothing like this. I'm new to JSON but have to do it during my internship.

Sample JSON:

{"records":[{"id":"rec1B74TQVtWwU6cW","fields":{"Name":"compriband","Materiaux":["recPqxRrg3tFC5o6T"]},"createdTime":"2021-03-07T18:22:47.000Z"},{"id":"rec3ZdAlSQhXCVG4c","fields":{"Name":"velux","Materiaux":["recig1Rh8WFpqe0wD","recAha2bQ5BTNED9V","recWAj3FZRsPj65Gz","recfv8V2t0Pje2Llg"]},"createdTime":"2021-03-07T18:27:27.000Z"}

My code attempt:

Private Sub CommandButton1_Click()

Dim W As Worksheet
Set W = ActiveSheet

'Read column names from row 1. Should match Airtable column names. No empty columns.
Dim fields As String
colCount = 2
Do Until IsEmpty(W.Cells(2, colCount))
fields = fields & "&fields[]=" & W.Cells(2, colCount).Value
colCount = colCount + 1
Loop

'Get the data from airtable

Dim http As New WinHttpRequest
Dim resp As String
Dim url As String

url = "https://api.airtable.com/v0/appY6Wo3AmLHqHkjr/categorie?api_key=key_here" & fields
http.Open "GET", url, False
http.Send
Debug.Print "Resultats " + CStr(http.ResponseText)
Dim json As Object
Set json = JsonConverter.ParseJson(http.ResponseText)

respRecord = 1
On Error GoTo Exit_Loop

Do Until json("records")(respRecord)("fields")(W.Cells(1, 1).Value) = ""

For respCol = 1 To colCount - 1
    cellValue = json("records")(respRecord)("fields")(W.Cells(1, respCol).Value)
    W.Cells(respRecord + 1, respCol).Value = cellValue
Next
respRecord = respRecord + 1
Loop

Exit_Loop:

End Sub

Welcome to SO! It would be really useful if you could post the code that you've tried so far and tell us about the problems that you're having with it. — Stavros Jon, Apr 07 '21 at 08:36
It's not clear why you think this JSON is problematic or what you expect "normal" JSON to look like. Of course it's nested; that's pretty much an identifying feature of most JSON out there. — tripleee, Apr 07 '21 at 08:47
@triplee no need to be mean i said i was a beginner , whats troubeling me is records [ "id" ,...] should i declare records are a collection an array or what ? i dont see what type is records . — Chayma B's, Apr 07 '21 at 08:50
@ChaymaB's you haven't done bad so far. The JSON you posted however is invalid you're missing a `]}` in the very end of the string. I suppose this was lost during copy-paste and the request you're sending is returning the correct string, right? — Stavros Jon, Apr 07 '21 at 08:50
I can see no "mean", only a request for clarification. You seem to assume that JSON would be formatted into records and fields in a matrix, but that's not how JSON is usually designed. — tripleee, Apr 07 '21 at 08:51
@stavrosJon yes its at the end of the whole file to close records from what i understood — Chayma B's, Apr 07 '21 at 08:52
https://stackoverflow.com/a/65338582/874188 is for Python but might help you understand what to expect. — tripleee, Apr 07 '21 at 08:52

Stavros Jon · Accepted Answer · 2021-04-07T10:57:42.850

As I said in the comments you haven't done bad so far. You are missing however some degree of understanding in how the JSON is structured.

A tool like this might help you understand the structure.

Also, I see that you have used the VBA JSON parser . The examples in the documentation should help you gain a better understanding of how to parse a JSON string.

Having said that, I will point out some stuff that should help you:

Your JSON looks like so:

Basically you have an array called records, designated by [] consisting of two items 0 and 1. These 2 items are nested JSONs. So your initial JSON is an array of JSONs.

To explicitly access the individual items of the array you can do this:

json("records")(1)
json("records")(2)
...
json("records")(n)

Now, each item consists of elements of its own. These elements could be arrays, nested jsons etc. Your items consist of two parameters id and createdTime and a nested JSON fields.

To go furhter into each item you can use this syntax:

json("records")(1)("nameOfParameter")

or

json("records")(1)("nameOfNestedJSON")

So if you wanted the 1st items id you would do:

Debug.Print json("records")(1)("id")

To loop through all the items of the array you can do this:

Dim item As Object
For Each item In json("records")
    Debug.Print item("id")
Next item

The nested JSON fields consists of two parameters Name and createdTime and an array Materiaux.

You can get the parameters like so:

Debug.Print json("records")(1)("fields")("Name")

And of course you can also do:

Dim item As Object
For Each item In json("records")
    Debug.Print item("fields")("Name")
Next item

You can go even deeper to access the elements of the Materiaux array:

Debug.Print json("records")(2)("fields")("Materiaux")(1)

And even loop through them:

Dim arrayItem As Variant
For Each arrayItem In json("records")(2)("fields")("Materiaux")
    Debug.Print arrayItem
Next arrayItem

Nested JSON VBA (records and fields)

1 Answers1