Oracle Rest API multiple rows

Question

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?

thatjeffsmith · Accepted Answer · 2022-03-31 15:45:10Z

2

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.

answered Mar 31, 2022 at 15:45

thatjeffsmith

22.7k6 gold badges44 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shams Over a year ago

I am getting an error, ORA-00932: inconsistent datatypes: expected CHAR got CURSOR, but it worked after calling from the POSTMAN thanks

Jens k Over a year ago

If i use this example the Open API 3.0 Documentation is wrong. I Can not see the properties from the nested Json. Can I fix this?

thatjeffsmith Over a year ago

@Jensk some of that may be opaque to ORDS...but this would be a separate question

EJ Egyed · Accepted Answer · 2022-03-31 15:35:26Z

0

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;

answered Mar 31, 2022 at 15:35

EJ Egyed

6,1341 gold badge11 silver badges25 bronze badges

1 Comment

dendiie Over a year ago

Thank you! It is so simple and so perfect!!!

Collectives™ on Stack Overflow

Oracle Rest API multiple rows

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related