Parse JSON Array 1

[Swi]

Strongm,

I saw your solution here: thread329-1789285

Would this also work on JSON arrays?

Thanks.

Swi

 

[strongm]

Actually, not easily!

I wrote a longer, more sophisticated version of this code as well, but never posted it to tek-tips as far as I recall. And I managed a head crash on the drive it was on since then, and never bothered to try and recover it

There are a number of json serialiser/deserialiser VBA/VBScript libraries you can google for

or you could look at PowerShell ...

 

[Swi]

Ok, thanks strongm!

Swi

 

[Swi]

strongm,

I looked and found this one.

https://www.aspjson.com/

I am trying to parse the below JSON.

It looks like it can do an array but I am having some issues with pulling that data. I have the other JSON data parsing just fine with the below code. Any ideas? Basically I want to pull the *scans* information.

    oxmlhttp2.Open "GET", url, False
    oxmlhttp2.setRequestHeader "Authorization", bearer
    oxmlhttp2.send
    Set oJSON = New aspJSON
    'Load JSON string
    oJSON.loadJSON(oxmlhttp2.responseText)
    jsontxt = oxmlhttp2.responseText
    response.write jsontxt
    if InStr(1, jsontxt, "Barcode not found.") > 0 then
        response.write "<strong>" & oJSON.data("message") & "</strong>"
    else
        response.write "<strong>IMB:</strong> " & oJSON.data("data").item("imb") & "<br>"
        response.write "<strong>Mail Class:</strong> " & oJSON.data("data").item("mail_class") & "<br>"
        response.write "<strong>Mail Shape:</strong> " & oJSON.data("data").item("mail_shape") & "<br>"
        response.write "<strong>Start The Clock Date:</strong> " & oJSON.data("data").item("start_the_clock_date") & "<br>"
        response.write "<strong>Expected Delivery Date:</strong> " & oJSON.data("data").item("expected_delivery_date") & "<br>"
        response.write "<strong>Anticipated Delivery Date:</strong> " & oJSON.data("data").item("anticipated_delivery_date")
    end if

{"message":null,"data":{"piece_id":"6676bb60-bb4b-11ea-9f8d-f50eb2f71890","mail_class":"Marketing","start_the_clock_facility_city":null,"start_the_clock_facility_zip":null,"mail_shape":"Letter","expected_delivery_date":"2020-07-02","start_the_clock_facility_state":null,"edoc_submitter_crid":"213519","anticipated_delivery_date":"2020-07-03","edoc_job_id":"MWD14375","parent_container_edoc_imcb":[],"scans":[{"scan_date_time":"2020-07-02T19:34:17","scanner_type":"MPE","scan_facility_zip":"17107","mail_phase":"Phase 3c- Destination Sequenced Carrier Sortation","scan_facility_locale_key":"X18801","scan_event_code":"919","handling_event_type":"A","scan_facility_state":"PA","scan_facility_name":"HARRISBURG","machine_name":"DIOSS-016","scan_facility_city":"HARRISBURG"},{"scan_date_time":"2020-07-02T16:23:00","scanner_type":"MPE","scan_facility_zip":"17107","mail_phase":"Phase 3c- Destination Sequenced Carrier Sortation","scan_facility_locale_key":"X18801","scan_event_code":"918","handling_event_type":"A","scan_facility_state":"PA","scan_facility_name":"HARRISBURG","machine_name":"DIOSS-016","scan_facility_city":"HARRISBURG"}],"start_the_clock_facility_locale_key":null,"parent_tray_edoc_imct":[],"start_the_clock_facility_name":null,"start_the_clock_date":"2020-06-29","imb":"0026120083200382963617356814721"}}

Swi

 

[Swi]

Got this sorted.

    for each scan in oJSON.data("data").item("scans")
        set thisscan = oJSON.data("data").item("scans").item(scan)
        response.Write "<strong>Scan Date Time:</strong> " & thisscan.item("scan_date_time")
    next

Swi

 

[strongm]

Actually, now that I've seen your JSOn string, my code would have handled that with a simple (and cleaner)

[tt]For Each scan In .data.scans[/tt]

in the With block

 

[Swi]

Thanks, I'll take a look at your previous code. Appreciate the help!

Swi

 

[strongm]

My version would look something like (but remember that msscript.ocx is a 32bit control, so you need to make sure you run from 32bit cscript or wscript if you platform is 64bit):

[blue]Dim fso 
Dim strText 
Const ForReading=1

Set fso= CreateObject("Scripting.FileSystemObject")
strText = fso.OpenTextFile("G:\Downloads\json.txt", ForReading).ReadAll

Dim scan
Dim oJSON
Set oJSON = vbsJSON(strText)

wscript.echo "IMB: " & oJSON.data.imb
wscript.echo "Mail Class: " & oJSON.data.mail_class
wscript.echo "Mail Shape: " & oJSON.data.mail_shape
wscript.echo "Start The Clock Date: " & oJSON.data.start_the_clock_date
wscript.echo "Expected Delivery Date: " & oJSON.data.expected_delivery_date
wscript.echo "Anticipated Delivery Date:" & oJSON.data.anticipated_delivery_date

For Each scan In oJSON.data.scans
    wscript.echo "Scan Date Time: " & scan.scan_date_time
Next


Function vbsJSON(JSONString)
    With  CreateObject("MSScriptControl.ScriptControl")
            .Language = "JScript"
            Set vbsJSON = .Eval("(" + JSONString + ")")
    End With
End Function[/blue]

 

[Swi]

Thanks, I will try it out!

Swi