Example 7-15 Inserting JSON Data into a Database Table

This example assumes that a REST API has been published in ORDS, allowing users to POST JSON data to the database. This way, administrators have the option to upload further departments into the departments table. Once the JSON data has been received, the MLE module uses JSON_TABLE() to convert the JSON data structure into a relational model.

CREATE TABLE departments(
    department_id NUMBER NOT NULL PRIMARY KEY,
    department_name VARCHAR2(50) NOT NULL,
    manager_id NUMBER,
    location_id NUMBER
);

CREATE OR REPLACE FUNCTION REST_API_DEMO(
    "depts" JSON
) RETURN BOOLEAN
AS MLE LANGUAGE JAVASCRIPT
{{
    /**
    *insert a number of department records, provided as JSON,
    *into the departments table
    *@params {object} depts - an array of departments
    */

    if(depts.constructor !== Array){
        throw new Error('must provide an array of departments to this function');
    }

    //convert JSON input to relational data and insert into a table
    const result = session.execute(`
        INSERT INTO departments(
            department_id,
            department_name,
            manager_id,
            location_id
        )
        SELECT
            jt.*
        FROM json_table(:depts, '$[*]' columns
            department_id    path    '$.department_id',
            department_name  path    '$.department_name',
            manager_id       path    '$.manager_id',
            location_id      path    '$.location_id'
        ) jt`,
        {
            depts:{
                val: depts,
                type: oracledb.DB_TYPE_JSON
            }
        }
    );

    if(result.rowsAffected !== depts.length){
        return false;
    } else {
        return true;
    }
}};
/

Using the following anonymous PL/SQL block to simulate the REST call, additional departments can be inserted into the table:

DECLARE
    l_success boolean := false;
    l_depts JSON;
BEGIN
    l_depts := JSON('[
        {
            "department_id": 1010,
            "department_name": "New Department 1010",
            "manager_id": 200,
            "location_id": 1700
        },
        {
            "department_id": 1020,
            "department_name": "New Department 1020",
            "manager_id": 201,
            "location_id": 1800
        },
        {
            "department_id": 1030,
            "department_name": "New Department 1030",
            "manager_id": 114,
            "location_id": 1700
        },
        {
            "department_id": 1040,
            "department_name": "New Department 1040",
            "manager_id": 203,
            "location_id": 2400
        }]'
    );

    l_success := REST_API_DEMO(l_depts);

    IF NOT l_success THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'an unexpected error occurred ' || sqlerrm
        );
    END IF;
END;
/

The data has been inserted successfully as demonstrated by the following query:

SELECT *
FROM departments
WHERE department_id > 1000;

Result:

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
         1010 New Department 1010                   200        1700
         1020 New Department 1020                   201        1800
         1030 New Department 1030                   114        1700
         1040 New Department 1040                   203        2400

Example 7-16 Use JavaScript to Manipulate JSON Data

Rather than using SQL functions like JSON_TABLE, JSON_TRANSFORM, and so on, it is possible to perform JSON data manipulation in JavaScript as well.

This example is based on the J_PURCHASEORDER table as defined in Oracle Database JSON Developer’s Guide. This table stores a JSON document containing purchase orders from multiple customers. Each purchase order consists of one or more line items.

The following function, addFreeItem(), allows the addition of a free item to customers ordering merchandise in excess of a threshold value.

CREATE OR REPLACE MLE MODULE purchase_order_mod
LANGUAGE JAVASCRIPT AS

/**
 *a simple function accepting a purchase order and checking whether
 *its value is high enough to merit the addition of a free item
 *
 *@param {object} po the purchase order to be checked
 *@param {object} freeItem which free item to add to the order free of charge
 *@param {number} threshold the minimum order value before a free item can be added
 *@param {boolean} itemAdded a flag indicating whether the free item was successfully added
 *@returns {object} the potentially updated purchaseOrder
 *@throws exception in case
 *    -any of the mandatory parameters is null
 *    -in the absence of line items
 *    -if the free item has already been added to the order
 */
export function addFreeItem(po, freeItem, threshold, itemAdded){
    
    //ensure values for parameters have been provided
    if(po == null || freeItem == null || threshold == null){
        throw new Error(`mandatory parameter either not provided or null`);
    }

    //make sure there are line items provided by the purchase order
    if(po.LineItems === undefined) {
        throw new Error(
            `PO number ${po.PONumber} does not contain any line items`
        );
    }

    //bail out if the free item has already been added to the purchase order
    if(po.LineItems.find(({Part}) => Part.Description === freeItem.Part.Description)){
        throw new Error(`${freeItem.Part.Description} has already been added to order ${po.PONumber}`);
    }

    //In, Out, and InOut Parameters are implemented in JavaScript using 
    //special interfaces
    itemAdded.value = false;

    //get the total order value
    const poValue = po.LineItems
        .map(x => x.Part.UnitPrice * c.Quantity)
        .reduce(
            (accumulator, currentValue) => accumulator + currentValue, 0
        );

    //add a free item to the purchase order if its value exceeds 
    //the threshold
    if(poValue > threshold){
        
        //update the ItemNumber
        freeItem.ItemNumber = (po.LineItems.length + 1)
        po.LineItems.push(freeItem);
        itemAdded.value = true;
    }

    return po;
}
/

As with every MLE module, you must create a call specification before you can use it in SQL and PL/SQL. The following example wraps the call to add_free_item() into a package. The function accepts a number of parameters, including an OUT parameter, requiring an extended signature clause mapping the PL/SQL types to MLE types. The SQL data type JSON maps to the MLE ANY type. Because there is no concept of an OUT parameter in JavaScript, the final parameter, p_item_added, must be passed using the Out interface. For a more detailed discussion about using bind parameters with JavaScript, see OUT and IN OUT Parameters.

CREATE OR REPLACE PACKAGE purchase_order_pkg AS

  FUNCTION add_free_item(
    p_po            IN JSON, 
    p_free_item     IN JSON,
    p_threshold     IN NUMBER,
    p_item_added    OUT BOOLEAN
  )
  RETURN JSON AS
  MLE MODULE purchase_order_mod
  SIGNATURE 'addFreeItem(any, any, number, Out<boolean>)';

  --additional code

END purchase_order_pkg;
/