使用 VBA 和 JSON 解析嵌套数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40444035/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parsing Nested Arrays using VBA and JSON
提问by Wraymac
I have a JSON I am trying to parse in VBA. I have successfully parsed out the array of "offers". Inside the array of "offers" is another array "prices""USD".
我有一个试图在 VBA 中解析的 JSON。我已成功解析出“报价”数组。在“报价”数组内是另一个数组“价格”“美元”。
The problem is that not every "offers" object has the "USD" array. I am trying to create an object that I can make a table/sheet with but I can't even get the objects to print in debug mode. This works but fails because not every Dict OfferDetails contains the "USD" object.
问题是并非每个“offers”对象都有“USD”数组。我正在尝试创建一个可以用来制作表格/工作表的对象，但我什至无法在调试模式下打印这些对象。这有效但失败了，因为并非每个 Dict OfferDetails 都包含“USD”对象。
What I would like to do is be able to print the string and if the "USD" object is missing just skip it and only print the ones that have the "USD". I have tried the IsMissing (in code) but it fails when it hits the missing "USD" object.
我想要做的是能够打印字符串，如果缺少“USD”对象，只需跳过它，只打印具有“USD”的对象。我已经尝试过 IsMissing（在代码中），但是当它遇到丢失的“USD”对象时失败了。
Any idea how I can get this string with the "USD" values? Note that the "USD" is an array and contains several objects, but I don't know how to address them either. Ideally I would like to parse out the "USD" the same way I did the "offers". I am totally lost as I am not very good in VBA
知道如何使用“USD”值获取此字符串吗？请注意，“USD”是一个数组，包含多个对象，但我也不知道如何处理它们。理想情况下，我想以与“报价”相同的方式解析“美元”。我完全迷失了，因为我不太擅长 VBA
This is a working script with a valid web JSON.
这是一个具有有效 Web JSON 的工作脚本。
Sub getJSONEP_lib_working()
'Need the JsonConverter found here https://github.com/VBA-tools/VBA-JSON
'Need the Microsoft Scripting Runtime
Dim Parsed As Dictionary
Dim Item As Dictionary
Dim OfferDetails As Dictionary
Dim Price As Dictionary
Dim USD As Dictionary
URL = "http://wraymac.com/JSON/example1.json"
url2 = "[{" & """mpn""" & ":" & """41202""" & "}]"
Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
MyRequest.Open "GET", URL
MyRequest.Send
JsonString = MyRequest.ResponseText
Dim json As Object
Set json = JsonConverter.ParseJson(JsonString)
Set Parsed = JsonConverter.ParseJson(MyRequest.ResponseText)
For Each Item In Parsed("results")(1)("items")
For Each OfferDetails In Item("offers")
'I tried this it doesn't work, it fails when it finds a non existent "USD"
If Not IsMissing(OfferDetails("prices")("USD")(1)(1)) Then
Debug.Print OfferDetails("prices")("USD")(1)(1)
Else
Debug.Print "Missing"
End If
x = Item("mpn") & " " & "sku" & " - " & OfferDetails("sku") & "," & "UID" & " - " & OfferDetails("seller")("uid") & " " & OfferDetails("moq") & "packaging" & " = " & OfferDetails("packaging") & " " & OfferDetails("seller")("name") & " " & Item("manufacturer")("name")
Debug.Print x
'This works but fails because not every Dict OfferDetails contains the "USD" object
'x = Item("mpn") & " " & "sku" & " - " & OfferDetails("sku") & "," & "UID" & " - " & OfferDetails("seller")("uid") & " " & OfferDetails("moq") & "packaging" & " = " & OfferDetails("packaging") & " " & OfferDetails("seller")("name") & " " & Item("manufacturer")("name")& " "&OfferDetails("prices")("USD")(1)(1)
Next OfferDetails
Next
End Sub
回答by Tim Williams
You want to use the Dictionary's Existsmethod:
你想使用字典的Exists方法：
Set Parsed = JsonConverter.ParseJson(MyRequest.ResponseText)
For Each Item In Parsed("results")(1)("items")
For Each OfferDetails In Item("offers")
If OfferDetails("prices").Exists("USD") Then
Debug.Print OfferDetails("prices")("USD").Count & " items:"
Debug.Print "-----------------"
For Each x In OfferDetails("prices")("USD")
Debug.Print x(1), x(2)
Next x
Debug.Print "-----------------"
Else
Debug.Print "No USD"
End If
Next OfferDetails
Next
The converter parses objects ({}) to dictionaries, and arrays ([]) to collections, so you can use Countto determine the number of items in each of those types of object.
转换器将对象 ({}) 解析为字典，将数组 ([]) 解析为集合，因此您可以使用它Count来确定每种类型的对象中的项目数。
