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.
$bag.bagInfo[].flightLegs[0].values()
gives all the entries in the first record of theflightLegs
array. This includes anactions
array. You can iterate this ( usingvalues()
) to get all the records of theactions
array as shown below.$bag.bagInfo[].flightLegs[0].values().values()
gives all the records of theactions
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:
$bagDet.bagInfo[].flightLegs[0].values()
gives all the entries in the first record of theflightLegs
array.bagInfo[].flightLegs[0].values())[2]
points to the third ( which is the last) record of theactions
array inside the first element of theflightLegs
array.- There are multiple records in the actions array.
bagInfo[].flightLegs[0].values())[2].actionCode
fetches the value corresponding to theactionCode
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.