使用 VBscript 访问 JSON 数据中的所有值 [英] Using VBscript to access all values in JSON data
问题描述
我必须做一些处理来自网络服务器的 json 格式输出的 vbscript.我正在使用一个旧的 vbscript 代码片段,我发现它叫做aspJSON"——我认为它来自 www.aspjson.com,但该站点不再可用.
I have to do some vbscript that handles a json formatted output from a webserver. I am using an old vbscript code snippet I have found called "aspJSON" - I think it is from www.aspjson.com but that site is no longer available.
我有这个 JSON 文件:
I have this JSON file:
{
"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 代码,我可以从数据中获取一些值.这两个可以正常工作:
With the aspJSON code I can get some of the values from the data. Theese two will work fine:
Msgbox oJSON.data("VAT")
MsgBox oJSON.data("buyInfo").item("maximumBuyAmount")
但我似乎无法访问 prices
的值:
But I cant seem to acces the values of prices
:
[{"unitPrice":12.50}]
和句点
:
[{"period":{"endDate":"xxx"}}]
如何访问这些值?
这是 aspJSON 代码:
This is the aspJSON code:
'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), "")
val = Replace(val, Chr(12), "f")
val = Replace(val, Chr(10), "
")
val = Replace(val, Chr(13), "
")
val = Replace(val, Chr(9), " ")
aj_JSONEncode = Trim(val)
End Function
Private Function aj_JSONDecode(ByVal val)
val = Replace(val, """", """")
val = Replace(val, "\", "")
val = Replace(val, "/", "/")
val = Replace(val, "", Chr(8))
val = Replace(val, "f", Chr(12))
val = Replace(val, "
", Chr(10))
val = Replace(val, "
", Chr(13))
val = Replace(val, " ", 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
推荐答案
与 VAT
和 buyInfo
不同,prices
是一个集合,它可以包含多个实例(注意JSON结构的不同,prices
用方括号封装).每当您处理集合时,都需要一个循环来遍历实例以获取它们的底层属性.
Unlike VAT
and buyInfo
, prices
is a Collection which can contain multiple instances (notice the difference in the JSON structure, prices
is encapsulated by square brackets). Whenever you deal with Collections a loop is required to iterate through the instances to get at their underlying properties.
我建议使用 For Each
循环,如下所示.#
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
# 提供的代码未经测试
通过在评论中与 @omegastripes 进行一些有用的讨论并查看 aspJSON
类,您应该能够按顺序访问 Collection/Array 项,例如获取您将使用的 unitPrice
;
Looking into this a bit more with some useful discussion with @omegastripes in the comments and looking through the aspJSON
class, you should be able to access the Collection / Array items by ordinal, for example to get unitPrice
you would use;
oJSON("prices")(0).Item("unitPrice")
考虑到这一点,我们做了一个快速测试脚本,结果如下.
With this in mind did a quick test script and here is the result.
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")
输出:
12.5
/Date(928142400000+0200)/
这篇关于使用 VBscript 访问 JSON 数据中的所有值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!