Finding the size of a complex data type

The size function can be used to return the size (number of fields/entries) of a complex data type.

Example 1: Find out how many flight legs/hops are there for a passenger with ticket number 1762320569757.

SELECT bagDet.fullName, size(bagDet.bagInfo.flightLegs) as Noof_Legs
FROM BaggageInfo bagDet WHERE ticketNo=1762320569757

Explanation: In the above query, you get the size of the flightLegs array using the size function.

Output:

{"fullName":"Lucinda Beckman","Noof_Legs":3}

Example 2: Find the number of action entries (for the bags) in the first leg for the passenger with ticket number 1762357254392.

SELECT bagDet.fullName, size(bagDet.bagInfo[].flightLegs[0].actions) AS FirstLeg_NoofActions
FROM BaggageInfo bagDet WHERE ticketNo=1762357254392

Output:

{"fullName":"Teena Colley","FirstLeg_NoofActions":3}

Example 3: Display details of the last transit action update done on the first leg for the passenger with the ticket number 1762340683564.

SELECT bagDet.fullName,
(bagDet.bagInfo[].flightLegs[0].values())[size(bagDet.bagInfo.flightLegs[0].actions)-1].actionCode
AS lastTransit_Update FROM BaggageInfo bagDet WHERE ticketNo=1762340683564

Output:

{"fullName":"Mary Watson","lastTransit_Update":"Checkin at YYZ"}

Explanation:

The above query is processed using the following steps:

• 1. $bagDet.bagInfo[].flightLegs[0].values() gives all the entries in the first record of the flightLegs array.
  2. size(bagDet.bagInfo.flightLegs[0].actions)gives the size of the actions array in the first leg.
  3. There are multiple records in the actions array. You can use the result of the size function to get the last record in the action array and the corresponding actionCode can be fetched. You subtract the size by 1 as the index of an array starts with 0.

Note:

The same query has been written in the topic Working with nested data type by hard coding the index of the actions array. Using the size function, you have rewritten the same query in a generic way without hard coding the index.