Using NESTED TABLES to join parent-child tables
A JOIN is used to combine rows from two or more tables, based on a related column
between them. In a hierarchical table, the child table inherits the primary
key columns of its parent table. This is done implicitly, without including
the parent columns in the CREATE TABLE
statement of the
child. All tables in the hierarchy have the same shard key columns.
You can use NESTED TABLES clause to join tables in Oracle NoSQL Database.
Overview of NESTED TABLES
Note:
Semantically, a NESTED TABLES clause is equivalent to a number of left-outer-join operations "centered" around the target table.- Queries multiple tables in the same hierarchy
- It is not an ANSI-SQL Standard
- It supports sibling tables join
Table 4-1 Nested Tables Vs LOJ
Nested Tables | LOJ |
---|---|
Queries multiple tables in the same hierarchy | Queries multiple tables in the same hierarchy |
Not an ANSI-SQL Standard | ANSI-SQL Standard |
Supports sibling tables join | Does not support sibling table joins |
If you want to follow along with the examples, download the script parentchildtbls_loaddata.sql and run 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, execute the
script.load -file parentchildtbls_loaddata.sql
Examples using NESTED TABLES
-
ticket
ticketNo LONG confNo STRING PRIMARY KEY(ticketNo)
-
ticket.bagInfo
id LONG tagNum LONG routing STRING lastActionCode STRING lastActionDesc STRING lastSeenStation STRING, lastSeenTimeGmt TIMESTAMP(4) bagArrivalDate TIMESTAMP(4) PRIMARY KEY(id)
-
ticket.bagInfo.flightLegs
flightNo STRING flightDate TIMESTAMP(4) fltRouteSrc STRING fltRouteDest STRING estimatedArrival TIMESTAMP(4), actions JSON PRIMARY KEY(flightNo)
-
ticket.passengerInfo
contactPhone STRING fullName STRING gender STRING PRIMARY KEY(contactPhone)
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
.
{"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"}
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
tableticket
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.
{"fullname":"Elane Lemons","contactPhone":"600-918-8404","gender":"F"}
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
.
{"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"}}
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
tableticket
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.
{"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))
{"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}
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.
{"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"}]}}
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
tableticket
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.
"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"}]}}
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.
{"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}
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.
{"id":79039899197492,"NUMBER_HOPS":3}
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
tableticket
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.
{"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"}