Search code examples
oracle-apexapex

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

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)

SOTEST_LOCAL table

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

SOTEST_LOCAL table data

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?

Solution

  • there are multiple options for this:

    1. 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;

    1. 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