21 Using PL/SQL Object Types for JSON

Example 21-1 Constructing and Serializing an In-Memory JSON Object

This example uses function parse to parse a string of JSON data that represents a JSON object with one field, name, creating an instance je of object type JSON_ELEMENT_T. This instance is tested to see if it represents an object, using introspection method (predicate) is_object().

If it represents an object (the predicate returns TRUE for je), it is cast to an instance of JSON_OBJECT_T and assigned to variable jo. Method put() for object type JSON_OBJECT_T is then used to add object field price with value 149.99.

Finally, JSON_ELEMENT_T instance je (which is the same data in memory as JSON_OBJECT_T instance jo) is serialized to a string using method to_string(), and this string is printed out using procedure DBMS_OUTPUT.put_line. The result printed out shows the updated object as {"name":"Radio-controlled plane","price":149.99}.

The updated transient object je is serialized here only to be printed out; the resulting text is not stored in the database. Sometime after the example code is executed, the memory allocated for object-type instances je and jo is reclaimed by the garbage collector.

DECLARE
  je JSON_ELEMENT_T;
  jo JSON_OBJECT_T;
BEGIN
  je := JSON_ELEMENT_T.parse('{"name":"Radio controlled plane"}');
  IF (je.is_Object) THEN
    jo := treat(je AS JSON_OBJECT_T);
    jo.put('price', 149.99);
  END IF;
  DBMS_OUTPUT.put_line(je.to_string);
END;
/

Example 21-2 Using Method GET_KEYS() to Obtain a List of Object Fields

PL/SQL method get_keys() is defined for PL/SQL object type JSON_OBJECT_T. It returns an instance of PL/SQL object type JSON_KEY_LIST, which is a varray of VARCHAR2(4000). The varray contains all of the field names for the given JSON_OBJECT_T instance.

This example iterates through the fields returned by get_keys(), adding them to an instance of PL/SQL object type JSON_ARRAY_T. It then uses method to_string() to serialize that JSON array and then prints the resulting string.

DECLARE
  jo          JSON_OBJECT_T;
  ja          JSON_ARRAY_T;
  keys        JSON_KEY_LIST;
  keys_string VARCHAR2(100);
BEGIN
  ja := new JSON_ARRAY_T;
  jo := JSON_OBJECT_T.parse('{"name":"Beda", 
                              "jobTitle":"codmonki", 
                              "projects":["json", "xml"]}');
  keys := jo.get_keys;
  FOR i IN 1..keys.COUNT LOOP
     ja.append(keys(i));
  END LOOP;
  keys_string := ja.to_string;
  DBMS_OUTPUT.put_line(keys_string);
END;
/

The printed output is ["name","jobTitle","projects"].

Example 21-3 Using Method PUT() to Update Parts of JSON Documents

This example updates each purchase-order document in JSON column po_document of table j_purchaseorder. It iterates over the JSON array LineItems in each document (variable li_arr), calculating the total price and quantity for each line-item object (variable li_obj), and it uses method put() to add these totals to li_obj as the values of new fields totalQuantity and totalPrice. This is done by user-defined function add_totals.

The SELECT statement here selects one of the documents that has been updated.

CREATE OR REPLACE FUNCTION add_totals(purchaseOrder IN VARCHAR2) RETURN VARCHAR2 IS
  po_obj        JSON_OBJECT_T;
  li_arr        JSON_ARRAY_T;
  li_item       JSON_ELEMENT_T;
  li_obj        JSON_OBJECT_T;
  unitPrice     NUMBER;
  quantity      NUMBER;
  totalPrice    NUMBER := 0;
  totalQuantity NUMBER := 0;
BEGIN
  po_obj := JSON_OBJECT_T.parse(purchaseOrder);
  li_arr := po_obj.get_Array('LineItems');
  FOR i IN 0 .. li_arr.get_size - 1 LOOP
    li_obj := JSON_OBJECT_T(li_arr.get(i));
    quantity := li_obj.get_Number('Quantity');
    unitPrice := li_obj.get_Object('Part').get_Number('UnitPrice');
    totalPrice := totalPrice + (quantity * unitPrice);
    totalQuantity := totalQuantity + quantity;
  END LOOP;
  po_obj.put('totalQuantity', totalQuantity);
  po_obj.put('totalPrice', totalPrice);
  RETURN po_obj.to_string;
END;
/

UPDATE j_purchaseorder SET (po_document) = add_totals(po_document);

SELECT po_document FROM j_purchaseorder po WHERE po.po_document.PONumber = 1600;

That selects this updated document:

{"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}],
 "totalQuantity": 14,
 "totalPrice": 279.3}