Working with Arrays

An array is an ordered collection of zero or more items. The items of an array are called elements. Arrays cannot contain any NULL values.

The BaggageInfo schema has many arrays. A simple array from the schema is the actions array in every flightLeg. You can use path expressions to navigate a simple array or a nested array.

"actions" : [ {
"actionAt" : "SYD",
"actionCode" : "ONLOAD to SIN",
"actionTime" : "2019.02.28 at 22:09:00 AEDT"
}, {
"actionAt" : "SYD",
"actionCode" : "BagTag Scan at SYD",
"actionTime" : "2019.02.28 at 21:51:00 AEDT"
}, {
"actionAt" : "SYD",
"actionCode" : "Checkin at SYD",
"actionTime" : "2019.02.28 at 20:06:00 AEDT"
} ]

Example 1: Fetch the details of the first leg of every bag (including all the actions taken at the leg) for the passenger with ticket number 1762357254392.

SELECT bagDet.fullName, bagDet.bagInfo[].flightLegs[0]
AS Details FROM BaggageInfo bagDet WHERE ticketNo=1762357254392

In the above query, flightLegs is an array. The slice step [0] is applied to the flightLegs array. Since array elements start with 0, this gives you the first record in the array. You get the first leg information of every bag for each passenger. You apply an additional filter with the ticketNo and so only one passenger information is fetched.

Output:

{"fullName":"Teena Colley",
"Details":[[
{"actionAt":"MSQ","actionCode":"ONLOAD to FRA","actionTime":"2019-02-13T07:17:00Z"},
{"actionAt":"MSQ","actionCode":"BagTag Scan at MSQ","actionTime":"2019-02-13T06:52:00Z"},
{"actionAt":"MSQ","actionCode":"Checkin at MSQ","actionTime":"2019-02-13T06:11:00Z"}],
"2019-02-13T09:00:00Z","2019-02-13T07:00:00Z","BM365","FRA","MSQ"]}

Note:

You can also use a slice step to select all array elements whose positions are within a range: [low: high], where low and high are expressions to specify the range boundaries. You can omit low and high expressions if you do not require a low or high boundary.

Example: Fetch the details of all the legs (including all the actions taken at all the legs) for the passenger with ticket number 1762357254392.

You'll be using the slice step to fetch the first 3 records of the flightLegs array.

SELECT bagDet.fullName, bagDet.bagInfo[].flightLegs[0:2] AS Details
FROM BaggageInfo bagDet WHERE ticketNo=1762357254392

Output:

{"fullName":"Teena Colley",
"Details":[
 [
   {"actionAt":"MSQ","actionCode":"ONLOAD to FRA","actionTime":"2019-02-13T07:17:00Z"},  
   {"actionAt":"MSQ","actionCode":"BagTag Scan at MSQ","actionTime":"2019-02-13T06:52:00Z"},   
   {"actionAt":"MSQ","actionCode":"Checkin at MSQ","actionTime":"2019-02-13T06:11:00Z"}
 ],
 "2019-02-13T09:00:00Z","2019-02-13T07:00:00Z","BM365","FRA","MSQ", 
 [
   {"actionAt":"HKG","actionCode":"Offload to Carousel at HKG","actionTime":"2019-02-13T11:15:00Z"},
   {"actionAt":"FRA","actionCode":"ONLOAD to HKG","actionTime":"2019-02-13T10:39:00Z"},
   {"actionAt":"FRA","actionCode":"OFFLOAD from FRA","actionTime":"2019-02-13T10:37:00Z"}
 ],
 "2019-02-13T11:18:00Z","2019-02-13T07:17:00Z","BM313","HKG","FRA"
]}