Parse JSON file using VBScript - QTP/UFT

Question

I am using QTP/UFT for automating my UI application. I would like to compare the UI values with the values from the REST API response. I'm new to VBScript and I have coded the method to call the REST API and get the response but i'm trying to find a solution how to parse the JSON using VBScript.

Please help me how i could parse the json response? (Code below) OR if it's easier to accept the REST response in xml and parse it in VBS?

Appreciate your help and ideas. Thanks!

userName    =   "[email protected]"
password    =   "blah.123"
acctNumber  =   "01999994201"

URL1="https://CXaic-blah.blah.ocp.blah.com:243/ic/api/integration/v1/flows/rest/blah_ACCNTSEARCH/1.0/accountSearch?accountNumber=" 
URL=URL1&acctNumber
Set objXmlHttpMain = CreateObject("Msxml2.ServerXMLHTTP") 
on error resume next 
objXmlHttpMain.open "GET",URL, False , userName, password
objXmlHttpMain.setRequestHeader "Accept", "application/json"
objXmlHttpMain.setRequestHeader "charset", "UTF-8"
objXmlHttpMain.send

restjsonresp    =   objXmlHttpMain.responseText

Below is the format of the json response i get:

{ 
   "searchResponse":{ 
      "element":[ 
         { 
            "accType":"R",
            "accountNumber":"1111111",
            "accountStatus":"A",
            "taxId":""
         }
      ]
   }
}

Answer 1

While I don't have QTP/UFT to test or verify the following code, I offer-up these JSON parsing solutions as-is for experimentation...

1) Inject a JScript block into a "htmlfile" object

Dim y, html : Set html = CreateObject("htmlfile")
Dim window : Set window = html.parentWindow
window.execScript "var json=" & restjsonresp & ";var e=new Enumerator(json.searchResponse.element);", "JScript"
While Not window.e.atEnd()
    Set y = window.e.item()
    Print "acctType: " & y.accType
    Print "accountNumber: " & y.accountNumber
    Print "accountStatus: " & y.accountStatus
    Print "taxId: " & y.taxId
    window.e.moveNext
Wend

2) Calling JScript code using the "MSScriptControl.ScriptControl" (requires 32-bit)

Dim x, eng : Set eng = CreateObject("MSScriptControl.ScriptControl")
eng.Language = "JScript"
eng.AddCode "function json() { return " & restjsonresp & "; }"
Dim oResp : Set oResp = eng.Run("json")
For Each x In oResp.searchResponse.element
    Print "acctType: " & x.accType
    Print "accountNumber: " & x.accountNumber
    Print "accountStatus: " & x.accountStatus
    Print "taxId: " & x.taxId
Next

3) Injecting a JScript block into "InternetExplorer.Application" (overkill? perfomance hit)

Dim z, objIE : Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate2 "about:blank"
objIE.Toolbar = False
objIE.StatusBar = False
objIE.MenuBar = False
Do While objIE.Busy
    Wait 1
Loop
objIE.Visible = False
objIE.document.open "text/html"
objIE.document.write "<script type='text/javascript'>document.json=" & restjsonresp & ";document.jsonEnum = new Enumerator(document.json.searchResponse.element);</script>"
objIE.document.close
While Not objIE.document.jsonEnum.atEnd()
    Set z = objIE.document.jsonEnum.item()
    Print "acctType: " & z.accType
    Print "accountNumber: " & z.accountNumber
    Print "accountStatus: " & z.accountStatus
    Print "taxId: " & z.taxId
    objIE.document.jsonEnum.moveNext
Wend
objIE.Quit

4) Using Demon's VbsJson object (a pure VBScript solution; albeit, with more code)

https://github.com/eklam/VbsJson

5) Use regular expressions (only for simple, well-defined JSON responses)

Dim re : Set re = New RegExp
re.IgnoreCase = True
re.Pattern = "\{\s*""searchResponse""\s*\:\s*\{\s*""element""\s*\:\s*\[\s*(\{\s*""accType""\s*\:\s*""(.*)""\s*,\s*""accountNumber""\s*\:\s*""(.*)""\s*,\s*""accountStatus""\s*\:\s*""(.*)""\s*,\s*""taxId""\s*\:\s*""(.*)""\s*\})\s*\]\s*\}\s*\}"
If re.Test(restjsonresp) Then
    Dim matches : Set matches = re.Execute(restjsonresp)
    Print "acctType: " & matches(0).SubMatches(1)
    Print "accountNumber: " & matches(0).SubMatches(2)
    Print "accountStatus: " & matches(0).SubMatches(3)
    Print "taxId: " & matches(0).SubMatches(4)
End If

6) Convert JSON to XML, then parse the XML (lots of code, potential overkill)

https://github.com/pravynandas/JSONToXML

If you can control the response, and deliver XML instead of JSON, then it may be preferable to stick with XML for VBScript in QTP/UFT. Regardless, I hope something here is helpful.

Enjoy.