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 JSON collection table or a JSON-relational duality view must have a document-identifier field, _id, at the top level, whose value is unique for the collection.^Foot 1 An attempt to insert a document with the same _id value as a document already present in the collection raises an error.

If you insert a document that doesn't have an _id field into a collection table then it's added automatically, with a unique value that's indexed for fast lookup. You must explicitly include the _id field in documents you insert into a duality-view collection.

A non-duality view collection need not have an _id document-identifier field, but if it has one then for it to be used as such you need to ensure that its values are unique across the collection.

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.

An _id need only be unique across a given collection; different collections can have documents whose _id values are the same. One use for this feature 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 (non-duality) JSON collection view doesn't have a document-identifier field then 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 an 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 12-byte RAW OID value it returns corresponds to the JSON-type value for field _id. The OID (object identifier) format is compatible with Oracle Database API for MongoDB. (The UUID format respects the IETF Universally Unique IDentifiers (UUIDs) proposed standard, RFC 9562.)

When you insert a document into a JSON collection table or a duality view, you can obtain the value of its _id field using a SQL INSERT command with a RETURNING clause that returns the value as an instance of type JSON. This is useful when the field is added automatically.

For example, this PL/SQL code sets the value of JSON-type variable id to the value of the automatically provided field _id:

DECLARE id JSON;
BEGIN
  INSERT INTO my_collection mc
    VALUES (JSON('{"a" : 42}'))
    RETURNING mc.data."_id" INTO id;
  DBMS_OUTPUT.put_line(json_serialize(id));
END

This Java code does the same thing — it sets the value of JSON-type variable id to the value of the automatically provided field _id:

String sql = "INSERT INTO my_collection mc " +
             "VALUES ('{\"a\" : 42}') " +
             "RETURNING mc.data.\"_id\" INTO ?";
OraclePreparedStatement ocstmt = (OraclePreparedStatement) conn.prepareStatement(sql);
ocstmt.registerReturnParameter(1, OracleTypes.JSON);
ocstmt.executeUpdate();
rs = ocstmt.getReturnResultSet();
System.out.println("Retrieved _id : " + rs.getObject(1, OracleJsonBinary.class));

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 can be directly updatable^Foot 2. That is, you can directly insert, update, and delete documents (or parts of documents), in addition to 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. (See Creating Duality Views.)

• A non-duality JSON collection view cannot be updated directly, that is, using documents. Using a document API you can only query its documents.

  You create such a collection using CREATE JSON COLLECTION VIEW. (See Example 6-3.)

For both duality and non-duality views you can update the supported documents indirectly, by updating table data underlying the view. The difference is that non-duality views are not directly document-updatable.

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. A non-duality 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.

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

Because it's ultimately "just a table", you can use a JSON collection table in most of the ways that you use a regular table. In particular, you can use GoldenGate to replicate a collection table between databases, including between Oracle Database and JSON document databases such as MongoDB. (You can also use GoldenGate to replicate a duality view.)

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

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: rename it, 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, non-duality 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.

• *_JSON_COLLECTION_VIEWS — Lists all collection views: owner and name.

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

CREATE JSON COLLECTION TABLE j_purchaseorder;

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