VBA json extract all data by a specific property name

I use VBA JSON parser from https://github.com/omegastripes/VBA-JSON-parser

I have the following json:

{
   "Porp1": {
      "Prop2": {
        "name": "test"
      },
   },
   "name": "test123",
   "other_prop": {
     "name": "specific name"
   }
}

with that tool I need to extract all values from name fields.

For now I use the follwing code:

Dim vJson As Object
Dim sState As String
JSON.Parse response, vJson, sState

'From here don't know to extract all `name` properties, no matter of level of nesting

How to achieve this ?

Solution

A converted JSON is a Dictionary that contains properties. The keys are the property names, the values can be

simple values (eg strings)
Dictionaries.
Arrays (either of values or Dictionaries)

So the easiest way to travel thru the JSON is to create a recursive routine.

If the property is a simple value, check the property name. If the property name = "name", write the property value (or do whatever you want to do)

If the property is a Dictionary, make a recursive call the the routine.

If the property is an Array, loop over that array and check if the members are again Dictionaries. If yes, make a recursive call for every member.

The following routine does exact that. I added a "Prefix" parameter so that you can see where the value came from.

Sub findProperty(prefix As String, jsonDict As Variant, lookForProperty As String)

    Dim propertyName As Variant
    Dim childJsonDict As Dictionary
    
    For Each propertyName In jsonDict.Keys
        If isDictionary(jsonDict(propertyName)) Then
            Set childJsonDict = jsonDict(propertyName)
            findProperty prefix & IIf(prefix = "", "", ".") & propertyName, childJsonDict, lookForProperty
        ElseIf IsArray(jsonDict(propertyName)) Then
            Dim i As Long
            For i = LBound(jsonDict(propertyName)) To UBound(jsonDict(propertyName))
                If isDictionary(jsonDict(propertyName)(i)) Then
                    Set childJsonDict = jsonDict(propertyName)(i)
                    findProperty prefix & IIf(prefix = "", "", ".") & propertyName & "[" & i & "]", childJsonDict, lookForProperty
                End If
            Next i
        ElseIf LCase(propertyName) = LCase(lookForProperty) Then
            Debug.Print prefix & IIf(prefix = "", "", ".") & propertyName, jsonDict(propertyName)
        End If
    Next
End Sub

Function isDictionary(property As Variant) As Boolean
    If VarType(property) <> vbObject Then Exit Function
    isDictionary = TypeName(property) = "Dictionary"
End Function

You can call this from your code with

Dim vJson As Object
Dim sState As String
JSON.Parse response, vJson, sState
If TypeName(vJson) = "Dictionary" Then
    Call findProperty("", vJson, "Name")
Else
    MsgBox "Error parsing JSON"
End If

And the output in the immediate window is

Porp1.Prop2.name            test
name          test123
other_prop.name             specific name

Update
a) There was an issue with my code (stupid Cut&Paste error) handling arrays, corrected.

b) The JSON converter expects a Variant as result variable (vJson). If you declare it as Object or Dictionary, the converter will raise a runtime error when the JSON-Code is invalid (as you can't assign a scalar like Null to an Object). I changed the code accordingly.

c) This code will not work if the topmost element is an Array, it expects a Dictionary. Hope that fits for you, I neither have the time nor valid test data to rewrite it.