Oracle Apex - REST data source - nested JSON array - sync two tables - where to write SQL

Question

This question is a follow up to another SO question.

Summary: I have an API returning a nested JSON array. Data is being extracted via APEX REST Data Sources. The Row Selector in the Data Profile is set to "." (to select the "root node").

The lines array has been manually added to a column (LINES) to the Data Profile, set data type to JSON Document, and used lines as the selector.

SAMPLE JSON RESPONSE FROM API
[ {
  "order_number": "so1223",
  "order_date": "2022-07-01",
  "full_name": "Carny Coulter",
  "email": "[email protected]",
  "credit_card": "3545556133694494",
  "city": "Myhiya",
  "state": "CA",
  "zip_code": "12345",
  "lines": [
    {
      "product": "Beans - Fava, Canned",
      "quantity": 1,
      "price": 1.99
    },
    {
      "product": "Edible Flower - Mixed",
      "quantity": 1,
      "price": 1.50
    }
  ]
},
{
  "order_number": "so2244",
  "order_date": "2022-12-28",
  "full_name": "Liam Shawcross",
  "email": "[email protected]",
  "credit_card": "6331104669953298",
  "city": "Humaitá",
  "state": "NY",
  "zip_code": "98670",
  "lines": [
    {
      "order_id": 5,
      "product": "Beans - Green",
      "quantity": 2,
      "price": 4.33
    },
    {
      "order_id": 1,
      "product": "Grapefruit - Pink",
      "quantity": 5,
      "price": 5.00
    }
  ]
},
]

The order attributes have been synchronized to a local table (Table name: SOTEST_LOCAL)

The table has the correct data. As you can see below, the LINES column contains the JSON array.

I then created an ORDER_LINES child table to extract the JSON from LINES column in the SOTEST_LOCAL table. (Sorry for the table names.. I should've named the tables as ORDERS_LOCAL and ORDER_LINES_LOCAL)

CREATE TABLE  "SOTEST_ORDER_LINES_LOCAL" 
   (    "LINE_ID" NUMBER, 
    "ORDER_ID" NUMBER, 
    "LINE_NUMBER" NUMBER, 
    "PRODUCT" VARCHAR2(200) COLLATE "USING_NLS_COMP", 
    "QUANTITY" NUMBER, 
    "PRICE" NUMBER, 
     CONSTRAINT "SOTEST_ORDER_LINES_LOCAL_PK" PRIMARY KEY ("LINE_ID")
  USING INDEX  ENABLE
   )  DEFAULT COLLATION "USING_NLS_COMP"
/
ALTER TABLE  "SOTEST_ORDER_LINES_LOCAL" ADD CONSTRAINT "SOTEST_ORDER_LINES_LOCAL_FK" FOREIGN KEY ("ORDER_ID")
      REFERENCES  "SOTEST_LOCAL" ("ORDER_ID") ON DELETE CASCADE ENABLE
/

QuickSQL version..

SOTEST_ORDER_LINES_LOCAL
    LINE_ID /pk
    ORDER_ID /fk SOTEST_LOCAL references ORDER_ID
    LINE_NUMBER
    PRODUCT
    QUANTITY
    PRICE

So per Carsten's answer in the previous question, I can write SQL to extract the JSON array from the LINES column in the SOTEST_LOCAL table to the child table SOTEST_ORDER_LINES_LOCAL.

My question is two parts.

Where exactly do I write the SQL? Would I write it in SQL Workshop in SQL Commands?
The REST data is being synchronized to make a request every hour. So would I need to write a function that runs every time there is new data being merged?

Carsten · Accepted Answer · 2023-01-21 16:13:16Z

there are multiple options for this:

Create a trigger on the local synchronization table

You could create an trigger on your ORDERS table, which runs AFTER INSERT, UPDATE or DELETE on your ORDERS table, and which maintains the LINES table. The nice things about this one is that the maintenance of the child table is independent from APEX or the REST Synchronization; it would also work if you just inserted rows with plain SQL*Plus.

Here's some pseudo-code on how the trigger could look like.

create or replace trigger tr_maintain_lines
after insert or update or delete on ORDERS_LOCAL
for each row
begin
    if inserting then
        insert into SOTEST_ORDER_LINES_LOCAL ( order_id, line_id, line_number, product, quantity, price) 
        ( select :new.id,
                 seq_lines.nextval,
                 j.line#,
                 j.product,
                 j.quantity,
                 j.price
            from json_table( 
                     :new.lines,
                     '$[*]' columns (
                         line# for ordinality,
                         product  varchar2(255) path '$.product',
                         quantity number        path '$.quantity',
                         price    number        path '$.price' ) ) );
    elsif deleting then
        delete SOTEST_ORDER_LINES_LOCAL
         where order_id = :old.id;
    elsif updating then
        -- 
        -- handle the update case here.
        -- I would simply delete and re-insert LINES rows.
    end if;
end;

Handle child table maintenance in APEX itself.

You could turn off the schedule of your REST Source synchronization, and have it only running when called with APEX_REST_SOURCE_SYNC.SYNCHRONIZE_DATA (https://docs.oracle.com/en/database/oracle/apex/22.1/aeapi/SYNCHRONIZE_DATA-Procedure.html#GUID-660DE4D1-4BAF-405A-A871-6B8C201969C9). Then create an APEX Automation, which runs on your desired schedule, and this automation has two Actions. One would be the REST Source Synchronization, the other one would call PL/SQL code to maintain the child tables.

Have a look into this blog posting which talks a bit about more complex synchronization scenarios (although it does exactly fit scenario): https://blogs.oracle.com/apex/post/synchronize-parent-child-rest-sources

I hope this helps

Hi Carsten, your help is sincerely appreciated. I've gone ahead and created a trigger on the ORDERS table. I actually have a follow up question stackoverflow.com/questions/75199190/… Please take a look. Thank you very much!

Collectives™ on Stack Overflow

Oracle Apex - REST data source - nested JSON array - sync two tables - where to write SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related