How to handle fields from a JSON data source that starts with an array?

[lee.irvine]

A valid form of a JSON document begins as an Array element. How do you access any element using the JsonDataSource "jsonpath" to query it? I have tried using the standard $[0] for the top level fields and as "$" for creating my subdataset but neither works... when I place the array under a standard element it works fin,e but that is not the format of our JSON.

My JSON loks a bit like this:

[
  {
     "race": {
         "raceId": "1.33.1141109.2",
         "startDate": "2014-11-09T13:15:00.000Z",
         "raceClassification": {
            "classification": "Novices'"
         },
         "raceType": {
            "key": "H"
         },
         "raceClass": 4,
         "course":          {
            "courseId": "1.33"
         },
         "meetingId": "1.33.1141109"
      },
      "numberOfRunners": 2,
      "runners": [
         {
            "horseId": "1.00387464",
            "trainer": {
               "trainerId": "1.00034060"
            },
            "ownerColours": "Maroon, pink sleeves, dark blue cap."
         },
         {
            "horseId": "1.00373620",
            "trainer": {
               "trainerId": "1.00010997"
            },
            "ownerColours": "Black, emerald green cross of lorraine, striped sleeves."
         }
      ]
   },
   {
      "race": {
         "raceId": "1.33.1141109.3",
         "startDate": "2014-11-09T13:45:00.000Z",
         "raceClassification":  {
            "classification": "Handicap"
         },
         "raceType": {
            "key": "C"
         },
         "raceClass": 4,
         "course": {
            "courseId": "1.33"
         },
         "meetingId": "1.33.1141109"
      },
      "numberOfRunners": 2,
      "runners": [
         {
            "horseId": "1.00297339",
            "trainer":             {
               "trainerId": "1.00000577"
            },
            "ownerColours": "Maroon and light blue (quartered), maroon sleeves."
         },
         {
            "horseId": "1.00333030",
            "trainer":             {
               "trainerId": "1.00000065",
            },
            "ownerColours": "Emerald green, yellow hoops, white cap."
         }
      ]
   }
]

[calculate.machine]

     

[calculate.machine]

I don’t think you describe your problem clearly enough. Maybe you want this: Input the parameter (1 or 2) and get from the JSON document three fields of the specified members of the array: horseId, trainerId,ownerColours; then the result should be as follows if the input parameter is defined as 1:

1.00387464  1.00034060  Maroon, pink sleeves, dark blue cap.

1.00373620  1.00010997  Black, emerald green cross of lorraine, striped sleeves.

Based on this, I tried to solve the problem using Jasper but all I got is null. Is this the problem to which you are trying to get a solution?

I tried another method later. I used esProc to assist Jasper in processing the Json data and it worked well. The esProc code is as follows:

A1=file("D:\jsonstr.json").read()    / Read the file out as the string

A2=A1.import@j()                         / Parse the json string

A3=A2(which).runners                   / Get the specified members of the array according to the parameter defined; “which” is the parameter name

A4=A3.new(horseId,trainer.trainerId:trainerId,ownerColours)             / Get three fields; trainerId needs to be got from the sublevel of data  

A5 result A4                                    / Return the result of A4 to Jasper

The code can be combined into one single line：result file("D:\jsonstr.json").read().import@j()(which).new(horseId,trainer.trainerId:trainerId,ownerColours) 

Jasper can connect to esProc through JDBC. The way it calls the esProc script is the same as that it calls the stored procedure. For details please refer to http://blog.raqsoft.com/?p=2447 .

 

 

The final report is as follows：

[narcism]

You can select an array element directly with:

[index]

Absolute path expressions are not supported in the current language for querying JSON data.

Starting with JasperReports Library v6.3.1, a new JSON query language(JSONQL) will be available for use. It is meant to replace the existing language for querying JSON data. New sample with extensive documentation will be available in the Data Source / Query Executer section of the sample reference page(http://jasperreports.sourceforge.net/sample.reference.html) once the release is out.