Differences Between Pre-26ai and 26ai Collections

Collections backed by 26ai JSON tables and views have a fixed structure with no separately stored metadata. They are first-class database objects: you can create, drop, and write to them via SODA APIs or directly with SQL.

Pre-26ai collections are not first-class database objects and can only be created, dropped, and written to through SODA APIs - not SQL. There are two reasons for this:

Separate metadata. Pre-26ai collections store metadata in the data dictionary, separately from the collection table or view. This prevents direct SQL-based create/drop operations (DBMS_SODA PL/SQL API does support these operations, as it is a full SODA implementation including collection create/drop methods).
SODA-managed columns. These collections include key, version, and timestamp (creation/last-modified) columns that are highly configurable via metadata and fully managed by SODA. By contrast, 26ai collections have only hidden key and version columns. These columns are not configurable, and are maintained automatically by the database itself.

Differences between the two collection types are summarized in the following table.

Feature	Collections based on JSON collection tables and views	Pre-26ai collections
Creating	Collections can be created using SQL or SODA APIs.	Collections can only be created from SODA APIs.
Naming	The collection name is the name of the corresponding table or view. The same name is used from SQL and SODA. If the collection name uses mixed case, an uppercase synonym is created automatically. If a collection is created from SQL using a mixed-case name without double quotes, a synonym for that mixed-case name is created automatically.	The collection name can differ from the corresponding table or view name. By default, the table or view name is derived from the collection name, but it can be overridden by specifying `tableName` in the collection metadata. SODA clients use the collection name; SQL access uses the corresponding table or view name. See Default Naming of a Pre-26ai Collection Table.
Primary key	The key is the `_id` field within the document. It can be specified on insert; if it is not present, the database generates a unique value. The hidden `RESID` column contains an encoded form of the `_id` value in the content.	Collection metadata designates a table column as the primary-key column. Depending on the metadata, the value can be automatically generated or specified by the client.
Columns	The collection always has a single visible column named `DATA` that contains the JSON documents, along with hidden columns `RESID` and `ETAG`. Virtual columns can be added to facilitate partitioning and sharding. JSON collection tables and views do not have `LAST_MODIFIED` or `CREATED_ON` columns, so SODA getters for these values return `null`.	By default, the document-content column is `JSON_DOCUMENT`, the ID column is `ID`, and the version column is `VERSION`. These are analogous to `DATA`, `RESID`, and `ETAG`. By default, there are also `LAST_MODIFIED` and `CREATED_ON` columns. Column names and SQL types can vary depending on the collection definition.
DML (updates, inserts, and deletes)	Documents can be inserted and updated from SQL or SODA APIs. When a document is inserted or updated from SQL, the database automatically maintains the `_id` field, `RESID`, and `ETAG`.	Documents can only be inserted and updated from SODA APIs.
Dropping	Collections can be dropped from SQL using `DROP TABLE` or `DROP VIEW`, or from SODA drop methods or corresponding SODA REST commands.	Collections can only be dropped from SODA APIs.
Mapping tables and views as collections	Not supported, because the table or view is immediately usable from SODA APIs without an additional mapping step.	Supported.
Storing non-JSON documents	Not supported.	Supported.
Metadata	Collection metadata is synthesized dynamically from the data dictionary instead of being stored by SODA. It includes `"native": true` to identify a collection based on a JSON collection table or view. Configuration options are limited to requesting `_etag` in the content and adding virtual columns for partitioning and sharding.	Metadata is stored by SODA and can specify many configuration options.
Views	Collections appear in `_JSON_TABLE_COLLECTIONS`, `_JSON_VIEW_COLLECTIONS`, and `_JSON_COLLECTIONS`. They also appear in `_SODA_COLLECTIONS`.	Collections appear in `*_SODA_COLLECTIONS`.

Strict vs lax type matching

Another difference between pre-26ai collections and JSON collection tables and views is how filter comparisons handle data types.

If the content column is of type JSON and the database version is 26ai or later, filters use strict type matching: values are compared by both type and value, strings are not coerced to other types, and non-string scalar values are not matched using their string representation. This applies to 26ai JSON collection tables and views, which store document content in a JSON-typed column.

For example:

Filter {"total" : 1} matches only documents where total is the number 1, not the string "1".
Filter {"total" : "1"} matches only documents where total is the string "1", not the number 1.

By contrast, pre-26ai collections use lax type matching, where strings can be coerced to other scalar types and non-string scalar values can be compared using their string representation. As a result, values of different types can match if they are considered equivalent after coercion.

For example, under lax type matching:

Filter {"total" : 1} matches documents where total is either the number 1 or the string "1".
Filter {"total" : "1"} matches documents where total is either the string "1" or the number 1.

Pre-26ai collections do not store documents in a JSON-typed column and therefore continue to use lax type matching semantics, even after a database upgrade to 26ai or later.

Although JSON collection tables and views are recommended, pre-26ai collections can still be created on 26ai for backward compatibility by supplying custom metadata at collection creation time. See Pre-26ai Collections After Upgrade for details. Existing pre-26ai collections continue to function unchanged after an upgrade.