6 JSON Collections

JSON collections are database objects that store or otherwise provide a set of JSON documents. Client applications typically use operations provided by document APIs to manipulate collections and their documents. They can also use SQL to do so.

In Oracle Database, a JSON collection is a special table or view that provides JSON documents in a single JSON-type object column named DATA. Each document in a collection table automatically has a document-identifier field, _id, at the top level, whose value is unique for the collection. Documents in a collection view need not have an _id field.^Foot 1

When you insert a document into a collection table, if for some reason it doesn't have an _id document-identifier field then that's added automatically, with a unique value (for the collection) that is indexed for fast lookup. An attempt to insert a document with the same _id value as a document already present raises an error.

Document APIs typically include an _id document-identifier field in documents to be inserted, but a "side-band" insertion into column DATA using SQL might not.

Uniqueness across a collection means that different collections can have documents whose _id values are the same. One use for this is for related collections to refer to related information. For example, a customer_profile collection and a customer_complaints collection can use the same _id value to, in effect, refer to the same customer.

Because a document-identifier value is unique across a collection, document APIs can use it to access documents directly — that's its purpose. If a JSON collection view doesn't have a document-identifier field, document APIs can still read documents in the collection, but only using query-expression find operations.

You can use SQL function json_id to create a value for a document-identifier field that you provide. The value returned by json_id is a identifier of SQL type RAW that is, in effect, globally unique. The required SQL string argument determines the kind of RAW value: with string 'OID', a 12-byte RAW value is returned; with string 'UUID', a 16-byte RAW value is returned.

When field _id is created automatically by Oracle, its value is provided by invoking json_id with argument 'OID'. The OID (object identifier) format is consistent with the OID values generated by MongoDB client drivers. The UUID format respects the IETF Universally Unique IDentifiers (UUIDs) proposed standard, RFC 9562.

Although a JSON collection is really a particular kind of database table or view, document-centric client applications don't bother with tables, views, or columns; they care only about collections and documents, using the operations provided by the Oracle Database document APIs: Oracle Database API for MongoDB and Simple Oracle Document Access (SODA). Each API has its own document and collection operations.

A JSON collection table stores JSON documents. You create such a collection using CREATE JSON COLLECTION TABLE. (See Example 6-2.)

A JSON collection view maps JSON documents to underlying relational data — there are two kinds:

• A JSON-relational duality view is directly updatable, which means you can directly insert, update, and delete documents, as well as querying them. Documents supported by a duality view always have a document-identifier field, _id.

  You create such a collection using CREATE JSON RELATIONAL DUALITY VIEW.

  You can also update the data stored in tables underlying the view, which can indirectly update the documents mapped to that data.

• An ordinary JSON collection view is not directly updatable. Using a document API you can only query its documents.

  You create such a collection using CREATE JSON COLLECTION VIEW.

  You can, however, update the supported documents indirectly, by updating the underlying table data.

For convenience, each time you create a JSON collection (table, view, or duality view) a synonym is automatically created for the collection name you provide. If the name you provide is unquoted then the synonym is the same name, but quoted. If the name you provide is quoted then the synonym is the same name, but unquoted. If the quoted name contains one or more characters that aren't allowed in an unquoted name then no synonym is created. The creation of a synonym means that the name of a collection is, in effect, always case-sensitive regardless of whether it's quoted. See CREATE SYNONYM in Oracle Database SQL Language Reference.

A duality view uses SQL/JSON generation functions to define the mapping between the supported JSON documents and the underlying relational data used to generate them. An ordinary collection view can also use the generation functions, but more generally it can use any SQL query over relational data, as long as it returns a (single) JSON object.

JSON duality views and JSON collection tables are interchangeable when it comes to their JSON data (generated in the case of duality views, stored in the case of collection tables). As the SQL*Plus describe command tells you, each has a single JSON-type object column named DATA, with a top-level, document-identifier field, _id.

See Also:

Overview of JSON-Relational Duality Views in JSON-Relational Duality Developer's Guide

Because a JSON collection has a fixed shape — a single JSON-type object column (DATA) with one JSON document (an object) per row, document APIs can access and manipulate its documents directly, without bothering about tables, columns, or rows.

You can think of CREATE JSON COLLECTION TABLE and CREATE JSON COLLECTION VIEW as macros that simplify an underlying use of CREATE TABLE and CREATE VIEW. CREATE JSON COLLECTION TABLE allows most of the same options as CREATE TABLE (Example 10-1 shows the use of a PARTITION BY RANGE clause, for example).

A JSON collection table has an additional option, ETAG support. If you provide CREATE JSON COLLECTION TABLE with the keywords WITH ETAG then each JSON document contains a document-handling field _metadata, whose value is an object with etag as its only field. This is the same as for a JSON duality view; see Car-Racing Example, Duality Views for more information. (If keywords WITH ETAG are not used then there is no _metadata field.)

The value of field etag is updated each time the document is written, so it can be used to check whether the document has changed since it was last read from the database. You can use this behavior to implement optimistic concurrency; see Using Optimistic Concurrency Control With Duality Views.

When you create a JSON collection table you can also define one or more expression columns for it. These columns are virtual: their values aren't stored in the column; they're calculated as the result of evaluating a SQL query expression whenever the column is accessed. The expression columns are invisible by default.

You can also provide constraints on column DATA or any user-defined expression columns (see Example 6-1). The most important use of expression columns is to partition a collection on JSON field values (see Example 10-1).

Example 6-1 Creating a JSON Collection Table with Virtual Column and Constraint

This example creates JSON collection table employee. In addition to the JSON-type column DATA, the table includes invisible virtual column SALARY, whose value is that of top-level JSON field salary, as a SQL number. The json_value expression extracts the field value, using item-method number() to interpret it as a SQL number.

The value of virtual column SALARY is constrained to be greater than zero, which also constrains the value of field salary of column DATA.

CREATE JSON COLLECTION TABLE employee
  (salary AS (json_value(DATA, '$.salary.number()')),
   CONSTRAINT sal_chk CHECK (salary > 0));

You can use ALTER TABLE to alter a JSON collection table: add/drop constraints, add/drop user-defined virtual columns, add/drop partitioning, and so on.

You can consult various static dictionary views to get information about JSON collection tables and views.

• *_JSON_COLLECTIONS — Lists all collection tables, collection views, and JSON-relational duality views: owner, name, and type (collection table, ordinary collection view, or duality view).

• *_JSON_COLLECTION_TABLES — Lists all collection tables: owner, name,and whether or not the documents contain an ETAG metadata value.

In addition:

• The dictionary views for tables (*_TABLES) and views (*_VIEWS) also list collection tables and collection views.

• The dictionary view for database objects (*_OBJECTS) also lists collection tables and collection views (with column OBJECT_TYPE as TABLE and VIEW, respectively).

See Also:

• JSON Storage Clause in Oracle Database SQL Language Reference for information about CREATE TABLE

• ALL_JSON_COLLECTIONS in Oracle Database Reference

• ALL_JSON_COLLECTION_TABLES in Oracle Database Reference

• ALL_OBJECTS in Oracle Database Reference

• ALL_TABLES in Oracle Database Reference

• ALL_VIEWS in Oracle Database Reference

Example 6-2 Creating a JSON Collection Table

This example creates collection table j_purchaseorder. It has a single, JSON-type object column named DATA.

CREATE JSON COLLECTION TABLE j_purchaseorder;

Contrast this example with Example 4-1, which creates an ordinary table with two relational columns, id and date_loaded, and a JSON-type column, po_document.

Example 6-3 Creating a (Non-Duality) JSON Collection View

This example creates ordinary, read-only collection view empview from relational data in table hr.employees.

CREATE JSON COLLECTION VIEW empview AS
  SELECT JSON {'_id'         : employee_id,
               last_name,             
               'contactInfo' : {email, phone_number},
               hire_date,
               salary}
    FROM hr.employees;

The data is selected from columns employee_id, last_name, email, phone_number, hire_date, and salary. The resulting JSON documents are objects with fields _id, LAST_NAME, contactInfo, HIRE_DATE, and SALARY. The value of field contactInfo is an object with fields EMAIL and PHONE_NUMBER, whose values come from columns email and phone_number.

If CREATE JSON COLLECTION VIEW were replaced by just CREATE VIEW, then the result would also be an ordinary, read-only view with a single DATA column, with the same data. But it wouldn't be a collection view, which means that it couldn't be queried by a document API such as Oracle Database API for MongoDB or Simple Oracle Document Access (SODA).

---

Footnote Legend

Footnote 1: If a JSON collection view has an _id document-identifier field, then for it to be used as such you need to ensure that its values are unique across the collection.