Accessing JSON array within JSON object in VBA Excel using VBA-JSON

Question

I am working on VBA macros to access live data from a website through API calls. I am using VBA-JSON library for it. There are mainly two scenarios when fetching data:

Scenario #1 (we can see data is a JSON array):

{
  "success": true,
  "data": [
    {
      "id": 4,
      "creator_user_id": {
        "id": 162,
        "name": "ASD",
        "email": "email id"

      },
      "user_id": {
        "id": 787878,
        "name": "XYZ",
        "email": "email id"
      },
      ...
    }
  ]
}

In the scenario 1 I can fetch values within data array by using For Each loop in VBA Excel through VBA-JSON library.

Scenario #2:

{
  "success": true,
  "data": {

    "id": 123,
    "name": "ABC",

    "options": [
      {
        "id": 119,
        "label": "Name1"
      },
      {
        "id": 120,
        "label": "Name2"
      }
    ],
    "mandatory_flag": false
  }
}

But in 2nd scenario I can not access data within data array, because JSON data array is within JSON object. For example I want the value of id: 120 which must return Name2, thus I want to access value of label which will return Name2.

I tried many ways, but can not get it. Please anyone can tell me how this can be done in VBA Excel? Any help appreciated.

Could you please post the code or share the link to VBA-JSON library you are using? Post the code you have tried also, note proper formatting. — omegastripes
– omegastripes, Commented Mar 1, 2017 at 10:01

drec4s · Accepted Answer · 2018-02-28 14:32:06Z

1

Using the VBA-JSON library, you can access the value you want by calling:

Set Parsed = JsonConverter.ParseJson(<yourjsonstring>)
Debug.Print Parsed("data")("options")(2)("label")

answered Feb 28, 2018 at 14:32

drec4s

8,1178 gold badges35 silver badges56 bronze badges

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

Collectives™ on Stack Overflow

Accessing JSON array within JSON object in VBA Excel using VBA-JSON

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related