Retrieving useable data from JSON

[jantograaf]

Hi all,

I'm trying to create a script that runs a JSON-query and then can retrieve some variables out of the returned, decoded object using JSON.au3. I have tried some other examples on this forum, but I'm stuck at one point. My query works perfectly and gets loaded into the variable $data. Then, decoding $data to $object seems to work as well. But then I can't get the date-field out of this JSON-structure with my script.

The JSON-structure returned looks like this:

expandcollapse popup

{
    "content": [
    {
        "id": "451ec583-8f27-4926-82a3-a2d85e57a110",
        "createdDate": "2018-08-08T08:40:57.449004Z",
        "updatedDate": "2018-08-08T08:40:57.449004Z",
        "lastOpenedDate": "2018-08-08T08:40:57.449004Z",
        "date": "2018-04-26T00:00:00",
        "description": "X-Ray Exam",
        "patient":
        {
            "id": "f857238a-c75d-4760-b8d1-8f50f8f9bbfa",
            "createdDate": "2018-08-08T08:40:37.623976Z",
            "updatedDate": "2018-08-08T08:40:37.623976Z",
            "lastOpenedDate": "2018-08-08T08:40:37.623976Z",
            "name": "Fuerstonia",
            "birthDate": "2014-06-08",
            "breed": "",
            "chip": "",
            "color": "",
            "damsire": "",
            "sire": "",
            "neutered": false,
            "orthancUuid": "",
            "sex": "U",
            "species": "Paard",
            "ueln": "De 431310762114",
            "pmsReference": "",
            "origin": ""
        },
        "type": "study",
        "accessionNumber": "KME201806960467",
        "instanceUid": null,
        "orthancUuid": "",
        "sent": false,
        "seriesCount": 0,
        "modalityType": "RX",
        "typeAndModality": "study RX",
        "client":
        {
            "id": "be627195-8458-4927-8446-f1ef37b917a4",
            "createdDate": "2018-08-08T08:40:31.433968Z",
            "updatedDate": "2018-08-08T08:40:31.433968Z",
            "lastOpenedDate": "2018-08-08T09:26:49.512298Z",
            "via": "",
            "extraInfo": "",
            "pmsReference": "",
            "contact":
            {
                "id": "406cc555-c491-4c29-b6bb-8d903f0e35a9",
                "createdDate": "2018-08-08T08:40:31.428968Z",
                "updatedDate": "2018-08-08T08:40:31.428968Z",
                "lastName": "Client 1",
                "firstName": "",
                "company": "",
                "email": "",
                "language": "nl",
                "phone": "",
                "address":
                {
                    "id": "6fc7703c-137a-4e0a-ba96-8c7f38f2044b",
                    "city": "",
                    "country": "",
                    "line": "",
                    "postalCode": ""
                }
            }
        }
    },

My script looks like this:

expandcollapse popup

#RequireAdmin

#include <json.au3>
#include <inet.au3>
#include <File.au3>

;Create a handle to a logfile (will be created if it doesnt exist)
Global $logfile = FileOpen("C:\VSOL\VSTK\Logs\MigrationFix\MigrationFix.log",9)
FileWriteLine($logfile,"Start script")

;Create the URL with the JSON-query
$URL = "http://localhost:8080/v0/studies/?seriesCount=1"
;Catch the query output into a variable
$data = _INetGetSource($URL)
;Check if there is any data at all in this variable, if not, exit
If Not $data Then
  MsgBox(1,"Error","No answer. Server is probably not running.")
  Exit
EndIf

;For debugging purposes
FileWriteLine($logfile,$data)

;Decode the JSON_string into a useable object
$object = Json_Decode($data,1000)
If @error Then
  FileWriteLine($logfile,"Error decoding JSON")
  Exit
EndIf

Local $i = 0

;Start a loop to retrieve the study date of each study...
While 1
  $study_date = json_get($object,'[' & $i & '].date')
  If @error Then 
    FileWriteLine($logfile,"Study-Date retrieval error")
    ExitLoop
  EndIf
  $i = $i + 1
WEnd

;Close the logfile
FileWriteLine($logfile,"Stop script")
FileClose($logfile)

;Open the logfile for quick reference
ShellExecute("C:\VSOL\VSTK\Logs\MigrationFix\MigrationFix.log")

If believe it has something to do with my json_get($object...)-command. Anyone who can point me in the right direction?

Thanks in advance!

Kind regards

Edited November 28, 2018 by jantograaf
Pushed enter too soon :-)

[TheXman]

Lookup and use the json_dump() function, in the json.au3 UDF file, to get a better idea of the naming scheme used to reference individual items.
 

Spoiler

 

I think this will work:

json_get($object,'.content[' & $i & '].date')

Disclaimer:  The suggestion is untested.

 

 

Edited November 28, 2018 by TheXman

[jantograaf]

I seem to be using a different JSON.au3, because my version (by Ward & zserge) doesn't include this json_dump()-function?

Could you point me to a newer/more correct version please? And thanks again for replying so quickly to my question.

[TheXman]

 

[TheXman]

I'm not sure if you noticed, but if you click "reveal hidden contents", in my first post, I suggested a solution.

Edited November 28, 2018 by TheXman

[TheXman]

On a side note, in general, you may run into problems processing an array like this.  If you don't encounter an "Index out of bounds" exception, you may end up in an endless loop or worse depending on the function calls. 

 

;Start a loop to retrieve the study date of each study...
While 1
  $study_date = json_get($object,'[' & $i & '].date')
  If @error Then 
    FileWriteLine($logfile,"Study-Date retrieval error")
    ExitLoop
  EndIf
  $i = $i + 1
WEnd

Below, in the hidden content, you will see an example that shows 2 different ways of processing your array.  One example uses a ForNext and the other uses a ForEach.  The ForEach would most likely be used only if you wanted to process all of the items in an array.  However, you could always set a condition in which you exited the loop. 

If you want to use an endless loop to process an array, then you should test to make sure the index is not out of bounds before referencing any array item.  i.e.  If $i < Ubounds($aSomeArray) Then <do process> Else ExitLoop.
 

Spoiler

 

expandcollapse popup

#Include <json.au3> ;<-- Change this line to point to your UDF

example()
Func example()
    Const $kJSON_STRING = _
        '{' & _
        '   "content": [' & _
        '       {' & _
        '           "date": "2018-04-26T00:00:00",' & _
        '           "description": "X-Ray Exam"' & _
        '       },' & _
        '       {' & _
        '           "date": "2018-07-03T00:00:00",' & _
        '           "description": "EKG"' & _
        '       }' & _
        '   ]' & _
        '}'


    Local $oJson      = Json_Decode($kJSON_STRING)
    Local $iItemCount = 0

    ConsoleWrite('***  Array processed using ForNext  ***' & @CRLF)
    $iItemCount = UBound(Json_Get($oJson, '.content'))
    For $i = 0 To $iItemCount - 1
        ;Process "content" item
        ConsoleWrite( _
            StringFormat("Description: %s (%s)\r\n", _
                Json_Get($oJson, StringFormat(".content[%i].description", $i)), _
                Json_Get($oJson, StringFormat(".content[%i].date"       , $i)) _
            ) _
        )
    Next

    ConsoleWrite(@CRLF & '***  Array processed using ForEach  ***' & @CRLF)
    For $oContentItem in Json_Get($oJson, '.content')
        ;Process "content" item
        ConsoleWrite( _
            StringFormat("Description: %s (%s)\r\n", _
                Json_Get($oContentItem, ".description"), _
                Json_Get($oContentItem, ".date"       ) _
            ) _
        )
    Next
EndFunc

 

 

 

 

 

Edited November 28, 2018 by TheXman

[jantograaf]

Hi @TheXman,

Sorry for the late reply, but indeed, I didn't notice the solutions proposed in the hidden contents. I only got time now to continue working on the project so I'll see what your suggestions will do, but I'm quite sure that they will help me make some big steps forward. 

The moving through the array was copy-pasted from another script where they demonstrated the usage of this json.au3, so I didn't quite write or check this myself, but you have a very valid point about the risk of running out of bounds or ending up in an endless loop. I'll integrate your solution or I'll try to use the Json_GetCount()-function which I believe also exists in the json.au3 you pointed me to.

Thanks a lot for your support! :-)

Kind regards,

Jan