Previous Next JavaScript must be enabled to correctly display this content

## 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.