How to Combine Responses from Two APIs into a Single Payload in Oracle Integration Cloud (OIC)?

Question

I am working with Oracle Integration Cloud (OIC) and have encountered a challenge. I need to combine the responses from two different APIs into a single payload.

Here are the sample responses from the two APIs:

API 1 Response (Customers):

json

{ "Customers": [ { "CustomerID": "c8239", "CustomerName": "Robert", "CustomerEmail": "robert@gmail.com" }, { "CustomerID": "c3242", "CustomerName": "John", "CustomerEmail": "john@gmail.com" }, { "CustomerID": "c4544", "CustomerName": "David", "CustomerEmail": "david@gmail.com" } ] }

API 2 Response (Orders):

json

{ "Order": [ { "OrderID": "o232", "OrderAmount": "15000" }, { "OrderID": "o231", "OrderAmount": "18000" }, { "OrderID": "0233", "OrderAmount": "17000" } ] }

I need to combine these responses into a single payload where each customer is paired with an order, as follows:

Combined Response:

json

{ "Combine": [ { "Customer": [ { "CustomerID": "c8239", "CustomerName": "Robert", "CustomerEmail": "robert@gmail.com" } ], "Order": [ { "OrderID": "o232", "OrderAmount": "15000" } ] }, { "Customer": [ { "CustomerID": "c3242", "CustomerName": "John", "CustomerEmail": "john@gmail.com" } ], "Order": [ { "OrderID": "o231", "OrderAmount": "18000" } ] }, { "Customer": [ { "CustomerID": "c4544", "CustomerName": "David", "CustomerEmail": "david@gmail.com" } ], "Order": [ { "OrderID": "0233", "OrderAmount": "17000" } ] } ] }

Additional Details:

1. Objective: I want to merge the customer data from the first API with the order data from the second API into a single, combined payload.

2. Tools: I am using Oracle Integration Cloud (OIC) and exploring the use of Data Stitch and other OIC features to achieve this.

3. Constraints: The number of customers and orders may vary, and they may not always have a one-to-one correspondence.

Answer 1

If there is no link between customers and orders I assume that it is irrelevant which order belongs to which customer:

WITH    --  S a m p l e    D a t a :
  api_data ( id, customers, orders ) AS
    ( Select 1 as id, 
             JSON_SERIALIZE( '{ "Customers": [ { "CustomerID": "c8239", "CustomerName": "Robert", "CustomerEmail": "robert@gmail.com" }, { "CustomerID": "c3242", "CustomerName": "John", "CustomerEmail": "john@gmail.com" }, { "CustomerID": "c4544", "CustomerName": "David", "CustomerEmail": "david@gmail.com" } ] }' PRETTY ) as customers,
             JSON_SERIALIZE( '{ "Order": [ { "OrderID": "o232", "OrderAmount": "15000" }, { "OrderID": "o231", "OrderAmount": "18000" }, { "OrderID": "0233", "OrderAmount": "17000" } ] }' PRETTY ) as orders
      From Dual
    ),

Use JSON_TABLE to extract columns from your api data and join them to get all the data from both sources in rows

  grid AS 
    ( Select  cst.rn, cst.customer_id, cst.customer_name, cst.customer_mail, 
              ord.order_id, ord.order_amount
      From ( Select ROWNUM as rn, c.*
             From   api_data a,
                    JSON_TABLE ( a.customers, '$.Customers[*]'
                                 Columns ( customer_id   Varchar2(12)    PATH '$[0].CustomerID',
                                           customer_name Varchar2(24)    PATH '$[0].CustomerName', 
                                           customer_mail Varchar2(24)    PATH '$[0].CustomerEmail'
                                         )
                               ) c
           ) cst
    Inner Join ( Select ROWNUM as rn, o.*
                From   api_data a,
                       JSON_TABLE ( a.orders, '$.Order[*]'
                                    Columns ( order_id     Varchar2(12)     PATH '$[0].OrderID',
                                              order_amount Number(14, 2)    PATH '$[0].OrderAmount'
                                             )
                                  ) o
           ) ord ON( ord.rn = cst.rn )
    )

Generate your combined json data using json functions:

SELECT   json_object( 'Combine' 
                       VALUE  json_arrayagg( json_object('Customer' VALUE customers, 
                                                         'Order'    VALUE orders )
                                           )
         )as cust_ord
FROM  ( Select    rn,
                  json_array( json_object('CustomerID'     VALUE customer_id,
                                          'CustomerName'   VALUE customer_name,
                                          'CustomerEmail'  VALUE customer_mail )
                           ) as customers, 
                  json_array( json_object('OrderID'       VALUE order_id,
                                          'OrderAmount'   VALUE To_Char(order_amount) )
                            ) as orders
       From   grid
      )

R e s u l t :

{ "Combine":[ 
                {
                    "Customer": [   {"CustomerID":"c8239","CustomerName":"Robert","CustomerEmail":robert@gmail.com}
                                ],
                    "Order":    [   {"OrderID":"o232","OrderAmount":15000}
                                ]
                },
                {   "Customer": [   {"CustomerID":"c3242","CustomerName":"John","CustomerEmail":john@gmail.com}
                                ],
                    "Order":    [   {"OrderID":"o231","OrderAmount":18000}
                                ]
                },
                {   "Customer": [   {"CustomerID":"c4544","CustomerName":"David","CustomerEmail":david@gmail.com}
                                ],
                    "Order":    [   {"OrderID":"0233","OrderAmount":17000}
                                ]
                }
                
            ]
}

Answer 2

If we assume your assumptions about the fact that ORDERS are in the same order as CUSTOMERS and thus also there is only 1 ORDER per customer, are correct, then you can join 2 JSON_TABLE with a "FOR ORDINALITY" column in each and do the JOIN condition on it:

with customers(js) as (
    select q'~{ "Customers": [ { "CustomerID": "c8239", "CustomerName": "Robert", "CustomerEmail": "robert@gmail.com" }, { "CustomerID": "c3242", "CustomerName": "John", "CustomerEmail": "john@gmail.com" }, { "CustomerID": "c4544", "CustomerName": "David", "CustomerEmail": "david@gmail.com" } ] }~'
),
orders (js) as (
    select q'~{ "Order": [ { "OrderID": "o232", "OrderAmount": "15000" }, { "OrderID": "o231", "OrderAmount": "18000" }, { "OrderID": "0233", "OrderAmount": "17000" } ] }~' 
),
customers_js (idx, js) as (
    select idx, custjs from customers c,
    json_table(
        c.js,
        '$.Customers[*]'
        columns (
            idx for ordinality,
            custjs format json path '$'  
        )
    )
),
orders_js(idx,js) as (
    select idx, orderjs from orders o,
    json_table(
        o.js,
        '$.Order[*]'
        columns (
            idx for ordinality,
            orderjs format json path '$'  
        )
    )
)
select 
    json_object(
        'Combine' is 
        json_arrayagg(
            json_object('Customer' value json_array(c.js), 'Order' is json_array(o.js)) 
        )
    )
from customers_js c
join orders_js o using(idx)
;


{"Combine":[{"Customer":[{"CustomerID":"c8239","CustomerName":"Robert","CustomerEmail":"robert@gmail.com"}],"Order":[{"OrderID":"o232","OrderAmount":"15000"}]},{"Customer":[{"CustomerID":"c3242","CustomerName":"John","CustomerEmail":"john@gmail.com"}],"Order":[{"OrderID":"o231","OrderAmount":"18000"}]},{"Customer":[{"CustomerID":"c4544","CustomerName":"David","CustomerEmail":"david@gmail.com"}],"Order":[{"OrderID":"0233","OrderAmount":"17000"}]}]}