json - 如何将数组/集合添加到 VBA POST JSON

Question

我在 https://github.com/VBA-tools/VBA-Web 中成功实现了 VBA-Web 代码但我不知道如何将带有数组或集合的项目添加到正文中。我对为此目的使用数组或集合非常陌生。这是我要发布的 Json 数据:

{
  "operation": "CREATE",
  "orderNo": "ORD001",
  "type": "D",
  "date": "2014-10-14",
  "location": {
    "address": "393 Hanover St, Boston, MA 02113, USA",
    "locationNo": "LOC001",
    "locationName": "Green Cross Pharmacy North End",
    "acceptPartialMatch": true
  },
  "duration": 20,
  "twFrom": "10:00",
  "twTo": "10:59",
  "load1": 10,
  "load2": 25,
  "vehicleFeatures": ["FR"],
  "skills": ["SK001", "SK002"],
  "notes": "Deliver at back door"
}

如何将位置数据添加到此代码中以进行发布？

Sub PostMan()
Dim Body As New Dictionary
Body.Add "operation", "CREATE"
Body.Add "orderNo", "ORD101"
Body.Add "type", "D"
Body.Add "date", "2020-04-24"
Body.Add "location", 'How do I the location data here? 
Body.Add  "vehicleFeatures", 'How do I add this item?
Body.Add  "skills", 'How do I add this item?

Dim Client As New WebClient
Dim Response As WebResponse
Set Response = Client.PostJson("https://api.optimoroute.com/v1/create_order?key=AUTH_KEY", Body)

Worksheets("Open1").Range("A1").Value = Response.Content
End Sub

Answer 1

对于我的大部分 JSON 工作，我使用 JsonConverter发现于 this repository .我为如何在 this answer 中构建 JSON 对象创建了一个有用的引用。 .根据结构，您可以将您的位置添加为 Dictionary数组为 Collections .

我使用 JsonConverter 查看并验证了 JSON 输出。 ，但没有在您的网站上进行测试。

Option Explicit

Sub PostMan()
    Dim Body As New Dictionary
    Body.Add "operation", "CREATE"
    Body.Add "orderNo", "ORD101"
    Body.Add "type", "D"
    Body.Add "date", "2020-04-24"

    Dim location As Dictionary
    Set location = New Dictionary
    With location
        .Add "address", "393 Hanover St, Boston, MA 02113, USA"
        .Add "locationNo", "LOC001"
        .Add "locationName", "Green Cross Pharmacy North End"
        .Add "acceptPartialMatch", True
    End With
    Body.Add "location", location

    Dim features As Collection
    Set features = New Collection
    features.Add "FR"
    Body.Add "vehicleFeatures", features

    Dim skills As Collection
    Set skills = New Collection
    With skills
        .Add "SK001"
        .Add "SK002"
    End With
    Body.Add "skills", skills

    Dim json As String
    json = JsonConverter.ConvertToJson(Body, Whitespace:=" ")

    Debug.Print json

'    Dim Client As New WebClient
'    Dim Response As WebResponse
'    Set Response = Client.PostJson("https://api.optimoroute.com/v1/create_order?key=AUTH_KEY", Body)
'
'    Worksheets("Open1").Range("A1").Value = Response.Content
End Sub