How to insert data that comes from JSON into a table in Oracle APEX

Question

I have an application in Oracle Apex that needs to consume data from a JSON that comes through a webhook, and insert it into my table.

The data is coming in the following structure:

{
  "event_type": "WEBHOOK.MARKED_OPPORTUNITY",
  "entity_type": "CONTACT",
  "event_identifier": "my-event-identifier",
  "timestamp": "2018-03-13T14:09:02.724-03:00",
  "event_timestamp": "2018-03-13T14:07:04.254-03:00",
  "contact": {
    "uuid": "c2f3d2b3-7250-4d27-97f4-eef38be32f7f",
    "email": "[email protected]",
    "name": "Contact Name",
    "job_title": "Developer",
    "bio": "This is my bio",
    "website": "http://rdstation.com.br",
    "personal_phone": "48 30252598",
    "mobile_phone": "48 30252598",
    "city": "Florianópolis",
    "facebook": "Contact Facebook",
    "linkedin": "Contact Linkedin",
    "twitter": "Contact Twitter",
    "tags": [
      "tag 1",
      "tag 2"
    ],
    "cf_custom_field_example": [
      "Option1",
      "Option2"
    ],
    "company": {
      "name": "Company Example 0"
    },
    "funnel": {
      "name": "default",
      "lifecycle_stage": "Lead",
      "opportunity": false,
      "contact_owner_email": "[email protected]",
      "interest": 20,
      "fit": 0,
      "origin": "Orgânico"
    }
  }
}

My question is how do I insert some of this data into my table when I trigger POST?

I'm trying this PL/SQL on my RESTful data services POST method

DECLARE
    new_id INTEGER;
    current_date DATE;
    blob_body BLOB := :body;
    clob_variable CLOB := CONVERT_TO_CLOB(blob_body);
    
    v_name VARCHAR2(100);
    v_email VARCHAR2(100);
BEGIN
    SELECT SYSDATE INTO current_date FROM dual;

    DECLARE
        v_json_obj JSON_OBJECT_T;
    BEGIN
        v_json_obj := JSON_OBJECT_T.parse(clob_variable);
        
        v_name := v_json_obj.get_String('name');
        v_email := v_json_obj.get_String('email');
    END;

    INSERT INTO LEADS (ID, NOME, EMAIL)
    VALUES (121212, v_name, v_email)
    RETURNING ID INTO new_id;

    :status_code := 201;
    :forward_location := '../employees/' || new_id;

EXCEPTION
    WHEN VALUE_ERROR THEN
        :errmsg := 'Wrong value.';
        :status_code := 400;
    WHEN OTHERS THEN 
        :status_code := 400;
        :errmsg := SQLERRM;
END;

I need to insert name and email value from JSON on my table LEADS.

Answer 1

You can do it using JSON_TABLE:

DECLARE
  v_date  DATE := SYSDATE;
  v_id    LEADS.ID%TYPE;
  v_name  LEADS.NOME%TYPE;
  v_email LEADS.EMAIL%TYPE;
BEGIN
  SELECT name, email
  INTO   v_name, v_email
  FROM   JSON_TABLE(
           :body,
           '$'
           COLUMNS (
             name  VARCHAR2(100) PATH '$.contact.name',
             email VARCHAR2(100) PATH '$.contact.email'
           )
         );

  INSERT INTO LEADS (ID, NOME, EMAIL)
  VALUES (DEFAULT, v_name, v_email)
  RETURNING ID INTO v_id;

  :status_code := 201;
  :forward_location := '../employees/' || v_id;
EXCEPTION
    WHEN VALUE_ERROR THEN
        :errmsg := 'Wrong value.';
        :status_code := 400;
    WHEN OTHERS THEN 
        :status_code := 400;
        :errmsg := SQLERRM;
END;
/