How to parse nested JSON in VBA

Question

I have a site that I need to connect to where I'd grab a JSON file and then parse out the information I need. But I'm having a bit of a hard time as the data is nested... Could someone please assist? I'm using the VBAJSON library to try to parse this..

JSON file looks something like this:

{
    record: [
        {
            Name: "Bob",
            Created: "2020/06/18"
        },
        {
            Age: "52",
            TableName: "AGE"
        },
        {
            School: "Woodside",
            TableName: "School"
        }
        {
            Phone: "Iphone",
            TableName: "Phone"
        },
    ]
}

'this is an attempt to start getting the data and parse it but I'm lost...

Sub getjson2()
Dim hReq As New WinHttpRequest
Dim strURL As String, JSON As Dictionary

strURL = "testsite.com/abcd"

hReq.Open "GET", strURL, False

hReq.Send

Dim response As String
    response = hReq.ResponseText
    

Set JSON = JsonConverter.ParseJson(response)

End Sub

Stavros Jon · Accepted Answer · 2020-06-23 08:15:48Z

First off, the Json you've posted is not valid. More specifically it's missing a comma right after the 3rd element of the array, there's a comma that shouldn't be there right after the 4th element of the array and it's also missing quotes from all keys. It should look like so:

{
  "record": [
    {
      "Name": "Bob",
      "Created": "2020/06/18"
    },
    {
      "Age": "52",
      "TableName": "AGE"
    },
    {
      "School": "Woodside",
      "TableName": "School"
    },
    {
      "Phone": "Iphone",
      "TableName": "Phone"
    }
    
  ]
}

Apart from that, I see you're using VBA-JSON parser so I assume you have added a reference to the Microsoft Scripting Runtime library.

Having said all the above, parsing the json is fairly easy. Here's how to get the value of the School key for example:

Debug.Print JSON("record")(3)("School")

This will print Woodside in your immediate window. You can get the rest of the values by adjusting the code accordingly.

Note:

I always recommend using the standard JSON format. However, If this is out of your control because that's how the response you're getting is formatted, there is a way to circumvent missing quotes:

JsonConverter.JsonOptions.AllowUnquotedKeys = True

Rogerio Nascimento · Accepted Answer · 2020-06-22 17:13:12Z

0

There is a solution available for that in Github. https://github.com/VBA-tools/VBA-JSON

This solution is basically adding functions that convert to and from JSON.

answered Jun 22, 2020 at 17:13

Rogerio Nascimento

3392 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to parse nested JSON in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related