JSON read from file issue

[sr4uk]

Hello.  I am connecting to our FreshDesk instance through API and reading in the JSON.  I need to capture and format the data for every agent in the system.  FD uses pagination and right now there are 31 pages (yes I know my code only looks at the first few, that was done intentionally for testing).  I can receive the JSON from FreshDesk and write it to a file, appending each response to the file.  I can open said file and see each response put into the file.  When I read the file into a variable to pass to the parser, it is only reading and passing the first response from the file, not the whole file. 

 

The end goal is to have all of the agents in 1 variable so I can pass that to the json_dump and have it parse the json using a unique key for each person.  

 

expandcollapse popup

;////////////////////////Get Agents NA Sandbox Button////////////////////////
Case $aPanel[1]
Switch $nMsg[0]
Case $getAgentsNAsandbox
    Local $pageNum = 1
    Local $headerReceived = 'rel="next"'
    Local $url = ""
    Local $rel='rel="next"'
    Local $fullData = ""
    Local $testRel = "True"
    Local $sendKey = 1
    Local $login =_Base64Encode($NAapiKey & ":" & $APIpassword)
    Local $openFile = FileOpen($userPathMain, $FO_APPEND)
    $oHTTP = ObjCreate("winhttp.winhttprequest.5.1")
        While $testRel = "True" And $pageNum < 3
            $url = $NAsandboxLink & "agents?page=" & $pageNum
            $oHTTP.Open("Get", $url)
            $oHTTP.SetRequestHeader('Authorization', 'Basic ' & $login)
            $oHTTP.Send()
            ;$oHttp.WaitForResponse()
            $headerReceived = $oHTTP.GetAllResponseHeaders()
            $oReceived = $oHTTP.ResponseText
            FileWriteLine($openFile, $oReceived)
            $oStatusCode = $oHTTP.Status
                If StringInStr($headerReceived, $rel) Then
                    $testRel = "True"
                Else
                    $testRel = "False"
                EndIf
        WEnd
    FileSetPos($openFile, 0, $FILE_BEGIN)
    $fullData = FileRead($openFile)
    FileClose($openFile)
    Local $dump = Json_Dump($fullData)
    
EndSwitch
;///////////////////////////////////////////////////////////////////////////////////

 

[Danp2]

It's possible that the While loop only processed one request. Have you checked the file's contents?

[sr4uk]

15 minutes ago, Danp2 said:

It's possible that the While loop only processed one request. Have you checked the file's contents?

Yup. File contents contains multiple responses

[Danp2]

You could try closing the file, then attempt to read it. If it still doesn't work, then I'm guessing that there's something being written to the file like and EOF character that is interfering with the FileRead. Have you tried adding the $FO_BINARY flag?

[sr4uk]

24 minutes ago, Danp2 said:

You could try closing the file, then attempt to read it. If it still doesn't work, then I'm guessing that there's something being written to the file like and EOF character that is interfering with the FileRead. Have you tried adding the $FO_BINARY flag?

If I throw it to the console it shows both runs being listed but it only creates from the first run on the json function.  I tried closing the file then reading and you can see it's all in there.  I'm not familiar with the binary flag, what would that change?  

[sr4uk]

I figured it out.  It didn't like the }] and [{ at the end and start of each run.  I added some code to read in the file and then did a string replace to make it one large statement and it worked beautifully.  This is what happens when you've been looking at something for too long.  😅

 

Quote

Case $aPanel[1]
Switch $nMsg[0]
Case $getAgentsNAsandbox
    Local $pageNum = 1
    Local $headerReceived = 'rel="next"'
    Local $url = ""
    Local $rel='rel="next"'
    Local $fullData = ""
    Local $testRel = "True"
    Local $login =_Base64Encode($NAapiKey & ":" & $APIpassword)
    Local $openFile = FileOpen($userPathMain, $FO_APPEND)
    $oHTTP = ObjCreate("winhttp.winhttprequest.5.1")
        While $testRel = "True" And $pageNum < 3
            $url = $NAsandboxLink & "agents?page=" & $pageNum
            $oHTTP.Open("Get", $url)
            $oHTTP.SetRequestHeader('Authorization', 'Basic ' & $login)
            $oHTTP.Send()
            ;$oHttp.WaitForResponse()
            $headerReceived = $oHTTP.GetAllResponseHeaders()
            $oReceived = $oHTTP.ResponseText
            FileWriteLine($openFile, $oReceived)
            $oStatusCode = $oHTTP.Status
                If StringInStr($headerReceived, $rel) Then
                    $testRel = "True"
                    $pageNum = $pageNum + 1
                Else
                    $testRel = "False"
                EndIf
        WEnd
    FileSetPos($openFile, 0, $FILE_BEGIN)
    $fullData = FileRead($userPathMain)
    $fullData = StringReplace($fullData, "}]" & @CRLF & "[{", ", ")
    FileClose($openFile)
    ConsoleWrite($fullData)
    FileClose($openFile)
    Local $dump = Json_Dump($fullData)
    
EndSwitch
;///////////////////////////////////////////////////////////////////////////////////

 

Edited April 28, 2023 by sr4uk

[Danp2]

Glad you found a solution. FWIW, I think that you may have been incorrect when you previously said "it is only reading and passing the first response from the file, not the whole file". In the image above, it shows accessing each node within an array. So you should be able to use something like "[1].avaliable" to access the next row in the array.

[TheXman]

I think the original issue was that the OP was appending several distinct JSON datasets into a single file and then trying to process all of them at once using the JSMN-based UDF library.  The JSMN-based UDF library, as it currently stands, cannot properly parse multiple distinct JSON datasets at once.

The example below shows how it could be handled using the jq JSON processor.  Among many other features, jq has the ability to process multiple JSON datasets without any issues.  The example could've used the _jqExecFile() function to process the JSON from a file, but for the purpose of this example it just used a JSON string as it would appear in the file.  Using jq, there's no need to modify the structure of the JSON in the file before processing. 

jqPlayground shows the filter in action and the script shows how to implement the filter that was created & tested in jqPlayground.

 

 

expandcollapse popup

#AutoIt3Wrapper_AU3Check_Parameters=-w 3 -w 4 -w 5 -w 6 -d

#include <Constants.au3>
#include <jq\jq.au3>
#include <Array.au3>

Const $JQ_EXE = "C:\Utils\jq\jq-win64.exe" ;<== Change path as needed

Const $JSON_DATA = _
    '[{"available":false, "id":11111}, {"available":false, "id":22222}]' & @CRLF & _
    '[{"available":false, "id":33333}]' & @CRLF & _
    '[{"available":false, "id":55555}]'


jq_example()

Func jq_example()
    Local $sResult = ""
    Local $aResult[0]

    ;Init jq
    _jqInit($JQ_EXE)
    If @error Then Return MsgBox($MB_ICONERROR, "_jqInit() Error", "@error = " & @error)

    ;Process JSON dataset to get list of ID's
    $sResult = _jqExec($JSON_DATA, '.[].id')
    If @error Then Return MsgBox($MB_ICONERROR, "_jqExec() Error", $sResult)

    ;Display result
    ConsoleWrite(_jqPrettyPrintJson($JSON_DATA, "   ") & @CRLF & @CRLF)
    ConsoleWrite("List of IDs" & @CRLF)
    ConsoleWrite($sResult & @CRLF)

    ;Add IDs to an array and display it
    _ArrayAdd($aResult, $sResult, 0, @CRLF)
    _ArrayDisplay($aResult)
EndFunc

Console output:

[
  {
    "available": false,
    "id": 11111
  },
  {
    "available": false,
    "id": 22222
  }
]
[
  {
    "available": false,
    "id": 33333
  }
]
[
  {
    "available": false,
    "id": 55555
  }
]

List of IDs
11111
22222
33333
55555

Edited April 28, 2023 by TheXman