POST error using VBA (MS Access) for API to Neto site

Question

I have an access database which I'm using to create a json POST API to a website utilising Neto (https://developers.neto.com.au/documentation/engineers/api-documentation). I am new to APIs, but have been researching for several months and making progress in understanding how it works. I have managed to get a 200 status response from the request which would indicate the header info (including authentication is correct) but error in relation to the body (I believe). Code as per below:

Dim reader As New XMLHTTP60
Dim username As String, APIkey As String
Dim strJson As String
 
strJson = "{" & _
  "'Filter': {" & _
    "'OrderStatus': 'Pick'," & _
    "'OutputSelector': [" & _
      "'OrderID'," & _
      "'ShippingOption'," & _
    "]," & _
  "}" & _
"}"

username = "xxx"
APIkey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

reader.Open "POST", "https://www.yoursite.co.nz/do/WS/NetoAPI", False 
reader.setRequestHeader "NETOAPI_USERNAME", username
reader.setRequestHeader "NETOAPI_KEY", APIkey 
reader.setRequestHeader "Content-Type", "application/json"
reader.setRequestHeader "Accept", "application/json"
reader.setRequestHeader "NETOAPI_ACTION", "GetOrder"
reader.send strJson

Debug.Print reader.Status

Debug.Print reader.responseText

error message:

{"CurrentTime":"2021-09-17 02:42:50","Ack":"Error","Messages":[{"Error":{"Message":"JSON Error","SeverityCode":"Error"},"Warning":{"Message":"Warning","SeverityCode":"Warning","Description":"'\"' expected, at character offset 1 (before \"'Filter': {'OrderSta...\")"}}]}

Initially i am just trying to retrieve 2 pieces of data (OrderID and ShippingOption) for any orders with status of Pick. i have tried replacing all ' with "" as i've seen in other posts along with a few other variations but with no luck.

Any help would be appreciated. Thanks

Answer 1

1. You are using single quote instead of double quotes in your JSON

2. The value of Filter is a collection enclosed with [ ] so I believe you need to wrap Pick as well (despite there's only 1 value).

3. You have an extra , after ShippingOption when it's the last value so remove that.

Try this instead:

strJSON = "{" & _
  """Filter"": {" & _
    """OrderStatus"": [""Pick""]," & _
    """OutputSelector"": [" & _
      """OrderID""," & _
      """ShippingOption""" & _
    "]" & _
  "}" & _
"}"

Above will produce the JSON below:

{"Filter": {"OrderStatus": ["Pick"],"OutputSelector": ["OrderID","ShippingOption"]}}