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.

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

Open in new window

LVL 6
worthyking1CTOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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?
0
ste5anSenior DeveloperCommented:
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
0
worthyking1CTOAuthor Commented:
@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.

' #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>"		
	Next

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.  

@ste5an

Can you give me some code samples of how I would do that?  I have no idea where to start.
ParseJSON.asp
0
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
0
worthyking1CTOAuthor Commented:
@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.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
> 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:
//  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)

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

Open in new window


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:
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.
0
worthyking1CTOAuthor Commented:
@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.
1
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
I probably won't be able to get to this tonight, but should have some time in the morning if you can wait.
0
worthyking1CTOAuthor Commented:
@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.
0
worthyking1CTOAuthor Commented:
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
		Next
		Response.Write "OrderTotal:  " & FormatCurrency(i("orderTotal")) & "<BR>"

	Loop While False: Next

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.