JSON containing sets of reccord, VBA-JSON parsing only the first one

JeromePilot commented 3 months ago

Hi,

I receive an unusal JSON like that : [{"A":"aaa","Date":"2021-08-03","C":"ccc","E":"eee","H":"hhh","G":"ggg","Status":"on"}, {"A":"b","Date":"2022-06-20","C":"d","E":"fff","H":"hhh","G":"ggg","Status":"on"}]

(so some sets of reccord)

the VBA-JSON is only parsing with the first set of value, IE the first A value "aaa" and I cannot access to the second "A" value ("b") in a For Each vKey In JsonObject.Keys Debug.Print vKey Next vKey

Any idear ?

Cheers

Nick-vanGemeren commented 3 months ago

That's not an unusual structure at all. It's a JSON Array containing a series of JSON Objects (records). Once parsed, it's aCollectioncontaining a series ofDictionaryobjects.

Your post implies that you get some output from that code fragment. If you apply it to the result of the parse, you will get an error. If youSet JsonObject = result(1), you shouldn't be surprised to get the keys of the first record.

Normally you should process the records with another loop:

Sub Issue271()
    Dim str As String
    Dim result As Object, JsonObject As Object, vKey
    str = "[{'A':'aaa','Date':'2021-08-03','C':'ccc','E':'eee','H':'hhh','G':'ggg','Status':'on'}," & _
           "{'A':'b','Date':'2022-06-20','C':'d','E':'fff','H':'hhh','G':'ggg','Status':'on'}]"
    Set result = ParseJson(str)
    For Each JsonObject In result
        For Each vKey In JsonObject.Keys
            Debug.Print vKey
          Next vKey
      Next
End Sub

... preferably including checks on the object types etc. you received.

====================== If the above solves your problem, please close your issue here.

JeromePilot commented 3 months ago

Excellent Nick-vanGemeren, it works :) I'll have to look forward on that.

VBA-tools / VBA-JSON

JSON containing sets of reccord, VBA-JSON parsing only the first one #271