Using Path expressions
Path expressions are used to navigate inside hierarchically structured data. Oracle NoSQL Database supports different complex data types like arrays and records. You will learn how to work with different complex data types using path expressions.
If you want to follow along with the examples, download the script baggageschema_loaddata.sql and execute it as shown below. This script creates the table used in the example and loads data into the table.
java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore
load
command, run the
script.load -file baggageschema_loaddata.sql
Using Internal variables and aliases
Oracle NoSQL Database allows implicit declaration of internal variables. Internal variables are bound to their values during the execution of the expressions that declare them.
The table name in a query may be followed by a table alias. Table aliases are essentially variables ranging over the rows of the specified table. If no alias is specified, one is created internally, using the name of the table as it is spelled in the query.
SELECT bagDet.ticketNo, bagDet.fullName, bagDet.contactPhone FROM BaggageInfo bagDet
WHERE confNo="QB1O0J"
Explanation: In this query, you fetch the values of static fields like
fullname, ticket number, and contact phone for a particular reservation code. You use a
table alias for the BaggageInfo
table.
{"ticketNo":1762390789239,"fullName":"Zina
Christenson","contactPhone":"987-210-3029"}
If the table alias starts with a dollar sign ($), then it actually serves as a variable declaration for a variable whose name is the alias. This variable is bound to the context row.
SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE
exists bag.bagInfo[$element.bagArrivalDate >="2019-01-01T00:00:00"]
Explanation: The bag arrival date value for every bag should be greater than the year 2019. Here the "$element" is bound to the context row ( every baggage of the customer). The EXISTS operator checks whether the sequence returned by its input expression is empty or not. The sequence returned by the comparison operator ">=" is non-empty for all bags which arrived after 2019.
{"fullName":"Lucinda Beckman","ticketNo":1762320569757}
{"fullName":"Adelaide Willard","ticketNo":1762392135540}
{"fullName":"Raymond Griffin","ticketNo":1762399766476}
{"fullName":"Elane Lemons","ticketNo":1762324912391}
{"fullName":"Zina Christenson","ticketNo":1762390789239}
{"fullName":"Zulema Martindale","ticketNo":1762340579411}
{"fullName":"Dierdre Amador","ticketNo":1762376407826}
{"fullName":"Henry Jenkins","ticketNo":176234463813}
{"fullName":"Rosalia Triplett","ticketNo":1762311547917}
{"fullName":"Lorenzo Phil","ticketNo":1762320369957}
{"fullName":"Gerard Greene","ticketNo":1762341772625}
{"fullName":"Adam Phillips","ticketNo":1762344493810}
{"fullName":"Doris Martin","ticketNo":1762355527825}
{"fullName":"Joanne Diaz","ticketNo":1762383911861}
{"fullName":"Omar Harvey","ticketNo":1762348904343}
{"fullName":"Fallon Clements","ticketNo":1762350390409}
{"fullName":"Lisbeth Wampler","ticketNo":1762355854464}
{"fullName":"Teena Colley","ticketNo":1762357254392}
{"fullName":"Michelle Payne","ticketNo":1762330498104}
{"fullName":"Mary Watson","ticketNo":1762340683564}
{"fullName":"Kendal Biddle","ticketNo":1762377974281}
Working with Arrays
An array is an ordered collection of zero or more items. The items of an array are called elements. Arrays cannot contain any NULL values.
BaggageInfo
schema has many arrays. A simple array from
the schema is the actions
array in every flightLeg
.
You can use path expressions to navigate a simple array or a nested
array."actions" : [ {
"actionAt" : "SYD",
"actionCode" : "ONLOAD to SIN",
"actionTime" : "2019.02.28 at 22:09:00 AEDT"
}, {
"actionAt" : "SYD",
"actionCode" : "BagTag Scan at SYD",
"actionTime" : "2019.02.28 at 21:51:00 AEDT"
}, {
"actionAt" : "SYD",
"actionCode" : "Checkin at SYD",
"actionTime" : "2019.02.28 at 20:06:00 AEDT"
} ]
SELECT bagDet.fullName, bagDet.bagInfo[].flightLegs[0]
AS Details FROM BaggageInfo bagDet WHERE ticketNo=1762357254392
In the above query, flightLegs
is an array. The slice step
[0]
is applied to the flightLegs
array. Since
array elements start with 0, this gives you the first record in the array. You get the
first leg information of every bag for each passenger. You apply an additional filter
with the ticketNo
and so only one passenger information is fetched.
{"fullName":"Teena Colley",
"Details":[[
{"actionAt":"MSQ","actionCode":"ONLOAD to FRA","actionTime":"2019-02-13T07:17:00Z"},
{"actionAt":"MSQ","actionCode":"BagTag Scan at MSQ","actionTime":"2019-02-13T06:52:00Z"},
{"actionAt":"MSQ","actionCode":"Checkin at MSQ","actionTime":"2019-02-13T06:11:00Z"}],
"2019-02-13T09:00:00Z","2019-02-13T07:00:00Z","BM365","FRA","MSQ"]}
Note:
You can also use a slice step to select all array elements whose positions are within a range: [low: high], where low and high are expressions to specify the range boundaries. You can omit low and high expressions if you do not require a low or high boundary.Example: Fetch the details of all the legs (including all the actions taken at all the legs) for the passenger with ticket number 1762357254392.
flightLegs
array.SELECT bagDet.fullName, bagDet.bagInfo[].flightLegs[0:2] AS Details
FROM BaggageInfo bagDet WHERE ticketNo=1762357254392
{"fullName":"Teena Colley",
"Details":[
[
{"actionAt":"MSQ","actionCode":"ONLOAD to FRA","actionTime":"2019-02-13T07:17:00Z"},
{"actionAt":"MSQ","actionCode":"BagTag Scan at MSQ","actionTime":"2019-02-13T06:52:00Z"},
{"actionAt":"MSQ","actionCode":"Checkin at MSQ","actionTime":"2019-02-13T06:11:00Z"}
],
"2019-02-13T09:00:00Z","2019-02-13T07:00:00Z","BM365","FRA","MSQ",
[
{"actionAt":"HKG","actionCode":"Offload to Carousel at HKG","actionTime":"2019-02-13T11:15:00Z"},
{"actionAt":"FRA","actionCode":"ONLOAD to HKG","actionTime":"2019-02-13T10:39:00Z"},
{"actionAt":"FRA","actionCode":"OFFLOAD from FRA","actionTime":"2019-02-13T10:37:00Z"}
],
"2019-02-13T11:18:00Z","2019-02-13T07:17:00Z","BM313","HKG","FRA"
]}
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.
SELECT bagDet.fullName, bagDet.bagInfo[].flightLegs[0].values().values() AS Action
FROM BaggageInfo bagDet WHERE ticketNo=1762330498104
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.
{"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"]}
SELECT bagDet.fullName, (bagDet.bagInfo[].flightLegs[0].values())[2].actionCode
AS lastTransit_Update FROM BaggageInfo bagDet WHERE ticketNo=1762340683564
$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.
{"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.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.
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.
{"fullName":"Lucinda Beckman","Noof_Legs":3}
SELECT bagDet.fullName, size(bagDet.bagInfo[].flightLegs[0].actions) AS FirstLeg_NoofActions
FROM BaggageInfo bagDet WHERE ticketNo=1762357254392
{"fullName":"Teena Colley","FirstLeg_NoofActions":3}
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
{"fullName":"Mary Watson","lastTransit_Update":"Checkin at YYZ"}
Explanation:
-
$bagDet.bagInfo[].flightLegs[0].values()
gives all the entries in the first record of theflightLegs
array.size(bagDet.bagInfo.flightLegs[0].actions)
gives the size of the actions array in the first leg.- 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 thesize
function, you have rewritten the same
query in a generic way without hard coding the index.