how to extract elements in json string in vba

Question

I have the following json string:

[
    {
        "Features": {
            "Level": [
                {
                    "endDate": "2018-12-11",
                    "minimum": "0.000000000000",
                    "maximum": "0.000000000000",
                    "value": "228.108000000000",
                    "payDate": "0"
                },
                {
                    "endDate": "2018-12-11",
                    "minimum": "0.000000000000",
                    "maximum": "0.000000000000",
                    "value": "3143.513000000000",
                    "payDate": "0"
                }
            ]
        }
    },
]

I was trying to extract the two values in excel using vba and have the following code (where result2 would be the json string)

Public Sub GetS (result2 As String, m As Integer)

Dim activeWS As Worksheet
Set activeWS = ThisWorkbook.Worksheets("Data")
Dim jsonStr As String, json As Object, headers()
jsonStr = result2 
Set json = JsonConverter.ParseJson(jsonStr)(1)
activeWS.Cells(m, 19) = json("Features")("Level")(0)("value")
activeWS.Cells(m, 20) = json("Features")("Level")(1)("value")


End Sub

the second part of the vba works, where it grab the value of 3143.51 (the second number), i'm wondering how can I get the first value (228.10).

I've tried using ("Initial Level")(0)("value") but it doesn't work.

Thank you so much.

Tim Williams · Answer 1 · 2018-12-14T22:16:40.957

0

JSON arrays get converted to VBA Collections - these are indexed from 1 not from zero

Try this:

jsonStr = Range("I10").Value 'your json sample
Set json = JsonConverter.ParseJson(jsonStr)(1)

Debug.Print json("Features")("Level")(1)("value") '>>228.108000000000
Debug.Print json("Features")("Level")(2)("value") '>>3143.513000000000

(tested and confirmed)

edited Dec 14 '18 at 22:16

answered Dec 14 '18 at 19:43

Tim Williams

154,628
8
97
125

HI Tim, it's still not working. Also, please note that json("Features")("Level")(1)("value") would grab the second value (not the first one), so is there any ways for me to grab the first value? Thanks you! – user10382480 Dec 14 '18 at 19:54
1

I could test but your posted JSON doesn't parse and I don't want to guess the fix. – Tim Williams Dec 14 '18 at 20:49
I edited it so it should work now, thank you so much! – user10382480 Dec 14 '18 at 21:39
Confirmed in testing using your (almost working - extra comma after last brace) json – Tim Williams Dec 14 '18 at 22:17
Thank you so much! – user10382480 Dec 17 '18 at 15:41

score 0 · Answer 2 · answered Dec 15 '18 at 08:03

Here is how to access data in the JSON string:

Json("match")("awayTeam")("coach")("countryOfBirth")
Json("match")("awayTeam")("coach")("nationality")
Json("match")("awayTeam")("captain")("id")
Json("match")("awayTeam")("captain")("name")
Json("match")("awayTeam")("captain")("shirtNumber")
Json("match")("awayTeam")("lineup")(1)("id")
Json("match")("awayTeam")("lineup")(1)("name")
Json("match")("awayTeam")("lineup")(1)("position")
Json("match")("awayTeam")("lineup")(1)("shirtNumber")
Json("match")("awayTeam")("lineup")(2)("id")
Json("match")("awayTeam")("lineup")(2)("name")
Json("match")("awayTeam")("lineup")(2)("position")
Json("match")("awayTeam")("lineup")(2)("shirtNumber")
Json("match")("awayTeam")("lineup")(3)("id")
Json("match")("awayTeam")("lineup")(3)("name")
Json("match")("awayTeam")("lineup")(3)("position")
Json("match")("awayTeam")("lineup")(3)("shirtNumber")
Json("match")("awayTeam")("lineup")(4)("id")
Json("match")("awayTeam")("lineup")(4)("name")
Json("match")("awayTeam")("lineup")(4)("position")
Json("match")("awayTeam")("lineup")(4)("shirtNumber")
Json("match")("awayTeam")("lineup")(5)("id")
Json("match")("awayTeam")("lineup")(5)("name")
Json("match")("awayTeam")("lineup")(5)("position")
Json("match")("awayTeam")("lineup")(5)("shirtNumber")
Json("match")("awayTeam")("lineup")(6)("id")
Json("match")("awayTeam")("lineup")(6)("name")
Json("match")("awayTeam")("lineup")(6)("position")
Json("match")("awayTeam")("lineup")(6)("shirtNumber")
Json("match")("awayTeam")("lineup")(7)("id")
Json("match")("awayTeam")("lineup")(7)("name")
Json("match")("awayTeam")("lineup")(7)("position")
Json("match")("awayTeam")("lineup")(7)("shirtNumber")
Json("match")("awayTeam")("lineup")(8)("id")
Json("match")("awayTeam")("lineup")(8)("name")
Json("match")("awayTeam")("lineup")(8)("position")
Json("match")("awayTeam")("lineup")(8)("shirtNumber")
Json("match")("awayTeam")("lineup")(9)("id")
Json("match")("awayTeam")("lineup")(9)("name")
Json("match")("awayTeam")("lineup")(9)("position")
Json("match")("awayTeam")("lineup")(9)("shirtNumber")
Json("match")("awayTeam")("lineup")(10)("id")
Json("match")("awayTeam")("lineup")(10)("name")
Json("match")("awayTeam")("lineup")(10)("position")
Json("match")("awayTeam")("lineup")(10)("shirtNumber")
Json("match")("awayTeam")("lineup")(11)("id")
Json("match")("awayTeam")("lineup")(11)("name")
Json("match")("awayTeam")("lineup")(11)("position")
Json("match")("awayTeam")("lineup")(11)("shirtNumber")
Json("match")("awayTeam")("bench")(1)("id")
Json("match")("awayTeam")("bench")(1)("name")
Json("match")("awayTeam")("bench")(1)("position")
Json("match")("awayTeam")("bench")(1)("shirtNumber")
Json("match")("awayTeam")("bench")(2)("id")
Json("match")("awayTeam")("bench")(2)("name")
Json("match")("awayTeam")("bench")(2)("position")
Json("match")("awayTeam")("bench")(2)("shirtNumber")
Json("match")("awayTeam")("bench")(3)("id")
Json("match")("awayTeam")("bench")(3)("name")
Json("match")("awayTeam")("bench")(3)("position")
Json("match")("awayTeam")("bench")(3)("shirtNumber")
Json("match")("awayTeam")("bench")(4)("id")
Json("match")("awayTeam")("bench")(4)("name")
Json("match")("awayTeam")("bench")(4)("position")
Json("match")("awayTeam")("bench")(4)("shirtNumber")
Json("match")("awayTeam")("bench")(5)("id")
Json("match")("awayTeam")("bench")(5)("name")
Json("match")("awayTeam")("bench")(5)("position")
Json("match")("awayTeam")("bench")(5)("shirtNumber")
Json("match")("awayTeam")("bench")(6)("id")
Json("match")("awayTeam")("bench")(6)("name")
Json("match")("awayTeam")("bench")(6)("position")
Json("match")("awayTeam")("bench")(6)("shirtNumber")
Json("match")("awayTeam")("bench")(7)("id")
Json("match")("awayTeam")("bench")(7)("name")
Json("match")("awayTeam")("bench")(7)("position")
Json("match")("awayTeam")("bench")(7)("shirtNumber")
Json("match")("goals")(1)("minute")
Json("match")("goals")(1)("extraTime")
Json("match")("goals")(1)("type")
Json("match")("goals")(1)("team")("id")
Json("match")("goals")(1)("team")("name")
Json("match")("goals")(1)("scorer")("id")
Json("match")("goals")(1)("scorer")("name")
Json("match")("goals")(1)("assist")("id")
Json("match")("goals")(1)("assist")("name")
Json("match")("goals")(2)("minute")
Json("match")("goals")(2)("extraTime")
Json("match")("goals")(2)("type")
Json("match")("goals")(2)("team")("id")
Json("match")("goals")(2)("team")("name")
Json("match")("goals")(2)("scorer")("id")
Json("match")("goals")(2)("scorer")("name")
Json("match")("goals")(2)("assist")("id")
Json("match")("goals")(2)("assist")("name")
Json("match")("goals")(3)("minute")
Json("match")("goals")(3)("extraTime")
Json("match")("goals")(3)("type")
Json("match")("goals")(3)("team")("id")
Json("match")("goals")(3)("team")("name")
Json("match")("goals")(3)("scorer")("id")
Json("match")("goals")(3)("scorer")("name")
Json("match")("goals")(3)("assist")
Json("match")("bookings")(1)("minute")
Json("match")("bookings")(1)("team")("id")
Json("match")("bookings")(1)("team")("name")
Json("match")("bookings")(1)("player")("id")
Json("match")("bookings")(1)("player")("name")
Json("match")("bookings")(1)("card")
Json("match")("bookings")(2)("minute")
Json("match")("bookings")(2)("team")("id")
Json("match")("bookings")(2)("team")("name")
Json("match")("bookings")(2)("player")("id")
Json("match")("bookings")(2)("player")("name")
Json("match")("bookings")(2)("card")
Json("match")("bookings")(3)("minute")
Json("match")("bookings")(3)("team")("id")
Json("match")("bookings")(3)("team")("name")
Json("match")("bookings")(3)("player")("id")
Json("match")("bookings")(3)("player")("name")
Json("match")("bookings")(3)("card")
Json("match")("bookings")(4)("minute")
Json("match")("bookings")(4)("team")("id")
Json("match")("bookings")(4)("team")("name")
Json("match")("bookings")(4)("player")("id")
Json("match")("bookings")(4)("player")("name")
Json("match")("bookings")(4)("card")
Json("match")("bookings")(5)("minute")
Json("match")("bookings")(5)("team")("id")
Json("match")("bookings")(5)("team")("name")
Json("match")("bookings")(5)("player")("id")
Json("match")("bookings")(5)("player")("name")
Json("match")("bookings")(5)("card")
Json("match")("bookings")(6)("minute")
Json("match")("bookings")(6)("team")("id")
Json("match")("bookings")(6)("team")("name")
Json("match")("bookings")(6)("player")("id")
Json("match")("bookings")(6)("player")("name")
Json("match")("bookings")(6)("card")
Json("match")("bookings")(7)("minute")
Json("match")("bookings")(7)("team")("id")
Json("match")("bookings")(7)("team")("name")
Json("match")("bookings")(7)("player")("id")
Json("match")("bookings")(7)("player")("name")
Json("match")("bookings")(7)("card")
Json("match")("substitutions")(1)("minute")
Json("match")("substitutions")(1)("team")("id")
Json("match")("substitutions")(1)("team")("name")
Json("match")("substitutions")(1)("playerOut")("id")
Json("match")("substitutions")(1)("playerOut")("name")
Json("match")("substitutions")(1)("playerIn")("id")
Json("match")("substitutions")(1)("playerIn")("name")
Json("match")("substitutions")(2)("minute")
Json("match")("substitutions")(2)("team")("id")
Json("match")("substitutions")(2)("team")("name")
Json("match")("substitutions")(2)("playerOut")("id")
Json("match")("substitutions")(2)("playerOut")("name")
Json("match")("substitutions")(2)("playerIn")("id")
Json("match")("substitutions")(2)("playerIn")("name")
Json("match")("substitutions")(3)("minute")
Json("match")("substitutions")(3)("team")("id")
Json("match")("substitutions")(3)("team")("name")
Json("match")("substitutions")(3)("playerOut")("id")
Json("match")("substitutions")(3)("playerOut")("name")
Json("match")("substitutions")(3)("playerIn")("id")
Json("match")("substitutions")(3)("playerIn")("name")
Json("match")("substitutions")(4)("minute")
Json("match")("substitutions")(4)("team")("id")
Json("match")("substitutions")(4)("team")("name")
Json("match")("substitutions")(4)("playerOut")("id")
Json("match")("substitutions")(4)("playerOut")("name")
Json("match")("substitutions")(4)("playerIn")("id")
Json("match")("substitutions")(4)("playerIn")("name")
Json("match")("substitutions")(5)("minute")
Json("match")("substitutions")(5)("team")("id")
Json("match")("substitutions")(5)("team")("name")
Json("match")("substitutions")(5)("playerOut")("id")
Json("match")("substitutions")(5)("playerOut")("name")
Json("match")("substitutions")(5)("playerIn")("id")
Json("match")("substitutions")(5)("playerIn")("name")
Json("match")("substitutions")(6)("minute")
Json("match")("substitutions")(6)("team")("id")
Json("match")("substitutions")(6)("team")("name")
Json("match")("substitutions")(6)("playerOut")("id")
Json("match")("substitutions")(6)("playerOut")("name")
Json("match")("substitutions")(6)("playerIn")("id")
Json("match")("substitutions")(6)("playerIn")("name")
Json("match")("referees")(1)("id")
Json("match")("referees")(1)("name")
Json("match")("referees")(1)("nationality")
Json("match")("referees")(2)("id")
Json("match")("referees")(2)("name")
Json("match")("referees")(2)("nationality")
Json("match")("referees")(3)("id")
Json("match")("referees")(3)("name")
Json("match")("referees")(3)("nationality")
Json("match")("referees")(4)("id")
Json("match")("referees")(4)("name")
Json("match")("referees")(4)("nationality")

The two answers below show how to use a function that I wrote, PrintJSONAccessors(), to unravel the collections and arrays produced by JsonConverter.ParseJson().

how to extract elements in json string in vba

2 Answers2