How to easily loop through and parse JSON array(s) within existing JSON object
I am pulling my hair out on parsing a JSON API response (from ShipStation) due to the arrays within the response object. I have always used a JSON parser (from demon.tw) which works perfectly for accessing the various items in a JSON object, however it does NOT work with arrays because of the fact that it is using JScript one cannot loop with a variable index.
I have tried a few other JSON classes but still had no success in being able to easily loop through each order and pull the relevant order information for each.
Below is a sample of the JSON response from the API. Can someone please help me loop through the orders (only 2 orders in the array in the sample below), and populate all the necessary order variables on each loop, such as OrderID, Customer info, billing/shipping addresses, line items ordered, priciing etc.
I have tried a few other JSON classes but still had no success in being able to easily loop through each order and pull the relevant order information for each.
Below is a sample of the JSON response from the API. Can someone please help me loop through the orders (only 2 orders in the array in the sample below), and populate all the necessary order variables on each loop, such as OrderID, Customer info, billing/shipping addresses, line items ordered, priciing etc.
{"orders":[{"orderId":123456789,"orderNumber":"12345","orderKey":"12345","orderDate":"2018-09-06T09:37:34.0000000","createDate":"2018-09-06T10:35:28.7430000","modifyDate":"2018-09-06T10:35:30.5670000","paymentDate":"2018-09-06T09:37:34.0000000","shipByDate":"2018-09-06T12:00:00.0000000","orderStatus":"awaiting_shipment","customerId":137841866,"customerUsername":"123abc@marketplace.amazon.com","customerEmail":"123abc@marketplace.amazon.com","billTo":{"name":"Jill","company":null,"street1":null,"street2":null,"street3":null,"city":null,"state":null,"postalCode":null,"country":null,"phone":null,"residential":null,"addressVerified":null},"shipTo":{"name":"Joe Blow","company":null,"street1":"123 Test Lane","street2":"","street3":null,"city":"SAN DIEGO","state":"CA","postalCode":"92103-1892","country":"US","phone":"666-555-1212","residential":true,"addressVerified":"Address validated successfully"},"items":[{"orderItemId":349877804,"lineItemKey":"54124319742610","sku":"B000000001","name":"Product 1","imageUrl":"","weight":{"value":5.00,"units":"ounces","WeightUnits":1},"quantity":1,"unitPrice":59.70,"taxAmount":4.19,"shippingAmount":7.00,"warehouseLocation":null,"options":[],"productId":27940881,"fulfillmentSku":"SKU123","adjustment":false,"upc":null,"createDate":"2018-09-06T10:35:28.85","modifyDate":"2018-09-06T10:35:28.85"}],"orderTotal":61.93,"amountPaid":61.93,"taxAmount":4.19,"shippingAmount":7.00,"customerNotes":null,"internalNotes":"","gift":false,"giftMessage":null,"paymentMethod":"Other","requestedShippingService":"Second US D2D Dom","carrierCode":"stamps_com","serviceCode":"usps_first_class_mail","packageCode":"package","confirmation":"none","shipDate":null,"holdUntilDate":null,"weight":{"value":5.00,"units":"ounces","WeightUnits":1},"dimensions":null,"insuranceOptions":{"provider":null,"insureShipment":false,"insuredValue":0.0},"internationalOptions":{"contents":null,"customsItems":null,"nonDelivery":null},"advancedOptions":{"warehouseId":355209,"nonMachinable":false,"saturdayDelivery":false,"containsAlcohol":false,"mergedOrSplit":false,"mergedIds":[],"parentId":null,"storeId":370777,"customField1":null,"customField2":null,"customField3":null,"source":null,"billToParty":null,"billToAccount":null,"billToPostalCode":null,"billToCountryCode":null,"billToMyOtherAccount":null},"tagIds":[88175],"userId":null,"externallyFulfilled":false,"externallyFulfilledBy":null,"labelMessages":null},{"orderId":987654321,"orderNumber":"54321","orderKey":"54321","orderDate":"2018-09-06T09:19:48.0000000","createDate":"2018-09-06T10:35:28.5400000","modifyDate":"2018-09-06T10:35:30.5670000","paymentDate":"2018-09-06T09:19:48.0000000","shipByDate":"2018-09-07T12:00:00.0000000","orderStatus":"awaiting_shipment","customerId":1001,"customerUsername":"abc321@marketplace.amazon.com","customerEmail":"abc321@marketplace.amazon.com","billTo":{"name":"John Q. Citizen","company":null,"street1":null,"street2":null,"street3":null,"city":null,"state":null,"postalCode":null,"country":null,"phone":null,"residential":null,"addressVerified":null},"shipTo":{"name":"John Q. Citizen","company":null,"street1":"100 Shipping Lane","street2":"","street3":null,"city":"Testville","state":"CA","postalCode":"95207","country":"US","phone":"999-888-7777","residential":true,"addressVerified":"Address validated successfully"},"items":[{"orderItemId":349877800,"lineItemKey":"48845313641770","sku":"SKU2","name":"Product 2","imageUrl":"none","weight":{"value":3.00,"units":"ounces","WeightUnits":1},"quantity":1,"unitPrice":45.20,"taxAmount":3.79,"shippingAmount":6.99,"warehouseLocation":"1 Warehouse Lane, Warehouse Town, UT 84088","options":[],"productId":26129123,"fulfillmentSku":"RHP-852029007009","adjustment":false,"upc":"852029007009","createDate":"2018-09-06T10:35:28.743","modifyDate":"2018-09-06T10:35:28.743"}],"orderTotal":55.98,"amountPaid":55.98,"taxAmount":3.79,"shippingAmount":6.99,"customerNotes":null,"internalNotes":"","gift":false,"giftMessage":null,"paymentMethod":"Other","requestedShippingService":"Std US D2D Dom","carrierCode":"stamps_com","serviceCode":"usps_first_class_mail","packageCode":null,"confirmation":"none","shipDate":null,"holdUntilDate":null,"weight":{"value":3.00,"units":"ounces","WeightUnits":1},"dimensions":null,"insuranceOptions":{"provider":null,"insureShipment":false,"insuredValue":0.0},"internationalOptions":{"contents":null,"customsItems":null,"nonDelivery":null},"advancedOptions":{"warehouseId":355209,"nonMachinable":false,"saturdayDelivery":false,"containsAlcohol":false,"mergedOrSplit":false,"mergedIds":[],"parentId":null,"storeId":370777,"customField1":null,"customField2":null,"customField3":null,"source":null,"billToParty":null,"billToAccount":null,"billToPostalCode":null,"billToCountryCode":null,"billToMyOtherAccount":null},"tagIds":[88175],"userId":null,"externallyFulfilled":false,"externallyFulfilledBy":null,"labelMessages":null}],"total":7,"page":1,"pages":4}
Big Monty
just to be clear, you are trying to loop through the response server side, correct? could you show the code you're trying to use, along with what json library you're using?
First of all: There is no JSON object or JSON array in this context. The entire string is JSON and can be deserialized to a óbject with properties which are arrays.
See this thread for more information: Calling a JSON feed using Classic ASP/VB Script
See this thread for more information: Calling a JSON feed using Classic ASP/VB Script
ASKER
@BigMonty
Yes, I am trying to loop through the response server side, and access all the values in each order for further work (writing to DB etc.)
I am using a JSON parser found here: http://demon.tw/my-work/vbs-json.html (full class include file attached to his post)
I have been using this class for some time now, and it works great for parsing JSON strings, but I've never run into having to deal with arrays in the strings before.
Here's a code snippet.
The above code works IF I call each value manually, such as with:
SSorderId = JsonStr.orders.[0].orderId
BUT, because this class is using JScript to do the parsing work it uses a string literal for the index, so "i" does not work to represent the index value. Neither does JsonStr.orders.[CStr(i)].o
I could not figure out a way around that, so I have been trying various other JSON parser classes, but so far have not had any luck.
@ste5an
Can you give me some code samples of how I would do that? I have no idea where to start.
ParseJSON.asp
Yes, I am trying to loop through the response server side, and access all the values in each order for further work (writing to DB etc.)
I am using a JSON parser found here: http://demon.tw/my-work/vbs-json.html (full class include file attached to his post)
I have been using this class for some time now, and it works great for parsing JSON strings, but I've never run into having to deal with arrays in the strings before.
Here's a code snippet.
' #1 Get Orders from ShipStation that are AWAITING_SHIPMENT and Tagged as "Not Yet Exported" (Tag ID: 88175)
Set objWinHttp = Server.CreateObject("WinHttp.WinHttpRequest.5.1")
strURL = "https://ssapi.shipstation.com/orders/listbytag?orderStatus=awaiting_shipment&tagId=88175&page=1&pageSize=2"
objWinHttp.open "GET", strURL, False
objWinHttp.setRequestHeader "Authorization", "Basic " & APIkey
objWinHttp.send msg
TheStatus = CInt(objWinHttp.Status)
If TheStatus = 200 then
strResponse = objWinHttp.ResponseText
'Parse the JSON response string
Set JsonStr = ParseJson(strResponse)
NumOrders = CInt(JsonStr.orders.length)
Response.Write "<br># Orders = " & NumOrders & "<br><br>"
For i = 0 to NumOrders-1
SSorderId = JsonStr.orders.[i].orderId
Response.Write "<br>SSorderId = " & SSorderId & "<br>"
NextThe above code works IF I call each value manually, such as with:
SSorderId = JsonStr.orders.[0].orderId
BUT, because this class is using JScript to do the parsing work it uses a string literal for the index, so "i" does not work to represent the index value. Neither does JsonStr.orders.[CStr(i)].o
I could not figure out a way around that, so I have been trying various other JSON parser classes, but so far have not had any luck.
@ste5an
Can you give me some code samples of how I would do that? I have no idea where to start.
ParseJSON.asp
are you open to using a different json parser? i'll look at this one, but if i cant figure it out, i can attach the one i use for things like this
ASKER
@Big Monty
Yes, I'm open to any solution that works! So far I've also tried the following:
JSON object class 3.8.1 May, 29th - 2016
https://github.com/rcdmk/aspJSON
and
JSON Parser Class
https://www.jsware.net/jsware/scrfiles.php5#json
The problem is that without full documentation (read: code samples for what I need to do), and being a JSON newb, I have not been able to get either of the above to work for my needs.
Yes, I'm open to any solution that works! So far I've also tried the following:
JSON object class 3.8.1 May, 29th - 2016
https://github.com/rcdmk/aspJSON
and
JSON Parser Class
https://www.jsware.net/jsware/scrfiles.php5#json
The problem is that without full documentation (read: code samples for what I need to do), and being a JSON newb, I have not been able to get either of the above to work for my needs.
> I'm open to any solution that works!
Working with arrays and objects is one of the things that pushed me out of working with asp. In php for example:
The output will be:
Tracking Number: 9405511899223197428490
Postal Code: 78756
The key is there is no looping to get what you want.
To implement this, you don't have to learn everything about php and you will find some similarities. You can create a several php pages that capture posted data, run it through the api, parse the response and echo out what you need. Then call the php page using classic asp through xmlhttppost or using an ajax request. This allows you to continue everything in classic asp and just use php for the api.
Your asp page would look like
Then track-a-package.php would call https://docs.shipengine.com/docs/track-a-package and can return comma delimited text like "tracking_number, postal_code" without hierarchy. Then you can use the split function to convert the text to an array. From there:
If you want to pursue this, let us know, otherwise, Big Monty can help with the using aspJson.
Working with arrays and objects is one of the things that pushed me out of working with asp. In php for example:
// REPRESENTS A JSON RESPONSE
$response =<<<EOD
{
"tracking_number": "9405511899223197428490",
"status_code": "DE",
"status_description": "Delivered",
"carrier_status_code": "01",
"carrier_status_description": "Your item was delivered in or at the mailbox at 9:10 am on March 2, 2017 in AUSTIN, TX 78756.",
"ship_date": "2018-09-06T20:49:09.549Z",
"estimated_delivery_date": null,
"actual_delivery_date": "2018-09-06T20:49:09.549Z",
"exception_description": null,
"events": [
{
"occurred_at": "2018-09-06T20:49:09.549Z",
"description": "Delivered, In/At Mailbox",
"city_locality": "AUSTIN",
"state_province": "TX",
"postal_code": "78756",
"country_code": "",
"company_name": "",
"signer": ""
}
]
}
EOD;
$json= json_decode($response); //CONVERT THE JSON RESPONSE TO SOMETHING PHP CAN USE
echo "Tracking Number: ". $json->{"tracking_number"}; // TOP LEVEL
echo "<br>";
echo "Postal Code: ". $json->{"events"}[0]->{"postal_code"}; //THIRD LEVEL (RESPONSE -> EVENTS -> POSTAL_CODE)The output will be:
Tracking Number: 9405511899223197428490
Postal Code: 78756
The key is there is no looping to get what you want.
To implement this, you don't have to learn everything about php and you will find some similarities. You can create a several php pages that capture posted data, run it through the api, parse the response and echo out what you need. Then call the php page using classic asp through xmlhttppost or using an ajax request. This allows you to continue everything in classic asp and just use php for the api.
Your asp page would look like
<%
DataToSend = "tracking_number=9405511899223197428490"
dim xmlhttp
set xmlhttp = server.Createobject("MSXML2.ServerXMLHTTP")
xmlhttp.Open "POST","http://mysite.com/track-a-package.php",false
xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
xmlhttp.send DataToSend
Response.ContentType = "text/plain"
'Response.Write xmlhttp.responseText
data = xmlhttp.responseText
Set xmlhttp = nothingThen track-a-package.php would call https://docs.shipengine.com/docs/track-a-package and can return comma delimited text like "tracking_number, postal_code" without hierarchy. Then you can use the split function to convert the text to an array. From there:
response = split(data,",")
tracking_number = response(0)
postal_code = response(1)If you want to pursue this, let us know, otherwise, Big Monty can help with the using aspJson.
ASKER
@Scott
Thanks for the suggestion, but this is definitely not a solution that I would try and implement. The purpose of this code is to import orders from ShipStation on a dialy basis. The volume of orders is great, and the complexity of each order is also great (each order contains multiple levels of data points and arrays etc.), so it's not something I'd be comfortable trying to pass between pages in comma-delimited forms.
Thanks for the suggestion, but this is definitely not a solution that I would try and implement. The purpose of this code is to import orders from ShipStation on a dialy basis. The volume of orders is great, and the complexity of each order is also great (each order contains multiple levels of data points and arrays etc.), so it's not something I'd be comfortable trying to pass between pages in comma-delimited forms.
I probably won't be able to get to this tonight, but should have some time in the morning if you can wait.
ASKER
@BigMonty
Whatever you can do to help (and whenever) would be GREATLY appreciated. I guess I'll have to set it aside for now as I'm at my wits end after wasting hours and hours trying different things that are not working.
Whatever you can do to help (and whenever) would be GREATLY appreciated. I guess I'll have to set it aside for now as I'm at my wits end after wasting hours and hours trying different things that are not working.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.