Working with JSON in Multilingual Engine/JavaScript in Oracle Database 23c Free

JSON has become one of the main, if not the main data exchange formats, and certainly one of the most important ways to store data. This article shows that working with JSON is rather easy when using MLE/JavaScript. This doesn't necessarily come as a surprise, after all, JSON is short for JavaScript Object Notation ...

Background

In this example you can see how to use JSON in a relational context. In other words, JSON is stored in a relational table using the JSON data type first introduced in Oracle Database 21c. If you would like to follow along, please head over to the Oracle Database JSON Developers Guide, chapter 4.

The example has been created and tested on Oracle Database 23c Free on Linux x86-64.

Here is an example purchase order you are going to see a lot in this article:

{
  "PONumber": 1600,
  "Reference": "ABULL-20140421",
  "Requestor": "Alexis Bull",
  "User": "ABULL",
  "CostCenter": "A50",
  "ShippingInstructions": {
    "name": "Alexis Bull",
    "Address": {
      "street": "200 Sporting Green",
      "city": "South San Francisco",
      "state": "CA",
      "zipCode": 99236,
      "country": "United States of America"
    },
    "Phone": [
      {
        "type": "Office",
        "number": "909-555-7307"
      },
      {
        "type": "Mobile",
        "number": "415-555-1234"
      }
    ]
  },
  "Special Instructions": null,
  "AllowPartialShipment": true,
  "LineItems": [
    {
      "ItemNumber": 1,
      "Part": {
        "Description": "One Magic Christmas",
        "UnitPrice": 19.95,
        "UPCCode": 13131092899
      },
      "Quantity": 9.0
    },
    {
      "ItemNumber": 2,
      "Part": {
        "Description": "Lethal Weapon",
        "UnitPrice": 19.95,
        "UPCCode": 85391628927
      },
      "Quantity": 5.0
    }
  ]
}

In-Database JavaScript and JSON are a nice match

Let's assume a promotion is run by this hypothetical business. As it's getting closer to Christmas, any customer spending more than a certain amount will get a footbag. Footbags are fun little presents, encourage mobility and coordination.

Here is an example how you could add the free item to the order. Assuming the purchase order is defined as demonstrated in the documentation (example shown above), here is the code:

create or replace mle module purchase_order_module
language javascript as

/**
 * A simple function accepting a purchase Order (as per chapter 4 in the Oracle Database
 * JSON Developer's Guide) 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 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} a flag indicating whether the free item was successfully added
 * @returns {object} the potentially updated purchaseOrder
 * @throws exceptions 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) {

    // sanity checking
    if (po == null || freeItem == null || threshold == null) {
        throw new Error(`mandatory parameter either not provided or null`);
    }

    // ensure 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 using special interfaces, see section
    // 5.2 in the MLE JavaScript Developers Guide
    itemAdded.value = false;

    // get the total order value
    const poValue = po.LineItems
        .map(x => x.Part.UnitPrice * x.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;
}
/

You must create a call specification if you would like to use addFreeItem() in SQL and/or PL/SQL. Here is an example call specification, using a PL/SQL package:

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_module
    signature 'addFreeItem(any, any, number, Out)';

    -- additional code

end purchase_order_pkg;
/

Assuming you decided to provide each of your customers spending more than 300 on your products with a lovely, fun, footbag, this is one way to do it:

-- let's list the number of line items per purchase order
select
  p.id,
  p.po_document.LineItems.size() as numItemsBefore
from
  j_purchaseorder p;

You should see the following output:

SQL> select
  2    p.id,
  3    p.po_document.LineItems.size() as numItemsBefore
  4  from
  5*   j_purchaseorder p;

ID                                     NUMITEMSBEFORE 
___________________________________ _________________ 
09BA7F7EB40F034DE063020011ACD4A2                    2 
09BA7F7EB410034DE063020011ACD4A2                    3

Let's keep that in mind before checking each purchase order for eligibility:

set serveroutput on long 100000 pages 100 lines 120 longchunk 120
declare
    l_free_item     JSON;
    l_updated_po    JSON;
    l_item_added    BOOLEAN;

    -- stub to simulate error handling ... this isn't the way to do it
    -- but should act as a good-enough approximation for this article
    procedure log_error(
        p_id j_purchaseorder.id%type,
        p_error varchar2) 
    as
    begin
        dbms_output.put_line('an error occurred processing ID ' || p_id);
        dbms_output.put_line(p_error);
    end;
begin
    -- this would have typically been selected from the database,
    -- not in this example though, to keep it simple
    -- note how ItemNumber is initialised to -1, it's updated by
    -- add_free_item()
    l_free_item := JSON('{
        "ItemNumber": -1,
        "Part":
        {
            "Description": "Super Fun Footbag",
            "UPCCode": 1234567890,
            "UnitPrice": 0.00
        },
        "Quantity": 1
    }');

    -- iterate over all purchase orders
    for po in (select id, po_document from j_purchaseorder) loop

        begin
            l_item_added := false;

            -- add a free item for purchases > 300
            l_updated_po := purchase_order_pkg.add_free_item(
                p_po            => po.po_document,
                p_free_item     => l_free_item,
                p_threshold     => 300,
                p_item_added    => l_item_added
            );

            -- persist the updated order in the database, skip the operation
            -- if the original PO has not been updated
            if ( l_item_added ) then

                update j_purchaseorder
                set
                    po_document = l_updated_po
                where
                    id = po.id;
            end if;
        exception when others then
            log_error( po.id, sqlerrm );
        end;
    end loop;
end;
/

Let's see if anyone was lucky enough:

select
    p.id,
    p.po_document.LineItems.size() as numItemsafter
from
    j_purchaseorder p;

The number of line items for ID 09BA7F7EB410034DE063020011ACD4A2 has increased by 1:

SQL> select
  2      p.id,
  3      p.po_document.LineItems.size() as numItemsafter
  4  from
  5*     j_purchaseorder p;

ID                                     NUMITEMSAFTER 
___________________________________ ________________ 
09BA7F7EB40F034DE063020011ACD4A2                   2 
09BA7F7EB410034DE063020011ACD4A2                   4

Looks like there's going to be a happy customer:

select
    json_serialize(
        p.po_document.LineItems[3]
        pretty
    ) as freebee
from
    j_purchaseorder p
where
    id = '09BA7F7EB410034DE063020011ACD4A2';

FREEBEE                                                                                                                                                  
_____________________________________________________
{
  "ItemNumber" : 4,
  "Part" :
  {
    "Description" : "Super Fun Footbag",
    "UPCCode" : 1234567890,
    "UnitPrice" : 0
  },
  "Quantity" : 1
}  

Trying to run the same PL/SQL block again won't add the footbag a second time, the JavaScript code ensures that only 1 of them is allowed.

SQL> l
  1  declare
  2      l_free_item     JSON;
  3      l_updated_po    JSON;
  4      l_item_added    BOOLEAN;
  5  
  6      -- stub to simulate error handling ... this isn't the way to do it
  7      -- but should act as a good-enough approximation for this article
  8      procedure log_error(
  9          p_id j_purchaseorder.id%type,
 10          p_error varchar2) 
 11      as
 12      begin
 13          dbms_output.put_line('an error occurred processing ID ' || p_id);
 14          dbms_output.put_line(p_error);
 15      end;
 16  begin
 17      -- this would have typically been selected from the database,
 18      -- not in this example though, to keep it simple
 19      -- note how ItemNumber is initialised to -1, it's updated by
 20      -- add_free_item()
 21      l_free_item := JSON('{
 22          "ItemNumber": -1,
 23          "Part":
 24          {
 25              "Description": "Super Fun Footbag",
 26              "UPCCode": 1234567890,
 27              "UnitPrice": 0.00
 28          },
 29          "Quantity": 1
 30      }');
 31  
 32      -- iterate over all purchase orders
 33      for po in (select id, po_document from j_purchaseorder) loop
 34  
 35          begin
 36              l_item_added := false;
 37  
 38              -- add a free item for purchases > 300
 39              l_updated_po := purchase_order_pkg.add_free_item(
 40                  p_po            => po.po_document,
 41                  p_free_item     => l_free_item,
 42                  p_threshold     => 300,
 43                  p_item_added    => l_item_added
 44              );
 45  
 46              -- persist the updated order in the database, skip the operation
 47              -- if the original PO has not been updated
 48              if ( l_item_added ) then
 49  
 50                  update j_purchaseorder
 51                  set
 52                      po_document = l_updated_po
 53                  where
 54                      id = po.id;
 55              end if;
 56          exception when others then
 57              log_error( po.id, sqlerrm );
 58          end;
 59      end loop;
 60* end;
SQL> /
an error occurred processing ID 09BA7F7EB410034DE063020011ACD4A2
ORA-04161: Error: Super Fun Footbag has already been added to order 672
ORA-04171: at addFreeItem (MARTIN.PURCHASE_ORDER_MODULE:30:15)


PL/SQL procedure successfully completed.

Summary

The goal of this article was to demonstrate the ease-of-use of In-Database JavaScript when it comes to working with JSON.