Traversing Nested JSON using VBA

Question

I have a JSON I am trying to parse in VBA. The JSON looks similar to the following:

{
    "participantEligibilityResults": [
        {
            "eligibilityResult": {
                "participantId": "HSA92a",
                "clientId": "NIRCCCONFIG",
                "environment": "CONFIG",
                "errorReason": null,
                "previousEvent": {
                    "eventDate": "2019-01-01",
                    "eventReason": "7",
                    "eligibilityDetails": [
                        {
                            "standardBenefitAreaId": "SPLIFE",
                            "benefitOptionId": "1XPay",
                            "coverageLevelId": "PPSP",
                            "employeeMonthlyCost": 216.67,
                            "employerMonthlyCost": 0.0,
                            "benefitProgramId": "ProgH"
                        },
                        {
                            "standardBenefitAreaId": "SPLIFE",
                            "benefitOptionId": "NoCoveragePay",
                            "coverageLevelId": null,
                            "employeeMonthlyCost": 0.0,
                            "employerMonthlyCost": 0.0,
                            "benefitProgramId": "ProgH"
                        }
                    ],
                    "dependents": []
                },
                "currentEvent": {
                    "eventDate": "2020-03-14",
                    "eventReason": "5",
                    "eligibilityDetails": [
                        {
                            "standardBenefitAreaId": "BASICCHLIFE",
                            "benefitOptionId": "BCHWaive",
                            "coverageLevelId": null,
                            "employeeMonthlyCost": 0.0,
                            "employerMonthlyCost": 0.0,
                            "benefitProgramId": "ProgH",
                            "beneficiaryCollection": "Not Applicable",
                            "maxCoverageAmount": 0.0,
                            "minCoverageAmount": 0.0,
                            "coverageAmount": 0.0,
                            "preTax": true,
                            "postTax": false,
                            "userDefinedTaxability": false,
                            "numberOfPayPeriods": 52,
                            "payperiodsRemaining": 42.0
                        },
                        {
                            "standardBenefitAreaId": "DENTAL",
                            "benefitOptionId": "DentalPPO",
                            "coverageLevelId": "PPFAM2",
                            "employeeMonthlyCost": 29.17,
                            "employerMonthlyCost": 125.0,
                            "benefitProgramId": "ProgH",
                            "beneficiaryCollection": "Not Applicable",
                            "maxCoverageAmount": 0.0,
                            "minCoverageAmount": 0.0,
                            "preTax": true,
                            "postTax": false,
                            "userDefinedTaxability": false,
                            "numberOfPayPeriods": 52,
                            "payperiodsRemaining": 42.0
                        }
                    ],
                    "dependents": [
                        {
                            "fullName": "Allison Drew ",
                            "dependentId": "5d82c4bf-609d-4c2f-8c1b-7d8fdd8b9fde",
                            "relationshipType": "Spouse",
                            "birthDate": "1980-01-01",
                            "activeIndicator": true,
                            "approvedIndicator": true,
                            "studentIndicator": false,
                            "coverages": [
                                {
                                    "standardBenefitAreaId": "DENTAL",
                                    "benefitOptionId": "NoCoverageDental",
                                    "dependentCoverageRequired": false,
                                    "activeCourtOrdered": false
                                },
                                {
                                    "standardBenefitAreaId": "MEDICAL",
                                    "benefitOptionId": "NoCoverageMedical",
                                    "dependentCoverageRequired": false,
                                    "activeCourtOrdered": false
                                }
                            ]
                        },
                        {
                            "fullName": "Adam Drew ",
                            "dependentId": "d3f97b64-4a50-4dea-bec8-51d3db39352a",
                            "relationshipType": "Child",
                            "birthDate": "2012-01-01",
                            "activeIndicator": true,
                            "approvedIndicator": true,
                            "studentIndicator": false,
                            "coverages": [
                                {
                                    "standardBenefitAreaId": "DENTAL",
                                    "benefitOptionId": "NoCoverageDental",
                                    "dependentCoverageRequired": false,
                                    "activeCourtOrdered": false
                                },
                                {
                                    "standardBenefitAreaId": "MEDICAL",
                                    "benefitOptionId": "NoCoverageMedical",
                                    "dependentCoverageRequired": false,
                                    "activeCourtOrdered": false
                                }
                            ]
                        }
                    ]
                }
            },
            "changes": []
        }
    ]
}

I am currently utilizing VBA-JSON from https://github.com/VBA-tools/VBA-JSON to parse the JSON.

JsonOptions.AllowUnquotedKeys = True
Set JSON = JsonConverter.ParseJson(jsonResponse)

For Each Eligibility In JSON("participantEligibilityResults")
    For Each Detail In Eligibility("eligibilityResult")
            'DO SOMETHING HERE
        Next
Next

Unfortunately, once I parse at the participantEligibilityResults level, I am unable to access the levels below. I get an error "Object doesn't support this property or method." Can someone point me in the right direction?

I'm curious to see what the JSON looks like after it has been parsed by the VBA plugin. — Mech
– Mech, Commented Apr 30, 2020 at 2:22
I hope this answers your question. Please let me know if it doesn't. When I call Set Json = JsonConverter.ParseJson(JsonText), it creates a Dictionary one item with the key of participantEligibilityResults. — Todd Zetlan
– Todd Zetlan, Commented Apr 30, 2020 at 2:29

Tim Williams · Accepted Answer · 2020-04-30 22:20:26Z

Everything enclosed in {} will be output as a dictionary, everything enclosed in [] will be a collection. You just need to follow the nesting to get where you want.

Sub Test()

    Dim result As String
    Dim Item, a
    Dim parsedResult As Object, obj, node, k

    'loading from a cell for testing...
    Set parsedResult = JsonConverter.ParseJson(Sheet2.Range("A1").Value)

    Set obj = parsedResult("participantEligibilityResults")(1)("eligibilityResult")

    Set node = obj("currentEvent")("eligibilityDetails")(1)
    DumpJSon node 'see below


    Set node = obj("currentEvent")("dependents")(1)
    DumpJSon node 'see below


End Sub

If there are specific items you want, then trying to create nested loops to get to them will likely not be very useful - identify the paths you want and access the values directly. If you need to (eg) loop over a collection then that needs to be part of your approach.

It's sometimes useful to double-check what you have in your parsed result, so you can use this to dump it to the Immediate window (the whole thing or only parts of it)

Sub DumpJSon(obj, Optional level As Long = 0)
    Const LEVEL_STEP As Long = 5
    Dim k, v, n, s, tmp
    If TypeName(obj) = "Dictionary" Then
        For Each k In obj.keys
            s = String(level, "-") & k & " = "
            If IsObject(obj(k)) Then
                Debug.Print s & IIf(obj(k).Count = 0, "Empty ", "") & _
                           TypeName(obj(k))
                DumpJSon obj(k), level + LEVEL_STEP
            Else
                Debug.Print s & obj(k)
            End If
        Next k
    ElseIf TypeName(obj) = "Collection" Then
        n = 1
        For Each v In obj
            s = String(level, "-") & "(Item #" & n & ") "
            If IsObject(v) Then
                Debug.Print s & IIf(v.Count = 0, "Empty ", "") & _
                            TypeName(v)
                DumpJSon v, level + LEVEL_STEP
            Else
                Debug.Print s & v
            End If
            n = n + 1
        Next v
    End If
End Sub

Collectives™ on Stack Overflow

Traversing Nested JSON using 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