VBA JSON - Parse Multiple Values

Question

I'm having trouble using the JSON-VBA converter with a multiple values key.

I have the normal recursion routines written to navigate JSON trees but here's an example of a simple JSON parse which I can't seem to get to work.

See this for the VBA-JSON converter software, which is terrific and fast.

Environment: Windows 7 / Access 2016 / Private LAN (no Internet)

Here's the code:

Option Compare Database
Option Explicit

Sub testparse()
Dim js As String, i As Long, jo As Object, item As Variant
Dim keys(), vals()

' fails on this string
js = "{ !Category!: !Famous Pets!," & _
  "!code!: [!a!,!b!,!c!] }"            ' string with multiple values

' with the following string, this works
js = "{ !Category!: !Famous Pets!," & _
  "    !code!: !singlecodevalue! }"

js = Replace(js, "!", Chr(34)) ' replace ! with quotes

Debug.Print " js = " & js
Set jo = JsonConverter.ParseJson(js) ' returns object with json elements
i = 0
ReDim keys(1 To jo.Count)
ReDim vals(1 To jo.Count)

Debug.Print " Number keys found at top level " & jo.Count
For Each item In jo
    i = i + 1
    keys(i) = item
    vals(i) = jo(item)
    Next item

For i = 1 To jo.Count
    Debug.Print "key " & keys(i) & " = " & vals(i)
    Next i

End Sub

When `code` is an array then the values are represented as a Collection object: you can't assign an object to an array position without using `Set`. FYI Dictionay already has `Keys` and `Items` properties which return arrays, so there's no need for the loop here. — Tim Williams, Mar 03 '20 at 16:05

score 0 · Answer 1 · answered Mar 03 '20 at 16:51

For each item you encounter when running through a JSON object, you have to determine what you're dealing with -- especially if you don't know ahead of time how many items in an array! It gets even trickier if you have a compound JSON structure with collections inside arrays and such.

The bottom line is that you have to check each item you pull out of the JSON object and figure out what it is before accessing it. The top level of a JSON object (assuming the use of JsonConverter) will always be a Dictionary. So you can count on looping through the keys of the top level dictionary:

Dim json As Dictionary
Set json = JsonConverter.ParseJson(someJsonString)

Dim topLevelKey As String
For Each topLevelKey In json
    Dim item As Variant
    Debug.Print topLevelKey & " = " & item
Next topLevelKey

The problem with this is the item is not always a simple string. It can be a value (String), an array (Collection), or a group (Dictionary). See this answer as a good reference.

Basically, this means you have to check each item before you use it. So you can check it like this:

Select Case TypeName(item)
    Case "Collection"
        '--- loop through the item as a Collection
    Case "Dictionary"
        '--- loop through the item as a Dictionary
    Case Else
        '--- the item is a value of some type (String, Boolean, etc)
End Select

In my example here, I created a sub called ParseItem that checks each of the items in this manner. Reworking your original code into the example below:

Option Explicit

Sub testparse()
    Dim js As String, i As Long, jo As Object, item As Variant
    Dim keys(), vals()

    ' fails on this string
    js = "{ !Category!: !Famous Pets!," & _
         "!code!: [!a!,!b!,!c!] }"               ' string with multiple values

    ' with the following string, this works
    '    js = "{ !Category!: !Famous Pets!," & _
    '         "    !code!: !singlecodevalue! }"

    '--- compound example
    '    js = "{ !Category!: !Famous Pets!,!code!: [!a!,!b!,{!c! : { !c1! : !1!, !c2!:!2!}}] }"

    js = Replace(js, "!", Chr(34))               ' replace ! with quotes

    Debug.Print "----------------------"
    Debug.Print "js = " & js
    Set jo = JsonConverter.ParseJson(js)         ' returns object with json elements
    ParseDictionary 1, "root", jo
End Sub

Private Sub ParseCollection(ByVal level As Long, _
                            ByVal key As String, _
                            ByRef jsonCollection As Variant)
    Dim item As Variant
    For Each item In jsonCollection
        ParseItem level, key, item
    Next item
End Sub

Private Sub ParseDictionary(ByVal level As Long, _
                            ByVal key As String, _
                            ByRef jsonDictionary As Variant)
    Dim dictKey As Variant
    For Each dictKey In jsonDictionary
        ParseItem level, dictKey, jsonDictionary(dictKey)
    Next dictKey
End Sub

Private Sub ParseItem(ByVal level As Long, _
                      ByVal key As String, _
                      ByRef item As Variant)
    Select Case TypeName(item)
    Case "Collection"
        Debug.Print Format(level + 1, "00 ") & key & " (collection)"
        ParseCollection (level + 1), key, item
    Case "Dictionary"
        Debug.Print Format(level + 1, "00 ") & key & " (dictionary)"
        ParseDictionary (level + 1), key, item
    Case Else
        Debug.Print Format(level, "00 ") & key & " = " & item
    End Select
End Sub

VBA JSON - Parse Multiple Values

1 Answers1

Linked