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.

Start your KVSTORE or KVLite and open the SQL.shell.

java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

Using the load command, run the script.

load -file baggageschema_loaddata.sql

• Using Internal variables and aliases
• Working with Arrays
• Working with nested data type
• Finding the size of a complex data type

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.

Example 1: Find the ticket number and passenger details for a given reservation code:

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.

Output:

{"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.

Example 2: Fetch the full name and tag number for all customer baggage shipped after 2019.

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.

Output:

{"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.

The 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"
} ]

Example 1: Fetch the details of the first leg of every bag (including all the actions taken at the leg) for the passenger with ticket number 1762357254392.

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.

Output:

{"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.

You'll be using the slice step to fetch the first 3 records of the flightLegs array.

SELECT bagDet.fullName, bagDet.bagInfo[].flightLegs[0:2] AS Details
FROM BaggageInfo bagDet WHERE ticketNo=1762357254392

Output:

{"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.

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.

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.