Inserting and Updating JSON Data

The usual ways to insert and update data to a TimesTen database work with JSON data. All of the usual database APIs used to insert or update LOB or VARCHAR2 columns can be used for JSON type columns. Textual JSON input is automatically converted to JSON type.

When using textual JSON data to perform an INSERT or UPDATE operation on a JSON type column, the data is implicitly wrapped with the JSON data type constructor.

Inserting a JSON document into a JSON type column is a straightforward operation, as shown in Example 2-2.

Updating a JSON document in a JSON type column requires that you update the entire JSON document. TimesTen does not support the update of specific portions of a JSON document. See Example 2-3.

See also:

• INSERT and INSERT...SELECT in Oracle TimesTen In-Memory Database SQL Reference

• UPDATE in Oracle TimesTen In-Memory Database SQL Reference

Example 2-2 Inserting JSON Data Into a JSON Type Column

This example inserts two rows of data into the j_purchaseorder table. The third column, po_document, contains JSON data.

INSERT INTO j_purchaseorder (id, date_loaded, po_document)
  SELECT 
    COUNT (id), 
    SYSDATE, 
    '{"PONumber" : 1599,
      "Reference" : "AERRAZUR-20140405",
      "Requestor" : "Alberto Errazuriz",
      "User" : "AERRAZUR",
      "CostCenter" : "A80",
      "ShippingInstructions" : 
        {"name" : "Alberto Errazuriz",
         "Address" : 
           {"street" : "Magdalen Centre, The Isis Science Park",
            "city" : "Oxford",
            "county" : "Oxon.",
            "postcode" : "OX9 9ZB",
            "country" : "United Kingdom"},
         "Phone" :
           [{"type" : "Office",
             "number" : "57-555-983"}]},
      "Special Instructions" : "Priority Overnight",
      "LineItems" :
        [{"ItemNumber" : 1,
          "Part" :
            {"Description" : "Gummo",
             "UnitPrice" : 27.95,
             "UPCCode" : 794043523625},
          "Quantity" : 8},
         {"ItemNumber" : 2,
          "Part" :
            {"Description" : "Sirens",
             "UnitPrice" : 19.95,
             "UPCCode" : 717951001931},
          "Quantity" : 7},
         {"ItemNumber" : 3,
          "Part" :
            {"Description" : "Karaoke: Favorite Duets 1",
             "UnitPrice" : 19.95,
             "UPCCode" : 13023025295},
          "Quantity" : 9}]}' 
  FROM j_purchaseorder;

INSERT INTO j_purchaseorder (id, date_loaded, po_document)
  SELECT 
    COUNT (id), 
    SYSDATE, 
    '{"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},
         {"ItemNumber" : 2,
          "Part" :
            {"Description" : "Lethal Weapon",
             "UnitPrice" : 19.95,
             "UPCCode" : 85391628927},
          "Quantity" : 5}]}' 
  FROM j_purchaseorder;

Example 2-3 Updating a JSON Document In a JSON Type Column

This example updates a JSON document in the JSON type column, po_document, of the j_purchaseorder table.

UPDATE j_purchaseorder po
  SET po_document = 
    '{"PONumber" : 1599,
      "Reference" : "AERRAZUR-20140405",
      "Requestor" : "Alberto Errazuriz",
      "User" : "AERRAZUR",
      "CostCenter" : "A80",
      "ShippingInstructions" : 
        {"name" : "Alberto Errazuriz",
         "Address" : 
           {"street" : "Magdalen Centre, The Isis Science Park",
            "city" : "Oxford",
            "county" : "Oxon.",
            "postcode" : "OX9 9ZB",
            "country" : "United Kingdom"},
         "Phone" :
           [{"type" : "Office",
             "number" : "57-555-983"}]},
      "Special Instructions" : "Priority Overnight",
      "LineItems" :
        [{"ItemNumber" : 1,
          "Part" :
            {"Description" : "Gummo",
             "UnitPrice" : 27.95,
             "UPCCode" : 794043523625},
          "Quantity" : 8}]}' 
  WHERE po.po_document.PONumber = 1599;