Tables With JSON Columns

4 Tables With JSON Columns

You can store JSON data in columns of database tables. If your use case is mainly document-centric you can store the documents in JSON collection tables for easiest use with document APIs. However you store JSON data, you can access, query, and update it in the same ways.

Creating Tables With JSON Columns
You can create a database table that has one or more JSON columns, alone or with relational columns. Oracle recommends that you use JSON data type for the JSON columns.
Limiting the Data in JSON-Type Columns
By adding modifier keywords to a JSON-type column specification when you create a table, you can specify the column size in bytes, and you can limit the column values to be only objects, arrays, scalars, or a combination.
Determining Whether a Column Must Contain Only JSON Data
How can you tell whether a given column of a table or view can contain only well-formed JSON data? Whenever this is the case, the column is listed in the following static data dictionary views: DBA_JSON_COLUMNS, USER_JSON_COLUMNS, and ALL_JSON_COLUMNS.

Parent topic: Store and Manage JSON Data

4.1 Creating Tables With JSON Columns

You can create a database table that has one or more JSON columns, alone or with relational columns. Oracle recommends that you use JSON data type for the JSON columns.

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.

Note:

A check constraint can reduce performance for data INSERT and UPDATE operations. If you are sure that your application uses only well-formed JSON data for a particular column, then consider disabling the check constraint, but do not drop the constraint.

Note:

SQL/JSON conditions is json and is not json return true or false for any non-NULL SQL value. But they both return unknown (neither true nor false) for SQL NULL. When used in a check constraint, they do not prevent a SQL NULL value from being inserted into the column. (But when used in a SQL WHERE clause, SQL NULL is never returned.)

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

You can restrict the data stored in a JSON-type column to be of a certain size in bytes, and you can restrict its values to be particular kinds of JSON data. See Limiting the Data in JSON-Type Columns.

See Also:

Loading External JSON Data for the creation of the full table j_purchaseorder
JSON Storage Clause in Oracle Database SQL Language Reference for information about CREATE TABLE

Example 4-1 Creating a Table with a JSON Type Column

This example creates table j_purchaseorder with JSON data type column data. Oracle recommends that you store JSON data as JSON type.

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

Example 4-2 Using IS JSON in a Check Constraint to Ensure Textual JSON Data is Well-Formed

This example creates table j_purchaseorder with a VARCHAR2 column for the JSON data. It uses a check constraint to ensure that the textual data in the column is well-formed JSON data. Always use such a check constraint if you use a data type other than JSON to store JSON data.

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));

The JSON data allowed here must be well-formed, but it can be lax or strict. Example 5-1 is a similar example, but it requires the well-formed JSON data to be strict.

Example 4-3 Inserting JSON Data Into a JSON Column

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

Note that if the data type of the third column is JSON (as in Example 4-1) and you insert textual data into that column, as in this example, the data is implicitly wrapped with the JSON constructor to provide JSON type data.

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}]}');

Related Topics

Limiting the Data in JSON-Type Columns

Parent topic: Tables With JSON Columns

4.2 Limiting the Data in JSON-Type Columns

By adding modifier keywords to a JSON-type column specification when you create a table, you can specify the column size in bytes, and you can limit the column values to be only objects, arrays, scalars, or a combination.

You can limit scalar values to a particular type, such as a date, or to more than one scalar type, such a date or a string. JSON arrays can be kept sorted, limited to a certain number of elements, and limited to having elements of a particular scalar type.

When defining a JSON-type column, to require its content to be a JSON object, array, or scalar value, you use a JSON-type modifier, in parentheses, after type keyword JSON: (OBJECT), (ARRAY), or (SCALAR), respectively.

You can limit the size of a JSON-type column by following the column specification (with or without type modifiers OBJECT, ARRAY, and SCALAR) with an optional LIMIT modifier and the maximum number of bytes (a positive-integer numeral) to be allotted for the column. Specifying a size limit for the column can allow for more efficient handling of that data. (This is analogous to using VARCHAR2(42) or VARCHAR2(500) instead of just VARCHAR2.)

Some particular use cases:

Modifier OBJECT presents perhaps the most common use case for JSON data. Oracle JSON collections use this modifier under the covers, for example, and document databases typically store only documents that are JSON objects.
A SCALAR modifier can sometimes be useful. A combination of scalar types, such as DATE and VARCHAR2, can allow different types for semantically equivalent or similar data.
If an application requires or expects the JSON column values to be limited in size, then limiting the column size can allow for more efficient handling.

Example 4-4 Creating a Table With a JSON-Type Column Of Objects

This table definition does the same thing as that in Example 4-1, except that it uses modifier OBJECT to require the data in column data to be a JSON object. (It is an error to try to insert a JSON value other than an object.)

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

Example 4-5 Creating a Table With a JSON-Type Object Column of Limited Size

Like Example 4-4, this example requires column data to contain JSON objects. In addition, it limits the binary (OSON format) size of each such object to 500 bytes. If modifier OBJECT was missing here then JSON values of all types could be stored, with a maximum of 500 bytes for each value.

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

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), as in Example 4-6, requires nonscalar values, and (OBJECT, SCALAR DATE) allows only objects or dates.

Example 4-6 Creating a Table With a JSON-Type Column of Objects Or Dates

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

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

A (single) scalar type other than NULL, for all of the array elements. (You can use any of the type names that can follow modifier SCALAR except NULL.)
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.

The default behavior is DISALLOW NULL, which means that no array elements can be JSON null.
Optionally^Foot 1, a comma (,) followed by an asterisk (*) or a positive integer indicating the maximum number of elements in an array. An asterisk, or nothing, means there is no array size limit. (By default, an asterisk is implied.)
Optionally, a comma (,) followed by keyword SORT, which means store each array value with its elements sorted in ascending canonical sort order. This means that nulls occur before non-null elements, which are in ascending order for their type. For example, string values are sorted lexicographically, numeric values are sorted numerically, dates are sorted by (increasing) time.

Sorted arrays can be more performant for some operations, such as the use of SQL/JSON condition json_exists with filter condition in (which checks whether a JSON value is a member of a given list).

Example 4-7 Creating a Table With a JSON-Type Column of Arrays of Sorted Numbers or nulls

This example requires column data to contain arrays of numbers or JSON null values. The array elements are stored in ascending canonical order, which means that any null values come before any numbers and numbers are sorted in ascending order.^Foot 2

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

Note:

You can see whether a given column has a JSON-type modifier, and if so what kind (OBJECT, ARRAY, or SCALAR), by consulting column JSON_MODIFIER of any of the static dictionary views ALL_TAB_COLUMNS, DBA_TAB_COLUMNS, USER_TAB_COLUMNS, ALL_TAB_COLS, DBA_TAB_COLS, and USER_TAB_COLS. See ALL_TAB_COLUMNS and ALL_TAB_COLS in Oracle Database Reference.

Note:

You can constrain the JSON data in a JSON-type column to be much more specific than what JSON-type modifiers allow (for example an object with certain fields of certain types) by applying a JSON-Schema VALIDATE check constraint to the column. See Validating JSON Data with a JSON Schema.

4.3 Determining Whether a Column Must Contain Only JSON Data

How can you tell whether a given column of a table or view can contain only well-formed JSON data? Whenever this is the case, the column is listed in the following static data dictionary views: DBA_JSON_COLUMNS, USER_JSON_COLUMNS, and ALL_JSON_COLUMNS.

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.

Note:

If a check constraint combines condition is json with another condition using logical condition OR, then the column is not listed in the views. In this case, it is not certain that data in the column is JSON data. For example, the constraint jcol is json OR length(jcol) < 1000 does not ensure that column jcol contains only JSON data.