starts_with and ends_with functions
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
ends_with
function 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
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
.
{"flightNo":"BM572","checkinTime":"2019-03-02T03:28:00Z",
"bagScanTime":"2019-03-02T04:52:00Z","diff":-5040000}
SELECT fullname FROM baggageInfo $bagInfo
WHERE ends_with($bagInfo.bagInfo[].routing, "JTR")
{"fullname":"Lucinda Beckman"}
{"fullname":"Gerard Greene"}
{"fullname":"Michelle Payne"}