When using textual JSON data to perform an INSERT or UPDATE operation on a JSON type column, the data is implicitly wrapped with constructor JSON. If the column is instead VARCHAR2, CLOB, or BLOB, then use condition is json as a check constraint, to ensure that the data inserted is (well-formed) JSON data.

Example 4-1, Example 4-2 and Example 4-3 illustrate this. They create and fill a table that holds data used in examples elsewhere in this documentation. For brevity, only two rows of data (two JSON documents) are inserted in Example 4-3.

Example 4-1 and Example 4-2 are alternative ways to create the table, using JSON type and VARCHAR2, respectively.

When defining a JSON-type column you can follow the type keyword JSON with a JSON-type modifier, in parentheses: (OBJECT), (ARRAY), or (SCALAR). This requires the column content to be a JSON object, array, or scalar value, respectively. (This is similar to using VARCHAR(42) instead of just VARCHAR2.)

Modifier keyword SCALAR can be followed by a keyword that specifies the required type of scalar: BOOLEAN, BINARY, BINARY_DOUBLE, BINARY_FLOAT, DATE, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, NULL, NUMBER, STRING, TIMESTAMP, or TIMESTAMP WITH TIME ZONE.

You can provide more than one modifier between the parentheses, separating them with commas. For example, (OBJECT, ARRAY) requires nonscalar values, and (OBJECT, SCALAR DATE) allows only objects or dates.

You can follow the specified modifiers OBJECT, ARRAY, and SCALAR (possibly followed by a scalar-type keyword) with an optional LIMIT keyword followed by the maximum number of bytes (an integer numeral) allotted for the JSON-type column. Specifying a size limit for the column can allow for more efficient handling of that data.

If the specified modifiers include ARRAY and they're not followed by keyword LIMIT, then keyword ARRAY can be followed, within parentheses (( )), by syntax that further specifies the possible array values:

1. A (single) scalar type for all of the array elements. (You can use any of the type names that can follow modifier SCALAR.)

2. Optionally, keyword ALLOW, or DISALLOW, followed by keyword NULL. For ALLOW this means that in addition to the specified scalar type (#1, above), any array elements can be JSON null, respectively. The default behavior is DISALLOW NULL, which means that (unless the specified scalar type is itself NULL) no array elements can be JSON null.

3. Optionally, a comma (,) followed by an asterisk (*) or an integer indicating the maximum number of elements in an array. An asterisk, or nothing, means there is no array size limit.

4. Optionally, a comma (,) followed by keyword SORT, which means store each array value with its elements sorted in ascending order, according to the scalar element type. For example, string values are sorted lexicographically, numeric values are sorted numerically, dates are sorted by (increasing) time.

CREATE TABLE j_purchaseorder
  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
   date_loaded TIMESTAMP (6) WITH TIME ZONE,
   data        JSON);

CREATE TABLE j_purchaseorder
  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
   date_loaded TIMESTAMP (6) WITH TIME ZONE,
   data        JSON (OBJECT));

CREATE TABLE j_purchaseorder
  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
   date_loaded TIMESTAMP (6) WITH TIME ZONE,
   data        VARCHAR2 (23767)
   CONSTRAINT ensure_json CHECK (data is json));

INSERT INTO j_purchaseorder
  VALUES (
    SYS_GUID(),
    to_date('30-DEC-2014'),
    '{"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}]}');

INSERT INTO j_purchaseorder
  VALUES (
    SYS_GUID(),
    to_date('30-DEC-2014'),
    '{"PONumber"             : 672,
      "Reference"            : "SBELL-20141017",
      "Requestor"            : "Sarah Bell",
      "User"                 : "SBELL",
      "CostCenter"           : "A50",
      "ShippingInstructions" : {"name"    : "Sarah Bell",
                                "Address" : {"street"  : "200 Sporting Green",
                                             "city"    : "South San Francisco",
                                             "state"   : "CA",
                                             "zipCode" : 99236,
                                             "country" : "United States of America"},
                                "Phone"   : "983-555-6509"},
      "Special Instructions" : "Courier",
      "LineItems"            :
        [{"ItemNumber" : 1,
          "Part"       : {"Description" : "Making the Grade",
                          "UnitPrice"   : 20,
                          "UPCCode"     : 27616867759},
          "Quantity"   : 8.0},
         {"ItemNumber" : 2,
          "Part"       : {"Description" : "Nixon",
                          "UnitPrice"   : 19.95,
                          "UPCCode"     : 717951002396},
          "Quantity"   : 5},
         {"ItemNumber" : 3,
          "Part"       : {"Description" : "Eric Clapton: Best Of 1981-1999",
                          "UnitPrice"   : 19.95,
                          "UPCCode"     : 75993851120},
          "Quantity"   : 5.0}]}');

Each of these views lists the column name, data type, and format (TEXT or BINARY); the table or view name (column TABLE_NAME); and whether the object is a table or a view (column OBJECT_TYPE).

A JSON data type column always contains only well-formed JSON data, so each such column is always listed, with its type as JSON.

For a column that is not JSON type to be considered JSON data it must have an is json check constraint. But in the case of a view, any one of the following criteria suffices for a column to be considered JSON data:

• The underlying data has the data type JSON.

• The underlying data has an is json check constraint.

• The column results from the use of a SQL/JSON generation function, such as json_object.

• The column results from the use of SQL/JSON function json_query.

• The column results from the use of SQL function json_mergepatch, json_scalar, json_serialize, or json_transform.

• The column results from the use of the JSON data type constructor, JSON.

If an is json check constraint, which constrains a table column to contain only JSON data, is later deactivated, the column remains listed in the views. If the check constraint is dropped then the column is removed from the views.