Example: Using NESTED TABLES clause to query multiple tables in the same hierarchy

Let’s consider an application that tracks a population of users and the emails sent or received by these users. Given that SQL for Oracle NoSQL Database does not currently support general purpose joins, the emails are stored in a table that is created as a child of users, so that queries can be written that combine information from both tables using the NESTED TABLES clause. The create table statements for the two tables are shown below. Notice that it is possible for the application to receive emails that are not associated with any user in the users table; such emails will be assigned a uid value that does not exist in the users table.

CREATE TABLE users(
uid INTEGER,
name string,
email_address string,
salary INTEGER,
address json,
PRIMARY KEY(uid));

CREATE TABLE users.emails(
eid INTEGER,
sender_address string, // sender email address
receiver_address string, // receiver email address
time timestamp(3),
size INTEGER,
content string,
PRIMARY KEY(eid));

Here are two queries that can be written over the users and emails tables.

Example 6-58 Joining Tables

Count the number of emails sent in 2017 by all users whose salary is greater than 200K

SELECT count(eid)
FROM NESTED TABLES(
users
descendants(users.emails ON email_address = sender_address and
year(time) = 2017)
)
WHERE salary > 200;

In the above query, we are using count(eid) rather than count(*) because there may exist users with no emails or no sent emails. For such a user, the FROM clause will return a record where the eid field will be NULL, and count(eid) will not count such a record.

Example 6-59 Joining Tables

For each email whose size is greater than 100KB and was sent by a user in the the users table, return the name and address of that user.

SELECT name, address
FROM NESTED TABLES(users.emails ancestors(users))
WHERE size > 100 AND sender_address = email_address;

In the above query will return duplicate results for any user who has sent more than one "large" email. Currently, SQL for Oracle NoSQL Database does not support SELECT DINSTINCT, so the duplicate elimination has to be performed by the application.