使用 VBscript 访问 JSON 数据中的所有值

我必须做一些 vbscript 来处理来自网络服务器的 json 格式的输出。我正在使用我发现的一个名为“aspJSON”的旧 vbscript 代码片段 - 我认为它来自 www.aspjson.com，但该网站不再可用。

我有这个 JSON 文件：

{
"VAT":12678967.543233,
"buyInfo":{
    "maximumBuyAmount":100,
    "minimumBuyAmount":1,
},
"prices":[{
    "unitPrice":12.50
    "specialOfferPrice":8.75,
    "period":{
        "endDate":"\/Date(928142400000+0200)\/",
        "startDate":"\/Date(928142400000+0200)\/",
    },
}],
}

使用 aspJSON 代码，我可以从数据中获取一些值。这两个可以正常工作：

Msgbox oJSON.data("VAT")

MsgBox oJSON.data("buyInfo").item("maximumBuyAmount")

但我似乎无法访问的值prices:

[{"unitPrice":12.50}] 

and period:

[{"period":{"endDate":"xxx"}}]

我如何访问这些值？

这是 aspJSON 代码：

'Februari 2014 - Version 1.17 by Gerrit van Kuipers
Class aspJSON
Public data
Private p_JSONstring
private aj_in_string, aj_in_escape, aj_i_tmp, aj_char_tmp, aj_s_tmp, aj_line_tmp, aj_line, aj_lines, aj_currentlevel, aj_currentkey, aj_currentvalue, aj_newlabel, aj_XmlHttp, aj_RegExp, aj_colonfound

Private Sub Class_Initialize()
    Set data = Collection()

    Set aj_RegExp = new regexp
    aj_RegExp.Pattern = "\s{0,}(\S{1}[\s,\S]*\S{1})\s{0,}"
    aj_RegExp.Global = False
    aj_RegExp.IgnoreCase = True
    aj_RegExp.Multiline = True
End Sub

Private Sub Class_Terminate()
    Set data = Nothing
    Set aj_RegExp = Nothing
End Sub

Public Sub loadJSON(inputsource)
    inputsource = aj_MultilineTrim(inputsource)
    If Len(inputsource) = 0 Then Err.Raise 1, "loadJSON Error", "No data to load."

    select case Left(inputsource, 1)
        case "{", "["
        case else
            Set aj_XmlHttp = CreateObject("Msxml2.ServerXMLHTTP")
            aj_XmlHttp.open "GET", inputsource, False
            aj_XmlHttp.setRequestHeader "Content-Type", "text/json"
            aj_XmlHttp.setRequestHeader "CharSet", "UTF-8"
            aj_XmlHttp.Send
            inputsource = aj_XmlHttp.responseText
            set aj_XmlHttp = Nothing
    end select

    p_JSONstring = CleanUpJSONstring(inputsource)
    aj_lines = Split(p_JSONstring, Chr(13) & Chr(10))

    Dim level(99)
    aj_currentlevel = 1
    Set level(aj_currentlevel) = data
    For Each aj_line In aj_lines
        aj_currentkey = ""
        aj_currentvalue = ""
        If Instr(aj_line, ":") > 0 Then
            aj_in_string = False
            aj_in_escape = False
            aj_colonfound = False
            For aj_i_tmp = 1 To Len(aj_line)
                If aj_in_escape Then
                    aj_in_escape = False
                Else
                    Select Case Mid(aj_line, aj_i_tmp, 1)
                        Case """"
                            aj_in_string = Not aj_in_string
                        Case ":"
                            If Not aj_in_escape And Not aj_in_string Then
                                aj_currentkey = Left(aj_line, aj_i_tmp - 1)
                                aj_currentvalue = Mid(aj_line, aj_i_tmp + 1)
                                aj_colonfound = True
                                Exit For
                            End If
                        Case "\"
                            aj_in_escape = True
                    End Select
                End If
            Next
            if aj_colonfound then
                aj_currentkey = aj_Strip(aj_JSONDecode(aj_currentkey), """")
                If Not level(aj_currentlevel).exists(aj_currentkey) Then level(aj_currentlevel).Add aj_currentkey, ""
            end if
        End If
        If right(aj_line,1) = "{" Or right(aj_line,1) = "[" Then
            If Len(aj_currentkey) = 0 Then aj_currentkey = level(aj_currentlevel).Count
            Set level(aj_currentlevel).Item(aj_currentkey) = Collection()
            Set level(aj_currentlevel + 1) = level(aj_currentlevel).Item(aj_currentkey)
            aj_currentlevel = aj_currentlevel + 1
            aj_currentkey = ""
        ElseIf right(aj_line,1) = "}" Or right(aj_line,1) = "]" or right(aj_line,2) = "}," Or right(aj_line,2) = "]," Then
            aj_currentlevel = aj_currentlevel - 1
        ElseIf Len(Trim(aj_line)) > 0 Then
            if Len(aj_currentvalue) = 0 Then aj_currentvalue = aj_line
            aj_currentvalue = getJSONValue(aj_currentvalue)

            If Len(aj_currentkey) = 0 Then aj_currentkey = level(aj_currentlevel).Count
            level(aj_currentlevel).Item(aj_currentkey) = aj_currentvalue
        End If
    Next
End Sub

Public Function Collection()
    set Collection = CreateObject("Scripting.Dictionary")
End Function

Public Function AddToCollection(dictobj)
    if TypeName(dictobj) <> "Dictionary" then Err.Raise 1, "AddToCollection Error", "Not a collection."
    aj_newlabel = dictobj.Count
    dictobj.Add aj_newlabel, Collection()
    set AddToCollection = dictobj.item(aj_newlabel)
end function

Private Function CleanUpJSONstring(aj_originalstring)
    aj_originalstring = Replace(aj_originalstring, Chr(13) & Chr(10), "")
    aj_originalstring = Mid(aj_originalstring, 2, Len(aj_originalstring) - 2)
    aj_in_string = False : aj_in_escape = False : aj_s_tmp = ""
    For aj_i_tmp = 1 To Len(aj_originalstring)
        aj_char_tmp = Mid(aj_originalstring, aj_i_tmp, 1)
        If aj_in_escape Then
            aj_in_escape = False
            aj_s_tmp = aj_s_tmp & aj_char_tmp
        Else
            Select Case aj_char_tmp
                Case "\" : aj_s_tmp = aj_s_tmp & aj_char_tmp : aj_in_escape = True
                Case """" : aj_s_tmp = aj_s_tmp & aj_char_tmp : aj_in_string = Not aj_in_string
                Case "{", "["
                    aj_s_tmp = aj_s_tmp & aj_char_tmp & aj_InlineIf(aj_in_string, "", Chr(13) & Chr(10))
                Case "}", "]"
                    aj_s_tmp = aj_s_tmp & aj_InlineIf(aj_in_string, "", Chr(13) & Chr(10)) & aj_char_tmp
                Case "," : aj_s_tmp = aj_s_tmp & aj_char_tmp & aj_InlineIf(aj_in_string, "", Chr(13) & Chr(10))
                Case Else : aj_s_tmp = aj_s_tmp & aj_char_tmp
            End Select
        End If
    Next

    CleanUpJSONstring = ""
    aj_s_tmp = split(aj_s_tmp, Chr(13) & Chr(10))
    For Each aj_line_tmp In aj_s_tmp
        aj_line_tmp = replace(replace(aj_line_tmp, chr(10), ""), chr(13), "")
        CleanUpJSONstring = CleanUpJSONstring & aj_Trim(aj_line_tmp) & Chr(13) & Chr(10)

    Next


    End Function

Private Function getJSONValue(ByVal val)
    val = Trim(val)
    If Left(val,1) = ":"  Then val = Mid(val, 2)
    If Right(val,1) = "," Then val = Left(val, Len(val) - 1)
    val = Trim(val)

    Select Case val
        Case "true"  : getJSONValue = True
        Case "false" : getJSONValue = False
        Case "null" : getJSONValue = Null
        Case Else
            If (Instr(val, """") = 0) Then
                If IsNumeric(val) Then
                    getJSONValue = CDbl(val)
                Else
                    getJSONValue = val
                End If
            Else
                If Left(val,1) = """" Then val = Mid(val, 2)
                If Right(val,1) = """" Then val = Left(val, Len(val) - 1)
                getJSONValue = aj_JSONDecode(Trim(val))
            End If
    End Select
End Function

Private JSONoutput_level
Public Function JSONoutput()
    dim wrap_dicttype, aj_label
    JSONoutput_level = 1
    wrap_dicttype = "[]"
    For Each aj_label In data
         If Not aj_IsInt(aj_label) Then wrap_dicttype = "{}"
    Next
    JSONoutput = Left(wrap_dicttype, 1) & Chr(13) & Chr(10) & GetDict(data) & Right(wrap_dicttype, 1)
End Function

Private Function GetDict(objDict)
    dim aj_item, aj_keyvals, aj_label, aj_dicttype
    For Each aj_item In objDict
        Select Case TypeName(objDict.Item(aj_item))
            Case "Dictionary"
                GetDict = GetDict & Space(JSONoutput_level * 4)

                aj_dicttype = "[]"
                For Each aj_label In objDict.Item(aj_item).Keys
                     If Not aj_IsInt(aj_label) Then aj_dicttype = "{}"
                Next
                If aj_IsInt(aj_item) Then
                    GetDict = GetDict & (Left(aj_dicttype,1) & Chr(13) & Chr(10))
                Else
                    GetDict = GetDict & ("""" & aj_JSONEncode(aj_item) & """" & ": " & Left(aj_dicttype,1) & Chr(13) & Chr(10))
                End If
                JSONoutput_level = JSONoutput_level + 1

                aj_keyvals = objDict.Keys
                GetDict = GetDict & (GetSubDict(objDict.Item(aj_item)) & Space(JSONoutput_level * 4) & Right(aj_dicttype,1) & aj_InlineIf(aj_item = aj_keyvals(objDict.Count - 1),"" , ",") & Chr(13) & Chr(10))
            Case Else
                aj_keyvals =  objDict.Keys
                GetDict = GetDict & (Space(JSONoutput_level * 4) & aj_InlineIf(aj_IsInt(aj_item), "", """" & aj_JSONEncode(aj_item) & """: ") & WriteValue(objDict.Item(aj_item)) & aj_InlineIf(aj_item = aj_keyvals(objDict.Count - 1),"" , ",") & Chr(13) & Chr(10))
        End Select
    Next
End Function

Private Function aj_IsInt(val)
    aj_IsInt = (TypeName(val) = "Integer" Or TypeName(val) = "Long")
End Function

Private Function GetSubDict(objSubDict)
    GetSubDict = GetDict(objSubDict)
    JSONoutput_level= JSONoutput_level -1
End Function

Private Function WriteValue(ByVal val)
    Select Case TypeName(val)
        Case "Double", "Integer", "Long": WriteValue = val
        Case "Null"                     : WriteValue = "null"
        Case "Boolean"                  : WriteValue = aj_InlineIf(val, "true", "false")
        Case Else                       : WriteValue = """" & aj_JSONEncode(val) & """"
    End Select
End Function

Private Function aj_JSONEncode(ByVal val)
    val = Replace(val, "\", "\\")
    val = Replace(val, """", "\""")
    'val = Replace(val, "/", "\/")
    val = Replace(val, Chr(8), "\b")
    val = Replace(val, Chr(12), "\f")
    val = Replace(val, Chr(10), "\n")
    val = Replace(val, Chr(13), "\r")
    val = Replace(val, Chr(9), "\t")
    aj_JSONEncode = Trim(val)
End Function

Private Function aj_JSONDecode(ByVal val)
    val = Replace(val, "\""", """")
    val = Replace(val, "\\", "\")
    val = Replace(val, "\/", "/")
    val = Replace(val, "\b", Chr(8))
    val = Replace(val, "\f", Chr(12))
    val = Replace(val, "\n", Chr(10))
    val = Replace(val, "\r", Chr(13))
    val = Replace(val, "\t", Chr(9))
    aj_JSONDecode = Trim(val)
End Function

Private Function aj_InlineIf(condition, returntrue, returnfalse)
    If condition Then aj_InlineIf = returntrue Else aj_InlineIf = returnfalse
End Function

Private Function aj_Strip(ByVal val, stripper)
    If Left(val, 1) = stripper Then val = Mid(val, 2)
    If Right(val, 1) = stripper Then val = Left(val, Len(val) - 1)
    aj_Strip = val
End Function

Private Function aj_MultilineTrim(TextData)
    aj_MultilineTrim = aj_RegExp.Replace(TextData, "$1")
End Function

private function aj_Trim(val)
    aj_Trim = Trim(val)
    Do While Left(aj_Trim, 1) = Chr(9) : aj_Trim = Mid(aj_Trim, 2) : Loop
    Do While Right(aj_Trim, 1) = Chr(9) : aj_Trim = Left(aj_Trim, Len(aj_Trim) - 1) : Loop
    aj_Trim = Trim(aj_Trim)
end function
End Class

Unlike VAT and buyInfo, prices是一个可以包含多个实例的集合（注意 JSON 结构的差异，prices用方括号括起来）。每当您处理集合时，都需要一个循环来迭代实例以获取其底层属性。

I'd recommend a For Each loop, like below. ^#

Dim key, price

'Iterating a Scripting.Dictionary using For Each returns the key.
For Each key In oJSON.data("prices")
  'Get the price instance by passing the key back into 
  'the Scripting.Dictionary.
  Set price = oJSON.data("prices")(key)
  MsgBox price.item("unitPrice")
  MsgBox price.item("specialOfferPrice")
  MsgBox price.item("period").item("endDate")
  MsgBox price.item("period").item("startDate")
  'Clear object before iterating the next instance.
  Set price = Nothing
Next

^{# Code provided untested}

通过一些有用的讨论进一步研究这一点@omegastripes在评论中并浏览aspJSON类，您应该能够按序数访问集合/数组项，例如获取unitPrice你会使用；

oJSON("prices")(0).Item("unitPrice")

考虑到这一点，我们做了一个快速测试脚本，结果如​​下。

Option Explicit

Dim prices: Set prices = CreateObject("Scripting.Dictionary")
Dim price, period

With prices
    Set price = CreateObject("Scripting.Dictionary")
    With price
        Call .Add("unitPrice", 12.50)
        Call .Add("specialOfferPrice", 8.75)
        Set period = CreateObject("Scripting.Dictionary")
        With period
            Call .Add("endDate", "/Date(928142400000+0200)/")
        End With
        Call .Add("period", period)
    End With
    'Uses same method as the AddToCollection() in aspJSON to
    'assign the ordinal position when adding the child Dictionary.
    Call .Add(.Count, price)
End With

WScript.Echo prices(0).Item("unitPrice")
WScript.Echo prices(0).Item("period").Item("endDate")

Output:

12.5
/Date(928142400000+0200)/