【Question】

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 fine but that is not the format of our JSON.

My JSON looks 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.”  } ] } ]

 

Enter a parameter (like 1 or 2) to retrieve three fields under a specified element in the JSON array: horseId, trainerId, ownerColours. If the parameter value is 1, the result will be like this:

1.003874641.00034060Maroon, pink sleeves, dark blue cap.

1.003736201.00010997Black, emerald green cross of lorraine, striped sleeves.

 

【Answer】

It’s fast and convenient to get the desired data if we handle the JSON document in SPL. Below is the SPL script:

	A
1	=file(“d:\\data.json”).read()
2	=json(A1)
3	=A2(which).runners
4	=A3.new(horseId,trainer.trainerId:trainerId,ownerColours)

 

Result of executing the SPL script:

A1: Read in the JSON string;

A2: Parse the JSON string into a table sequence;

A3: Specify the element in the array under which the fields will be retrieved to generate a two-dimensional table based on a node; which is the parameter name, whose value can be 1 or 2 here;

A4: Extract the three desired fields, in which the trainerId field will be extracted from a second-level node, to generate a new table sequence.