Previous Next JavaScript must be enabled to correctly display this content

## SQL Examples

Example 1: Fetch the details of all passengers who have been issued a ticket.

    SELECT fullname, contactPhone, gender
    FROM NESTED TABLES
    (ticket a descendants(ticket.passengerInfo b))

Explanation: This is an example of a join where the target table `ticket` is joined with its child table `passengerInfo`.

Output:

    {"fullname":"Elane Lemons","contactPhone":"600-918-8404","gender":"F"}
    {"fullname":"Adelaide Willard","contactPhone":"421-272-8082","gender":"M"}
    {"fullname":"Dierdre Amador","contactPhone":"165-742-5715","gender":"M"}
    {"fullname":"Doris Martin","contactPhone":"289-564-3497","gender":"F"}
    {"fullname":"Adam Phillips","contactPhone":"893-324-1064","gender":"M"}

Example 1a: Fetch the details of the passenger with ticket number 1762324912391 .

    SELECT fullname, contactPhone, gender
    FROM NESTED TABLES
    (ticket a descendants(ticket.passengerInfo b))
    WHERE a.ticketNo=1762324912391

Explanation: This is an example of a join where the target table`ticket` is joined with its child table `passengerInfo`. Additionally, you can limit the result set by applying a filter condition to the result of the join. You are limiting the result to a particular ticket number.

Output:

    {"fullname":"Elane Lemons","contactPhone":"600-918-8404","gender":"F"}

Example 2: Fetch all the bag details for all passengers who have been issued a ticket.

    SELECT * FROM NESTED TABLES
    (ticket a descendants(ticket.bagInfo b))

Explanation: This is an example of a join where the target table `ticket` is joined with its child table `bagInfo`.

Output:

    {"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},
    "b":{"ticketNo":1762344493810,"id":79039899165297,"tagNum":17657806255240,"routing":"MIA/LAX/MEL",
    "lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MEL",
    "lastSeenTimeGmt":"2019-02-01T16:13:00.0000Z","bagArrivalDate":"2019-02-01T16:13:00.0000Z"}}

    {"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},
    "b":{"ticketNo":1762324912391,"id":79039899168383,"tagNum":1765780623244,"routing":"MXP/CDG/SLC/BZN",
    "lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"BZN",
    "lastSeenTimeGmt":"2019-03-15T10:13:00.0000Z","bagArrivalDate":"2019-03-15T10:13:00.0000Z"}}

    {"a":{"ticketNo":1762392135540,"confNo":"DN3I4Q"},
    "b":{"ticketNo":1762392135540,"id":79039899156435,"tagNum":17657806224224,"routing":"GRU/ORD/SEA",
    "lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"SEA",
    "lastSeenTimeGmt":"2019-02-15T21:21:00.0000Z","bagArrivalDate":"2019-02-15T21:21:00.0000Z"}}

    {"a":{"ticketNo":1762376407826,"confNo":"ZG8Z5N"},
    "b":{"ticketNo":1762376407826,"id":7903989918469,"tagNum":17657806240229,"routing":"JFK/MAD",
    "lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MAD",
    "lastSeenTimeGmt":"2019-03-07T13:51:00.0000Z","bagArrivalDate":"2019-03-07T13:51:00.0000Z"}}

    {"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},
    "b":{"ticketNo":1762355527825,"id":79039899197492,"tagNum":17657806232501,"routing":"BZN/SEA/CDG/MXP",
    "lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MXP",
    "lastSeenTimeGmt":"2019-03-22T10:17:00.0000Z","bagArrivalDate":"2019-03-22T10:17:00.0000Z"}}

Example 2a: Fetch all the bag details for a particular ticket number.

    SELECT * FROM
    NESTED TABLES (ticket a descendants(ticket.bagInfo b))
    WHERE a.ticketNo=1762324912391

Explanation: This is an example of a join where the target table`ticket` is joined with its child table `bagInfo`. Additionally, you can limit the result set by applying a filter condition to the result of the join. You are limiting the result to a particular ticket number.

Output:

    {"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},
    "b":{"ticketNo":1762324912391,"id":79039899168383,"tagNum":1765780623244,"routing":"MXP/CDG/SLC/BZN",
    "lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"BZN",
    "lastSeenTimeGmt":"2019-03-15T10:13:00.0000Z","bagArrivalDate":"2019-03-15T10:13:00.0000Z"}}

Note:

If you move the non-join predicate restriction to the ON clause, the result set includes all the rows that meet the ON clause condition. Rows from the right outer table that do not meet the ON condition are populated with NULL values as shown below.

    SELECT * FROM
    NESTED TABLES(ticket a descendants(ticket.bagInfo b
    ON a.ticketNo=b.ticketNo
    AND a.ticketNo=1762324912391))

Output:

    {"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},"b":null}
    {"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},"b":null}
    {"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"}, "b":{"ticketNo":1762324912391,"id":79039899168383,"tagNum":1765780623244,"routing":"MXP/CDG/SLC/BZN",
    "lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"BZN",
    "lastSeenTimeGmt":"2019-03-15T10:13:00.0000Z","bagArrivalDate":"2019-03-15T10:13:00.0000Z"}}
    {"a":{"ticketNo":1762392135540,"confNo":"DN3I4Q"},"b":null}
    {"a":{"ticketNo":1762376407826,"confNo":"ZG8Z5N"},"b":null}

Example 3: Fetch all flight leg details for all passengers.

    SELECT * FROM
    NESTED TABLES (ticket a descendants(ticket.bagInfo.flightLegs b))

Explanation: This is an example of a join where the target table `ticket` is joined with its descendant `bagInfo`. A descendant table can be any level hierarchically below a table ( For example `fightLegs` is the child of `bagInfo` which is the child of `ticket`, so `flightLegs` is a descendant of `ticket`). All the rows from the `ticket` table will be fetched. If any row from the `ticket` table does not have a matching row in the `flightLegs` table, then NULL values will be displayed for those rows of the `flightLegs` table.

Output:

    {"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},
    "b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM604","flightDate":"2019-02-01T06:00:00.0000Z",
    "fltRouteSrc":"MIA","fltRouteDest":"LAX","estimatedArrival":"2019-02-01T11:00:00.0000Z",
    "actions":[{"actionAt":"MIA","actionCode":"ONLOAD to LAX","actionTime":"2019-02-01T06:13:00Z"},
    {"actionAt":"MIA","actionCode":"BagTag Scan at MIA","actionTime":"2019-02-01T05:47:00Z"},
    {"actionAt":"MIA","actionCode":"Checkin at MIA","actionTime":"2019-02-01T04:38:00Z"}]}}

    {"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},
    "b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM667","flightDate":"2019-02-01T06:13:00.0000Z",
    "fltRouteSrc":"LAX","fltRouteDest":"MEL","estimatedArrival":"2019-02-01T16:15:00.0000Z",
    "actions":[{"actionAt":"MEL","actionCode":"Offload to Carousel at MEL","actionTime":"2019-02-01T16:15:00Z"},
    {"actionAt":"LAX","actionCode":"ONLOAD to MEL","actionTime":"2019-02-01T15:35:00Z"},
    {"actionAt":"LAX","actionCode":"OFFLOAD from LAX","actionTime":"2019-02-01T15:18:00Z"}]}}

    {"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},
    "b":{"ticketNo":1762324912391,"id":79039899168383,"flightNo":"BM170","flightDate":"2019-03-15T08:13:00.0000Z",
    "fltRouteSrc":"SLC","fltRouteDest":"BZN","estimatedArrival":"2019-03-15T10:14:00.0000Z",
    "actions":[{"actionAt":"BZN","actionCode":"Offload to Carousel at BZN","actionTime":"2019-03-15T10:13:00Z"},
    {"actionAt":"SLC","actionCode":"ONLOAD to BZN","actionTime":"2019-03-15T10:06:00Z"},
    {"actionAt":"SLC","actionCode":"OFFLOAD from SLC","actionTime":"2019-03-15T09:59:00Z"}]}}

    {"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},
    "b":{"ticketNo":1762324912391,"id":79039899168383,"flightNo":"BM490","flightDate":"2019-03-15T08:13:00.0000Z",
    "fltRouteSrc":"CDG","fltRouteDest":"SLC","estimatedArrival":"2019-03-15T10:14:00.0000Z",
    "actions":[{"actionAt":"CDG","actionCode":"ONLOAD to SLC","actionTime":"2019-03-15T09:42:00Z"},
    {"actionAt":"CDG","actionCode":"BagTag Scan at CDG","actionTime":"2019-03-15T09:17:00Z"},
    {"actionAt":"CDG","actionCode":"OFFLOAD from CDG","actionTime":"2019-03-15T09:19:00Z"}]}}

    {"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},
    "b":{"ticketNo":1762324912391,"id":79039899168383,"flightNo":"BM936","flightDate":"2019-03-15T08:00:00.0000Z",
    "fltRouteSrc":"MXP","fltRouteDest":"CDG","estimatedArrival":"2019-03-15T09:00:00.0000Z",
    "actions":[{"actionAt":"MXP","actionCode":"ONLOAD to CDG","actionTime":"2019-03-15T08:13:00Z"},
    {"actionAt":"MXP","actionCode":"BagTag Scan at MXP","actionTime":"2019-03-15T07:48:00Z"},
    {"actionAt":"MXP","actionCode":"Checkin at MXP","actionTime":"2019-03-15T07:38:00Z"}]}}

    {"a":{"ticketNo":1762392135540,"confNo":"DN3I4Q"},
    "b":{"ticketNo":1762392135540,"id":79039899156435,"flightNo":"BM79","flightDate":"2019-02-15T01:00:00.0000Z",
    "fltRouteSrc":"GRU","fltRouteDest":"ORD","estimatedArrival":"2019-02-15T11:00:00.0000Z",
    "actions":[{"actionAt":"GRU","actionCode":"ONLOAD to ORD","actionTime":"2019-02-15T01:21:00Z"},
    {"actionAt":"GRU","actionCode":"BagTag Scan at GRU","actionTime":"2019-02-15T00:55:00Z"},
    {"actionAt":"GRU","actionCode":"Checkin at GRU","actionTime":"2019-02-14T23:49:00Z"}]}}

    {"a":{"ticketNo":1762392135540,"confNo":"DN3I4Q"}
    ,"b":{"ticketNo":1762392135540,"id":79039899156435,"flightNo":"BM907","flightDate":"2019-02-15T01:21:00.0000Z",
    "fltRouteSrc":"ORD","fltRouteDest":"SEA","estimatedArrival":"2019-02-15T21:22:00.0000Z",
    "actions":[{"actionAt":"SEA","actionCode":"Offload to Carousel at SEA","actionTime":"2019-02-15T21:16:00Z"},
    {"actionAt":"ORD","actionCode":"ONLOAD to SEA","actionTime":"2019-02-15T20:52:00Z"},
    {"actionAt":"ORD","actionCode":"OFFLOAD from ORD","actionTime":"2019-02-15T20:44:00Z"}]}}

    {"a":{"ticketNo":1762376407826,"confNo":"ZG8Z5N"},
    "b":{"ticketNo":1762376407826,"id":7903989918469,"flightNo":"BM495","flightDate":"2019-03-07T07:00:00.0000Z",
    "fltRouteSrc":"JFK","fltRouteDest":"MAD","estimatedArrival":"2019-03-07T14:00:00.0000Z",
    "actions":[{"actionAt":"MAD","actionCode":"Offload to Carousel at MAD","actionTime":"2019-03-07T13:54:00Z"},
    {"actionAt":"JFK","actionCode":"ONLOAD to MAD","actionTime":"2019-03-07T07:00:00Z"},
    {"actionAt":"JFK","actionCode":"BagTag Scan at JFK","actionTime":"2019-03-07T06:53:00Z"},
    {"actionAt":"JFK","actionCode":"Checkin at JFK","actionTime":"2019-03-07T05:03:00Z"}]}}

    {"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},
    "b":{"ticketNo":1762355527825,"id":79039899197492,"flightNo":"BM386","flightDate":"2019-03-22T07:23:00.0000Z",
    "fltRouteSrc":"CDG","fltRouteDest":"MXP","estimatedArrival":"2019-03-22T10:24:00.0000Z",
    "actions":[{"actionAt":"MXP","actionCode":"Offload to Carousel at MXP","actionTime":"2019-03-22T10:15:00Z"},
    {"actionAt":"CDG","actionCode":"ONLOAD to MXP","actionTime":"2019-03-22T10:09:00Z"},
    {"actionAt":"CDG","actionCode":"OFFLOAD from CDG","actionTime":"2019-03-22T10:01:00Z"}]}}

    {"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},
    "b":{"ticketNo":1762355527825,"id":79039899197492,"flightNo":"BM578","flightDate":"2019-03-22T07:23:00.0000Z",
    "fltRouteSrc":"SEA","fltRouteDest":"CDG","estimatedArrival":"2019-03-21T23:24:00.0000Z",
    "actions":[{"actionAt":"SEA","actionCode":"ONLOAD to CDG","actionTime":"2019-03-22T11:26:00Z"},
    {"actionAt":"SEA","actionCode":"BagTag Scan at SEA","actionTime":"2019-03-22T10:57:00Z"},
    {"actionAt":"SEA","actionCode":"OFFLOAD from SEA","actionTime":"2019-03-22T11:07:00Z"}]}}

    {"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},
    "b":{"ticketNo":1762355527825,"id":79039899197492,"flightNo":"BM704","flightDate":"2019-03-22T07:00:00.0000Z",
    "fltRouteSrc":"BZN","fltRouteDest":"SEA","estimatedArrival":"2019-03-22T09:00:00.0000Z",
    "actions":[{"actionAt":"BZN","actionCode":"ONLOAD to SEA","actionTime":"2019-03-22T07:23:00Z"},
    {"actionAt":"BZN","actionCode":"BagTag Scan at BZN","actionTime":"2019-03-22T06:58:00Z"},
    {"actionAt":"BZN","actionCode":"Checkin at BZN","actionTime":"2019-03-22T05:20:00Z"}]}}

Example 3a: Fetch all the flight leg details for a particular ticket number.

    SELECT * FROM
    NESTED TABLES (ticket.bagInfo.flightLegs b ancestors(ticket a))
    WHERE a.ticketNo=1762344493810

Explanation: This is an example of a join where the target table`ticket` is joined with its descendant `bagInfo`. Additionally, you can limit the result set by applying a filter condition to the result of the join. You are limiting the result to a particular ticket number.

The result has two rows, implying there are two flight legs for this ticket number.

Output:

    "a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},
    "b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM604",
    "flightDate":"2019-02-01T06:00:00.0000Z","fltRouteSrc":"MIA","fltRouteDest":"LAX",
    "estimatedArrival":"2019-02-01T11:00:00.0000Z",
    "actions":[{"actionAt":"MIA","actionCode":"ONLOAD to LAX","actionTime":"2019-02-01T06:13:00Z"},
    {"actionAt":"MIA","actionCode":"BagTag Scan at MIA","actionTime":"2019-02-01T05:47:00Z"},
    {"actionAt":"MIA","actionCode":"Checkin at MIA","actionTime":"2019-02-01T04:38:00Z"}]}}

    {"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},
    "b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM667",
    "flightDate":"2019-02-01T06:13:00.0000Z","fltRouteSrc":"LAX","fltRouteDest":"MEL",
    "estimatedArrival":"2019-02-01T16:15:00.0000Z",
    "actions":[{"actionAt":"MEL","actionCode":"Offload to Carousel at MEL","actionTime":"2019-02-01T16:15:00Z"},
    {"actionAt":"LAX","actionCode":"ONLOAD to MEL","actionTime":"2019-02-01T15:35:00Z"},
    {"actionAt":"LAX","actionCode":"OFFLOAD from LAX","actionTime":"2019-02-01T15:18:00Z"}]}}

Example 4: Fetch the bag id and number of hops for all bags of all passengers.

    SELECT b.id,count(*) AS NUMBER_HOPS
    FROM NESTED TABLES (ticket a descendants(ticket.bagInfo.flightLegs b))
    GROUP BY b.id

Explanation: You group the data based on the bag id (using GROUP BY) and get the count of flight legs ( using count() ) for every bag.

Output:

    {"id":79039899168383,"NUMBER_HOPS":3}
    {"id":79039899156435,"NUMBER_HOPS":2}
    {"id":7903989918469,"NUMBER_HOPS":1}
    {"id":79039899165297,"NUMBER_HOPS":2}
    {"id":79039899197492,"NUMBER_HOPS":3}

Example 4a: Find the number of hops for all bags of a particular passenger.

    SELECT b.id,count(*) AS NUMBER_HOPS FROM
    NESTED TABLES (ticket a descendants(ticket.bagInfo.flightLegs b))
    WHERE a.ticketNo=1762355527825
    GROUP BY b.id

Explanation: You group the data based on the bag id (using GROUP BY) and get the count of flight legs ( Using count()) for every bag. Additionally, you filter the results for a particular ticket number.

Output:

    {"id":79039899197492,"NUMBER_HOPS":3}

Example 5: Fetch bag id and routing details of all bags that arrived after 2019.

    SELECT b.id, routing FROM
    NESTED TABLES(ticket a descendants(ticket.bagInfo b))
    WHERE CAST (b.bagArrivalDate AS Timestamp(0))>=
    CAST ("2019-01-01T00:00:00" AS Timestamp(0))

Explanation: This is an example of a join where the target table`ticket` is joined with its child table `bagInfo`. The filter condition is applied on the `bagArrivalDate`. The `CAST` function is used to convert the string into Timestamp and then the values are compared.

Output:

    {"id":79039899197492,"routing":"BZN/SEA/CDG/MXP"}
    {"id":79039899165297,"routing":"MIA/LAX/MEL"}
    {"id":79039899168383,"routing":"MXP/CDG/SLC/BZN"}
    {"id":79039899156435,"routing":"GRU/ORD/SEA"}
    {"id":7903989918469,"routing":"JFK/MAD"}