How to add array/collection to VBA POST JSON

Question

I am successfully implementing the VBA-Web code in https://github.com/VBA-tools/VBA-Web but I dont know how to add an item with array or collection into the body. I'm quite new to using array or collections for this purpose. Here's the Json data I'm trying to post:

{
  "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"
}

How do I add the location data into this code for posting?

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

Make a new location dictionary with the properties needed, add that dictionary to the body dictionary for the location property. You want a nested dictionary. For the other properties an array should work — Ryan Wildry, Apr 24 '20 at 14:43
I tried that approach already but it didn't work. Dim Location As New Dictionary.... Body.Add "location", Location. Can you provide an example? — Smith O., Apr 24 '20 at 14:46

score 2 · Accepted Answer · answered Apr 24 '20 at 15:56

For most of my JSON work, I use the JsonConverter found in this repository. I created a useful reference for how to structure JSON objects in this answer. Based on the structure, you can add your location as a Dictionary and the arrays as Collections.

I reviewed and verified the JSON output using the JsonConverter, but did not test it with your website.

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

How to add array/collection to VBA POST JSON

1 Answers1