Method PUT() to Update Parts of JSON Documents (Con arrays)

 De la JSON Developer's Guide de Oracle:

Example 25-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}

 Fuentes.

Artículo:   "Example 25-3 Using Method PUT() to Update Parts of JSON Documents" Publicado en https://docs.oracle.com/. Consultado el 25/08/2024.

URL: https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/using-PLSQL-object-types-for-JSON.html#GUID-F0561593-D0B9-44EA-9C8C-ACB6AA9474EE