Collections

Documents are organized into collections.

How Collections Are Represented

In non-REST SODA implementations, collections are represented as language-specific objects that provide access to various operations, covered in Operations on Document Collections.

In SODA REST, collection names are specified as part of the operation URL.

At the database level, collections are tables or views.

Creating and Dropping Collections

With Oracle Database 26ai, JSON collection tables and views can be created from either SODA APIs or SQL. The COMPATIBLE database parameter must be set to 23 or higher.

For example, in SODA for Java:

OracleCollection col = db.admin().createCollection("myColl");

The SQL equivalent is:

CREATE JSON COLLECTION TABLE "myColl";

Java syntax is used here as an example - other SODA implementations provide similar create collection operations.

If a collection is created using SQL, it is immediately accessible through SODA APIs. JSON collection tables and views can also be read and written directly using SQL. The same applies to JSON-relational duality views, introduced in 26ai, which are also accessible as SODA collections.

Collections can also be dropped from either SODA APIs or SQL.

Caution: This applies only to 26ai JSON collection tables and views. Do not drop a pre-26ai collection using SQL - doing so leaves dangling metadata. See Pre-26ai Collections for details.

For example, in SODA for Java:

col.admin().drop();

The SQL equivalent (26ai JSON collection tables and views only) is:

DROP TABLE mycoll;

Collection Names and SQL Synonyms

For 26ai JSON collection tables and views, the collection name and the table or view name are the same.

If the collection name uses mixed case, a synonym is created automatically so the collection can be referenced from SQL without quoted identifiers:

• Create a SODA collection named "myColl" → a synonym MYCOLL is created automatically, so you can refer to it in SQL without using "myColl" in double quotes.
• Create a collection from SQL without quotes, e.g. CREATE JSON COLLECTION TABLE myColl → a synonym "myColl" is created automatically, preserving the mixed-case name for use in SODA APIs.

Pre-26ai Collections

Pre-26ai collections differ significantly at the database level. For details, see Pre-26ai Collections.