Wraymac Wraymac - 1 month ago 26
JSON Question

Parsing Nested Arrays VBA-JSON , JSON

I have a JSON I am trying to parse in VBA
I have successfully parsed out the array of "offers"
Inside the array of "offers" is another array "prices""USD"
The problem is that not every "offers" object has the "USD" array
I am trying to create an object that I can make a table/sheet with but I can't even get the objects to print in debug mode

This works but fails because not every Dict OfferDetails contains the "USD" object

What I would like to do is be able to print the string and if the "USD" object is missing just skip it and only print the ones that have the "USD"

I have tried the IsMissing (in code) but it fails when it hits the missing "USD" object

Any Idea how I can get this string with the "USD" values, note that the "USD" is an array and contains several objects, but I don't know how to address them either.

Ideally I would like to parse out the "USD" the same way I did the "offers"

I am totally lost as I am not very good in VBA

Please don't vote me down, I need the help

This is a working script with a valid web json

Sub getJSONEP_lib_working()
'Need the JsonConverter found here https://github.com/VBA-tools/VBA-JSON
'Need the Microsoft Scripting Runtime

Dim Parsed As Dictionary
Dim Item As Dictionary
Dim OfferDetails As Dictionary
Dim Price As Dictionary
Dim USD As Dictionary

URL = "http://wraymac.com/JSON/example1.json"
url2 = "[{" & """mpn""" & ":" & """41202""" & "}]"

Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
MyRequest.Open "GET", URL
MyRequest.Send

JsonString = MyRequest.ResponseText

Dim json As Object
Set json = JsonConverter.ParseJson(JsonString)


Set Parsed = JsonConverter.ParseJson(MyRequest.ResponseText)

For Each Item In Parsed("results")(1)("items")
For Each OfferDetails In Item("offers")


'I tried this it doesn't work, it fails when it finds a non existent "USD"
If Not IsMissing(OfferDetails("prices")("USD")(1)(1)) Then
Debug.Print OfferDetails("prices")("USD")(1)(1)
Else
Debug.Print "Missing"
End If


x = Item("mpn") & " " & "sku" & " - " & OfferDetails("sku") & "," & "UID" & " - " & OfferDetails("seller")("uid") & " " & OfferDetails("moq") & "packaging" & " = " & OfferDetails("packaging") & " " & OfferDetails("seller")("name") & " " & Item("manufacturer")("name")
Debug.Print x

'This works but fails because not every Dict OfferDetails contains the "USD" object
'x = Item("mpn") & " " & "sku" & " - " & OfferDetails("sku") & "," & "UID" & " - " & OfferDetails("seller")("uid") & " " & OfferDetails("moq") & "packaging" & " = " & OfferDetails("packaging") & " " & OfferDetails("seller")("name") & " " & Item("manufacturer")("name")& " "&OfferDetails("prices")("USD")(1)(1)

Next OfferDetails
Next

End Sub

Answer

You want to use the Dictionary's Exists method:

Set Parsed = JsonConverter.ParseJson(MyRequest.ResponseText)

     For Each Item In Parsed("results")(1)("items")
        For Each OfferDetails In Item("offers")

            If OfferDetails("prices").Exists("USD") Then
                Debug.Print OfferDetails("prices")("USD")(1)(1)
            Else
                Debug.Print "No USD"
            End If