Nested stucture in ORDS WS response

We're writing WS with ORDS (Oracle Rest Data Services) and we are able to have simple (flat) json responses. ORDS automatically convert my SYS_REFCURSON in json, for example:

{
  "title": "To Kill a Mockingbird",
  "author": "Harper Lee",
  "yearPublished": 1960,
  "genre": "Fiction",
  "pages": 281
}

Now we want to have some structure, for instance:

{
    "title": "To Kill a Mockingbird",
    "author": {
        "firstName": "Harper",
        "lastName": "Lee"
    },
    "yearFirstPublished": 1960,
    "genre": "Fiction",
    "editions": [
        {
            "yearPublished": 1960,
            "publisher": "J.B. Lippincott & Co.",
            "isbn": "978-0-06-112008-4",
            "pages": 281,
            "language": "English"
        },
        {
            "yearPublished": 2004,
            "publisher": "Time Warner",
            "isbn": "978-0446310789",
            "pages": 374,
            "language": "English"
        },
        {
            "yearPublished": 2015,
            "publisher": " Random House UK Ltd",
            "isbn": "978-1784870799",
            "pages": 485,
            "language": "English"
        }
    ]
}

Here is my database structure

CREATE TABLE BOOK
(
  BOOK_ID     NUMBER(9)  NOT NULL,
  BOOK_NAME   VARCHAR2(500 CHAR) NOT NULL,
  AUTHOR_ID   NUMBER(9) NOT NULL,
  GENRE       VARCHAR2(100) NOT NULL,
  FIRST_PUBLISHED_YEAR NUMBER(4),
  PAGES             NUMBER(4)
);

CREATE TABLE AUTHOR
(
  AUTHOR_ID   NUMBER(9)  NOT NULL,
  FIRST_NAME  VARCHAR2(500 CHAR) NOT NULL,
  LAST_NAME   VARCHAR2(500 CHAR) NOT NULL
);

CREATE TABLE EDITIONS
(
  BOOK_ID   NUMBER(9)  NOT NULL,
  YEAR_PUBLISHED NUMBER(4),
  ISBN   VARCHAR2(13 CHAR) NOT NULL,
  PUBLISHER   VARCHAR2(500 CHAR) NOT NULL,
  PAGES   VARCHAR2(500 CHAR) NOT NULL,
  LANGUAGE   VARCHAR2(50 CHAR) NOT NULL
);

The first json comes from a WS defined as a simple select:

BEGIN
  ORDS.define_service(
    p_module_name    => 'test',
    p_base_path      => 'test/',
    p_pattern        => 'books/getBookById/:book_id',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'select BOOK_NAME as title, 
    (select FIRST_NAME||'' ''||LAST_NAME from author a where a.author_id = t.author_id) as author,
    FIRST_PUBLISHED_YEAR as yearPublished,
    GENRE as genre,
    PAGES as pages
from BOOK
where BOOK_ID = :book_id;',
    p_items_per_page => 0);

  COMMIT;
END;
/

Is it possibile to get the second json output (I suppose a function/procedure is needed)?

I tried to define the service using a stored procedure returning cursor, but it cannot have the "structure". May be with a procedure that firstly reads the BOOK table and then the EDITIONS one, but how to "merge" the results to achieve the second json?

Thanks, Vjncenzo

Upvotes: 0

Answers (3)

p3consulting

Reputation: 4640

And for 23ai:

create or replace json collection view book_jcv as
select json {
    '_id' : b.book_id,
    'title' : b.book_name,
    'author' : [
        select json {
            'firstName' : a.first_name,
            'lastName' : a.last_name
        } from author a where a.author_id = b.author_id
    ],
    'yearFirstPublished' : b.first_published_year,
    'genre' : b.genre,
     'editions' : [ 
        select json {
            'yearPublished' : e.year_published,
            'publisher' : e.publisher,
            'isbn' : e.isbn,
            'pages' : e.pages,
            'language' : e.language
            }
         from   editions e where e.book_id = b.book_id
        ]
} as data
from book b;

Your ORDS handling becoming:

select json_serialize(d.data pretty) as "{}book"
from   book_jcv d
where d.data."_id" = :book_id
;

Upvotes: 1

p3consulting

Reputation: 4640

With GROUP BY or with LATERAL:

select json_object(
    'title' : b.book_name,
    'author' : json_object(
        'firstName' : a.first_name,
        'lastName' : a.last_name
    ),
    'yearFirstPublished' : b.first_published_year,
    'genre' : b.genre,
    'editions' : json_arrayagg(
        json_object(
            'yearPublished' : e.year_published,
            'publisher' : e.publisher,
            'isbn' : e.isbn,
            'pages' : e.pages,
            'language' : e.language
        )
    )
     RETURNING CLOB
) as "{}book"
from book b
join author a using (author_id)
join editions e using (book_id)
where book_id = :book_id
group by book_id, b.book_name, a.first_name, a.last_name, 
    b.first_published_year, b.genre
;

select json_object(
    'title' : b.book_name,
    'author' : a.author,
    'yearFirstPublished' : b.first_published_year,
    'genre' : b.genre,
    'editions' : e.editions
     RETURNING CLOB
) as "{}book"
from book b,
lateral( 
    select json_object(
        'author' : json_object(
            'firstName' : a.first_name,
            'lastName' : a.last_name
        ) 
    ) author
    from author a where a.author_id = b.author_id
) a,
lateral (
    select json_arrayagg(
        json_object(
            'yearPublished' : e.year_published,
            'publisher' : e.publisher,
            'isbn' : e.isbn,
            'pages' : e.pages,
            'language' : e.language
        )
         RETURNING CLOB
    ) editions 
    from editions e where e.book_id = b.book_id
) e
where b.book_id = :book_id
;

Upvotes: 1

p3consulting

Reputation: 4640

Not by returning a SYS_REFCURSOR and letting ORDS generates the JSON, it will always be a list of simple objects, if you want a hierarchical structure you need to generate the JSON yourself (JSON_OBJECT, JSON_ARRAY, JSON_ARRAYAGG, ...), and remember to name the column "{}someName" to avoid ORDS to escape it.

Upvotes: 0

Nested stucture in ORDS WS response

Answers (3)

Related Questions