starts_with and ends_with functions

The starts_with function indicates whether or not the source string begins with the search string.

returnvalue starts_with(source, search_string)

source ::= any*
search_string ::= any*
returnvalue ::= boolean

The ends_withfunction indicates whether or not the source string ends with the search string.

returnvalue ends_with(source, search_string)

source ::= any*
search_string ::= any*
returnvalue ::= boolean

Example: How long does it take from the time of check-in to the time the bag is scanned at the point of boarding for the passenger with ticket number 176234463813?

SELECT $flightLeg.flightNo,
$flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime AS checkinTime,
$flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime AS bagScanTime,
timestamp_diff(
   $flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime,
   $flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime
) AS diff
FROM baggageinfo $s, $s.bagInfo[].flightLegs[] AS $flightLeg
WHERE ticketNo=176234463813 
AND starts_with($s.bagInfo[].routing, $flightLeg.fltRouteSrc)

Explanation: In the baggage data, every flightLeg has an actions array. There are three different actions in the actions array. The action code for the first element in the array is Checkin/Offload. For the first leg, the action code is Checkin and for the other legs, the action code is Offload at the hop. The action code for the second element of the array is BagTag Scan. In the query above, you determine the difference in action time between the bag tag scan and check-in time. You use the contains function to filter the action time only if the action code is Checkin or BagScan. Since only the first flight leg has details of check-in and bag scan, you additionally filter the data using starts_with function to fetch only the source code fltRouteSrc.

Output:

{"flightNo":"BM572","checkinTime":"2019-03-02T03:28:00Z",
"bagScanTime":"2019-03-02T04:52:00Z","diff":-5040000}

Example 2 : Find list of passengers whose destination is JTR.

SELECT fullname FROM baggageInfo $bagInfo 
WHERE ends_with($bagInfo.bagInfo[].routing, "JTR")

Output:

{"fullname":"Lucinda Beckman"}
{"fullname":"Gerard Greene"}
{"fullname":"Michelle Payne"}