Oracle Rest API multiple rows-CodePudding

I have to create a GET type of API and I have to connect two tables.

For example, I have the following tables:

Table 1:

customer_id: {001}
first_name: {f_name}
last_name: {l:name}

Table 2:

customer_id: {001} {001}
street: {A street} {B street}
zip_code: {1234} {1234}
city: {xxxx} {xxxx}
country: {xx} {xx}

If I connect the two tables I get the following result:

{
        "customer_id": 001,
        "first_name": "f_name",
        "last_name": "l_name",
        "street": "A street",
        "zip_code": "1234",
        "city": "xxxx",
        "country": xx       
    }
{
        "customer_id": 001,
        "first_name": "f_name",
        "last_name": "l_name",
        "street": "B street",
        "zip_code": "1234",
        "city": "xxxx",
        "country": xx       
    }

This is beacuse the table2 has two rows with the customer_id:"001".

But I want this kind of result:

{
    "customer_id": 001,
    "first_name": "f_name",
    "last_name": "l_name",
    "address": [
        {
            "street": "A steet",
            "zip_code": "1234",
            "city": "xxxx",
            "country": xx
        },
        {
            "street": "B street",
            "zip_code": "1234",
            "city": "xxxx",
            "country": xx
        }
    ]
}

It seems like a simple query won't work here. Has anybody ideal how should I create this GET type of API?

CodePudding user response：

This can be created using a simple SELECT statement for your GET API. You need to use the CURSOR command to create nested arrays within your JSON object. An example of what you need can be seen below.

:p_customer_id in the example below is some sort of input parameter from your API.

SELECT t1.customer_id,
       t1.first_name,
       t1.last_name,
       CURSOR (SELECT t2.street,
                      t2.zip_code,
                      t2.city,
                      t2.country
                 FROM table2 t2
                WHERE t2.customer_id = t1.customer_id)    AS address
  FROM table1 t1
 WHERE t1.customer_id = :p_customer_id;

CodePudding user response：

We're going to use a CURSOR in the query.

First the tables and data:

CREATE TABLE CUST (
    CUSTOMER_ID INTEGER,
    FIRST_NAME  VARCHAR2(20),
    LAST_NAME   VARCHAR2(20)
);

ALTER TABLE CUST 
    ADD CONSTRAINT CUST_PK PRIMARY KEY ( 
    CUSTOMER_ID  
    );

CREATE TABLE ADDY (
    CUSTOMER_ID INTEGER,
    STREET      VARCHAR2(20),
    ZIPCODE     VARCHAR2(5),
    CITY        VARCHAR2(20),
    COUNTRY     VARCHAR2(20)
);

ALTER TABLE ADDY 
    ADD CONSTRAINT CUST_ID FOREIGN KEY 
    ( 
     CUSTOMER_ID
    ) 
    REFERENCES CUST 
    ( 
    CUSTOMER_ID 
    ) 
    ON DELETE CASCADE 
    NOT DEFERRABLE NOVALIDATE 
;

insert into CUST values (1, 'Jeff', 'Smith');
insert into addy values (1, 'Chowning Place', '00001', 'Blacksburg', 'USA');
insert into addy values (1, 'Inkberry Ct', '00002', 'Apex', 'USA');

Then we can build the REST API.

Our template is cust_address/:id, and we're going to do a GET on that.

The SQL behind the GET handler:

select customer_id,
       first_name,
       last_name,
       CURSOR(
                select street,
                       zipcode,
                       city,
                       country
                from ADDY O
                where C.customer_id = O.customer_ID
       ) address
from CUST C
where customer_id = :id

Putting that all together, our TEMPLATE/HANDLER combo is defined like this in ORDS (as shown in SQL Developer Web)

Then we call the API - just in our browser:

You'll need to either turn paging off for a Collection Query (set to 0), or you'll need to define your handler as a Collection Item.

Disclaimer: I'm an Oracle employee and the product manager for Oracle REST Data Services.