Databases and Collections

This section explains how MongoDB databases and collections map to schemas and collection tables in Oracle Database.

Databases

A MongoDB database groups related collections. Oracle Database uses the term schema for an analogous construct that stores tables and other dependent objects. The Oracle Database API for MongoDB translates database names used in MongoDB commands to schema names in Oracle Database. Consider the following mongosh example:

use payroll;
db.createCollection('employees');

This script creates the employees collection in the payroll database. When executed against Oracle Database, the Oracle Database API for MongoDB creates the collection employees in the schema payroll.

Unlike Oracle Database, MongoDB will implicitly create a database when it doesn’t exist. In the previous example, if payroll doesn’t exist, it will be created automatically when the collection employees is created. The Oracle Database API for MongoDB will only implicitly create a schema when it doesn’t exist if the connected user is an administrative user. Otherwise, the schema must be created in advance. A schema can be created using the following SQL:

db.aggregate([{$sql:`create user payroll no authentication`}]);

After you create the schema, MongoDB commands that reference the payroll database map to the schema with the same name.

JSON Collections

A JSON collection is a table or view that exposes a single JSON type column named DATA. There are three different types: JSON collection tables, JSON collection views, and JSON duality views. Most MongoDB commands can only interact with JSON collections; other types of tables and database objects will not be visible or accessible. The following example creates two JSON collection tables and a relational table:

db.aggregate([{$sql:`create json collection table "employees"`}]);
db.createCollection("departments");
db.aggregate([{$sql:`create table payroll_records(employee_name varchar2(4000), job varchar2(100))`}]);

show collections;
departments
employees

Running show collections in mongosh displays departments and employees only, and not the relational table payroll_records.

The $sql aggregation stage is the one exception to this restriction. $sql can access any database object, including objects that are not JSON collections. See Aggregation Pipelines for details.

JSON Collection Tables

A JSON collection table stores JSON objects where each object contains an “_id” field that uniquely identifies the object within the table. Inserting duplicate “_id” values raises an error. If you insert an object without an “_id” field, the database generates a unique value.

The MongoDB CREATE command can be used to create a collection. For example:

db.createCollection('employees');

You can create the same collection from SQL by issuing a create json collection table statement:

db.aggregate([{$sql:`create json collection table "employees"`}]);

Refer to JSON Collections for additional information about JSON collection tables.

JSON Collection Views

A JSON collection view is a logical construct that returns a collection of JSON documents without storing data. JSON collection views are read-only; attempts to insert, update, or delete data fail. Define a JSON collection view by using either an aggregation pipeline or SQL.

Defining a view using a MongoDB aggregation pipeline

Define a JSON collection view by providing an aggregation pipeline to the MongoDB CREATE command. The resulting view returns only the documents produced by the pipeline.

// create a collection table with three documents
db.createCollection("employees");

db.employees.insertMany([
  {"name" : "SMITH", "job":"CLERK", "sal":800}, 
  {"name" : "ALLEN", "job":"SALESMAN", "sal":1600}, 
  {"name" : "WARD",  "job":"SALESMAN", "sal":1250}
]);

// create a collection view that only returns sales staff
db.createView("sales_staff", "employees", [{"$match" : {"job" : "SALESMAN"}}]);

db.sales_staff.find({}, {"name" : 1, "_id" : 0});
[ { name: 'ALLEN' }, { name: 'WARD' } ]

Defining a view using SQL

You can also create a view from SQL by using the create json collection view statement. This approach exposes other Oracle Database data as JSON documents. For example:

// create a relational table (non-collection) and insert two rows
db.aggregate([{$sql:`create table employees_base(name varchar2(100), job varchar2(50), sal number)`}]);
db.aggregate([{$sql:`insert into employees_base values('SMITH', 'CLERK', 800), ('ALLEN', 'SALESMAN', 1600)`}]);

// create a json collection view that exposes the relational rows a JSON objects
db.aggregate([{$sql:`
   create json collection view jemployees as 
       select json {'name' : name, 'job' : job, 'sal' : sal } 
       from employees_base
`}]);

// query the json collection view
admin> db.jemployees.find();
[ { name: 'SMITH', job: 'CLERK', sal: 800 }, { name: 'ALLEN', job: 'SALESMAN', sal: 1600 } ]

This example first creates the relational table employees_base with two rows. It then defines the JSON collection view jemployees, which presents these rows as two JSON objects. MongoDB commands such as find can access the resulting collection.

JSON Duality Views

The third type of JSON collection is called a JSON duality view. JSON duality views are like JSON collection views except that they support both reads and writes. That is, JSON duality view collections support inserts, updates, and deletes that will be automatically propagated to the underlying base tables used to define the view. JSON Duality views are described in section JSON Duality Views.