Working with nested data type

Oracle NoSQL database supports nested data type. That means you can have one data type inside another data type. For example, records inside an array, an array inside an array, and so on. The sample Baggageinfo schema uses nested data type of an array of arrays.

Example 1: Fetch the various actions taken on the first leg for the passenger with the ticket number 1762330498104.

SELECT bagDet.fullName, bagDet.bagInfo[].flightLegs[0].values().values() AS Action
FROM BaggageInfo bagDet WHERE ticketNo=1762330498104

Explanation: In the above query, flightLegs is a nested data type. This in turn has an actions array, which is an array of records. The above query is executed in two steps.

1. $bag.bagInfo[].flightLegs[0].values() gives all the entries in the first record of theflightLegs array. This includes an actions array. You can iterate this ( using values() ) to get all the records of the actions array as shown below.
2. $bag.bagInfo[].flightLegs[0].values().values() gives all the records of the actions array.

Output:

{"fullName":"Michelle Payne",
"Action":["SFO","ONLOAD to IST","2019-02-02T12:10:00Z","SFO",
"BagTag Scan at SFO","2019-02-02T11:47:00Z","SFO",
"Checkin at SFO","2019-02-02T10:01:00Z"]}

Example 2: 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())[2].actionCode
AS lastTransit_Update FROM BaggageInfo bagDet WHERE ticketNo=1762340683564

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. bagInfo[].flightLegs[0].values())[2] points to the third ( which is the last) record of the actions array inside the first element of the flightLegs array.
3. There are multiple records in the actions array. bagInfo[].flightLegs[0].values())[2].actionCode fetches the value corresponding to the actionCode element.

Output:

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

Note:

In a later section you will learn to write the same query in a generic way without hardcoding the array index by using the size function. See Finding the size of a complex data type.