How to easily loop through and parse JSON array(s) within existing JSON object

worthyking1 used Ask the Experts™
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 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.

{"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":"","customerEmail":"","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":"","customerEmail":"","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}

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Big MontyWeb Ninja at large

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?
ste5anSenior Developer

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
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:   (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 = "" "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>"		

Open in new window

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)].orderId or any other variation I have tried.

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.  


Can you give me some code samples of how I would do that?  I have no idea where to start.
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Big MontyWeb Ninja at large

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
@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


JSON Parser Class

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.
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

> 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:
$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": ""

$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)

Open in new window

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","",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 = nothing

Open in new window

Then track-a-package.php would call 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)

Open in new window

If you want to pursue this, let us know, otherwise, Big Monty can help with the using aspJson.
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.
Big MontyWeb Ninja at large

I probably won't be able to get to this tonight, but should have some time in the morning if you can wait.
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.
So, I figured out how to do it using the original JSON Parser by demon.

1. First I had to skip the JS parser function and go straight to the VB Decode function. This allows me to loop through the arrays using a standard UBound For Loop and index.

2. Second, I had to fix the Decode function so that it didn't bomb out when encountering empty objects or arrays, by adding the following to the Decode function:

str=Replace(str,"[]","[""""]")       ' This handles empty arrays by replacing them with empty quotes
str=Replace(str,"{}","{""""}")      '  This handles empty objects by replacing them with empty quotes

Open in new window

And here's a sample of my (now) working code:

	Set json = New VbsJson
	Set o = json.Decode(APIstrResponse)       ' parse API response
	For Each i In o("orders"): Do
		' First check if Manual/Auto so we can skip it right away
		ManualAuto = Trim(i("advancedOptions")("customField1"))
		If ManualAuto = "A" OR ManualAuto = "M" then response.Write("Manual/Autoship order....skipping this import!"): Exit Do
		' Populate vars
		OrderID = CLng(i("orderId"))
		OrderNumber = Trim(i("orderNumber"))
		OrderDate = Trim(i("orderDate"))
		StoreID = Trim(i("advancedOptions")("storeId"))
		ShipToName = Trim(i("shipTo")("name"))
		ShipToCompany = Trim(i("shipTo")("company") )
		ShipToStreet1 = Trim(i("shipTo")("street1") )
		ShipToStreet2 = Trim(i("shipTo")("street2") )
		ShipToCity = Trim(i("shipTo")("city") )
		ShipToState = Trim(i("shipTo")("state") )
		ShipToZip = Trim(i("shipTo")("postalCode") )
		ShipToCountry = Trim(i("shipTo")("country") )
		ShipToPhone = Trim(i("shipTo")("phone") )
		Response.Write "Order Status:  " & OrderStatus & "<BR>"
		Response.Write "OrderID:  " & OrderID & "<BR>"
		Response.Write "OrderNumber:  " & OrderNumber & "<BR>"
		Response.Write "OrderDate:  " & OrderDate & "<BR>"
		Response.Write "ManualAuto:  " & ManualAuto & "<BR>"
		Response.Write "Store:  " &  StoreID & "<BR>"
		Response.Write "ShipTo:  " & ShipToName & "<BR>"
		Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" & ShipToStreet1 & "<BR>"
		Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" & ShipToCity & "," & ShipToState & " " & ShipToZip & "<BR>"
		Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" & ShipToCountry & "<BR>"
		pc = 1
		For Each p in i("items")        ' loop through order line items array
			Response.Write "Item # " & pc & ": " & p("sku") & "   " & p("name") & "<BR>"
			pc = pc +1
		Response.Write "OrderTotal:  " & FormatCurrency(i("orderTotal")) & "<BR>"

	Loop While False: Next

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial