Get data from nested tables

Question

I'm starting with object-oriented databases and I have a lot of questions about it :-(

Having the following structure:

CREATE OR REPLACE TYPE typeAuthor AS OBJECT(
    aId INTEGER,
    aName VARCHAR(60),
    aSurname VARCHAR(200),
);

CREATE TABLE tableAuthors OF typeAuthor (aId PRIMARY KEY)
NESTED TABLE aArticles STORE AS aArticles_nt;

CREATE OR REPLACE TYPE typeListAuthors AS TABLE OF REF typeAuthor;

CREATE OR REPLACE TYPE typeUniversity AS OBJECT(
    uniId INTEGER,
    uAlias VARCHAR(16),
    uName VARCHAR(20),
    uLocation VARCHAR(150),
    uAuthors typeListAuthors
);

CREATE TABLE tableUniversity OF typeUniversity (uniId PRIMARY KEY)
NESTED TABLE uAuthors STORE AS uAuthors_nt;

If I do a SELECT, for example:

SELECT u.uAuthors from tableUniversity u WHERE u.uniId = 1;

It returns all the data of the objects stored in the table that matches with the condition, but... How can I get only the names of the Authors?

I tried with

SELECT u.uAuthors.aName from tableUniversity u WHERE u.uniId = 1;

But it doesn't work.

Probably this is a basic question, but as I said, I'm starting with this and is being a little bit confusing to me.

Answer 1

You can unnest the inner collection with:

from tableUniversity u
cross join table (u.uAuthors) a

and dereference the value returned from that, which you can access as column_value:

SELECT deref(a.column_value)
from tableUniversity u
cross join table (u.uAuthors) a

and then you can access the aName field from each dereferenced object:

SELECT deref(a.column_value).aname
from tableUniversity u
cross join table (u.uAuthors) a
WHERE u.uniId = 1;

db<>fiddle