我使用VBA JSON解析器从https://github.com/omegastripes/VBA-JSON-parser开始
我有以下json:
{
"Porp1": {
"Prop2": {
"name": "test"
},
},
"name": "test123",
"other_prop": {
"name": "specific name"
}
}
使用该工具，我需要从name个字段中提取所有值.
现在，我使用以下代码:
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
如何做到这一点呢？
转换后的JSON是包含属性的Dictionary.键是属性名称，值可以是
因此，遍历JSON的最简单方法是创建一个递归 routine .
如果属性是简单值，请判断属性名称.如果属性名称="name"，则编写属性value(或执行您想做的任何操作)
如果属性是字典，则对 routine 进行递归调用.
如果该属性是一个数组，则循环遍历该数组，并判断成员是否再次是字典.如果是，则对每个成员进行递归调用.
下面的 routine 就是这样做的.我添加了一个"prefix"参数，这样您就可以看到值来自哪里.
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
您可以使用以下命令从代码中调用它
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
"即时"窗口中的输出为
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)JSON转换器需要一个变量作为结果变量(VJson).如果将其声明为对象或字典，则当JSON代码无效时，转换器将引发运行时错误(因为您不能将像Null这样的标量赋给对象).我相应地更改了代码.
C)如果最上面的元素是数组，则此代码将不起作用，它需要一个字典.希望它适合你，我既没有时间也没有有效的测试数据来重写它.