Determine if Nested Key Exists in JSON Response - VBA

Question

I'm having trouble parseing a JSON response into Excel. The issue is due to null value in one of the nested arrays. I have no problem reading the value into the file when it exists, but I am having difficulty putting a check to verify that it does exist.

I can access the id value of field2, but if field2 comes back as null, it throws an error. I've tried a few different ways to check if id of field2 exists, with no success

         For Each Value In JsonObject("value")
        ws.Cells(rowindex, 21) = Value("value1")("id")
        For Each Item In Value("field1")
            If field1.Exists("field2")("id") Then
            'If field1.("field2").Exists("id") Then
                ws.Cells(rowindex, 22) = field1("field2")("id")
            End If
            ws.Cells(rowindex, 25) = field1("config")("id")
        Next
        rowindex = rowindex + 1
 Next

and the JSON file

    {
 "value": [
  {
   "value1": {
    "id": "123",
   },
   "field1": [
    {
     "config": {
      "id": "131",
     },
     "field2": {
      "id": "a594c0fc-6ddb-64da-b0e8-c544f2bbbe3e",
     }
    },{
     "config": {
      "id": "320",
     },
     "field2": {
      "id": "a594c0fc-6ddb-64da-b0e8-c544f2bbbe3e",
     }
    }]
  },{
   "value1": {
    "id": "456",
   },
   "field1": [
    {
     "config": {
      "id": "131",
     },
     "field2": null
    },{
     "config": {
      "id": "320",
     },
     "field2": null
    }]
  }],
 "count": 2
}

Scott Holtzman · Accepted Answer · 2020-07-16 15:18:07Z

1

Something like this (untested):

For Each Item In Value("field1")
    If item = "field2" Then
       If Not IsNull(item("field2")("id")) Then
          '....
       End If
    End If
Next

answered Jul 16, 2020 at 15:18

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user6767022 Over a year ago

This got me on the right track. I only needed one If Statemment - If Not IsNull(field1("tester")) Then.

Collectives™ on Stack Overflow

Determine if Nested Key Exists in JSON Response - VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related