1 Overview of Oracle Database API for MongoDB

Oracle Database API for MongoDB lets applications interact with collections of JSON documents in Oracle Database using MongoDB commands.

Oracle Database API for MongoDB is provided as part of Oracle Autonomous Database. You can enable it there using the Oracle Cloud Infrastructure Console. See Using the Oracle Database API for MongoDB in Using Oracle Autonomous Database Serverless or Use MongoDB API with Oracle Autonomous Database on Dedicated Exadata Infrastructure for information about using the MongoDB API with an Autonomous Database (including an Autonomous JSON Database). This information covers configuring the database for use with the API, including for security and connection.

If you have release 22.3 or later of Oracle REST Data Services (ORDS), then you can use the MongoDB API with any Oracle database, release 21c or later, as well as with any Oracle Autonomous Database, release 19c (serverless, dedicated, and cloud@customer). See Oracle API for MongoDB Support in Oracle REST Data Services Installation and Configuration Guide for information about enabling the API.

Oracle Database API for MongoDB is compatible with MongoDB version 4.0 and higher. This compatibility means that most applications, drivers, and tools that you use today with MongoDB databases can be used with Oracle Database API for MongoDB with little or no change.

See Also:

1.1 Purpose of Oracle Database API for MongoDB

Oracle Database understands Mongo-speak. That's the purpose of Oracle Database API for MongoDB.

You have one or more applications that interact with a MongoDB NoSQL database, and you want to migrate the data to Oracle Database. Or you have relatively simple collections of JSON documents and you prefer not to learn and use SQL (Structured Query Language). Or you're used to and prefer to use MongoDB commands, particularly for the business logic of your applications (query by example) but also for data definition (creating collections and indexes), data manipulation (CRUD operations), and some database administration (status information). You appreciate the flexibility of a JSON document store: no fixed data schemas, easy to use document-centric APIs.

If you have applications that use MongoDB, you'd like to make them more robust by providing advanced security; fully ACID transactions (atomicity, consistency, isolation, durability); standardized JOINs with all sorts of data; and analytics, machine-learning, and reporting capabilities.

Oracle Database API for MongoDB, or Mongo API for short, provides such advantages to developers who speak MongoDB. It translates the MongoDB wire protocol into SQL statements that are executed by Oracle Database. You can continue to use the drivers, frameworks, and tools you're used to, to develop your JSON document-store applications.

Oracle Database is a converged database. It's multi-model and polyglot — seemingly different kinds of databases rolled into one, providing synergy across very different features, supporting different workloads and data models.

Oracle Database is also multitenant, which means you can have both consolidation and isolation, for different teams and purposes. And it provides a single, common approach for security, upgrades, patching, and maintenance. But if you use an Autonomous Oracle Database, such as Autonomous JSON Database, then Oracle takes care of all such database administration responsibilities. And there's Always Free access to an autonomous database.

The standard, declarative language SQL (Structured Query Language) underlies processing on Oracle Database. You might develop applications using Mongo-speak or Simple Oracle Document Access (SODA) with a popular application development language, but SQL is behind it all, and it enables your app to play well with everything else on Oracle Database.

Parent topic: Overview of Oracle Database API for MongoDB

1.2 Tools and Drivers for Oracle Database API for MongoDB

Oracle Database API for MongoDB supports a variety of MongoDB tools and drivers.

Oracle recommends that you use the following tool and driver versions, or higher, with support for load-balanced connections.

  • C 1.19.0

  • C# 2.13.0

  • Compass 1.28.1

  • Database Tools 100.5.0 (includes mongoexport, mongorestore, and mongodump)

  • Go 1.6.0

  • Java 4.3.0

  • MongoSH 0.15.6

  • Node.js driver 4.1.0

  • PyMongo 3.12.0 (for Python language)

  • Ruby 2.16.0

  • Rust 2.1.0

You can download these drivers from https://www.mongodb.com/docs/drivers/.

Note:

Examples in this documentation of input to, and output from, Oracle Database API for MongoDB use the syntax of shell mongosh.

Parent topic: Overview of Oracle Database API for MongoDB

1.3 Terms and Concepts: MongoDB and Oracle Database

Some application-user terms and concepts used by MongoDB are presented, together with description of their relation to Oracle Database..

Some of the same terms are also used in Oracle Database API for MongoDB. In general, application developers need not be concerned with the Oracle Database concepts and technologies that underlie such terms.

Table 1-1 Application-User Terms

Term Description
Database

A set of collections.

On Oracle Database this corresponds to a database schema.

Because of this possible confusion over use of the word database, in this documentation that word is used for Oracle Database, and the term schema, or database schema, is used for what MongoDB calls a "database".

User

For log-in purposes, a user of Oracle Database API for MongoDB is an Oracle Database user, which is also called a database schema (see previous).

To use the collections in a given schema ("database") , you log in with the Oracle Database API for MongoDB using the MongoDB PLAIN $external mechanism and providing the credentials for that schema.

A root user, that is, a user who has MongoDB role root, can create additional database schemas. And a root user can use the collections of any schema without needing to log in separately for that schema.

Collection

A collection contains a set of documents.

A collection name is unique for a given database schema: Different collections can have the same name if they are in different schemas.

On Oracle Database, a table or a view underlies a collection. The table name is derived from the collection name and is typically the same. (Exceptions include collection names that use words reserved by Oracle Database.) Typically all documents in a collection are JSON documents.
Document

The basic unit of storage for data in a collection.

On Oracle Database a document corresponds roughly to a row in the table or view that underlies the collection.

A document is typically a JSON document, that is, it contains only JSON data. On Oracle Autonomous Database a document is always a JSON document.

On Oracle Autonomous Database the table column used to store documents is named data.

Primary Key

On Oracle Database a primary key is used to uniquely identify a table or view row.

MongoDB uses a unique _id field in a document to identify the document. On Oracle Database the primary key for a JSON document is stored in a column named id. Its value is automatically set to the value of the document's _id field. See Document Key: Differences and Conversion (Oracle Database Prior to 23ai).

Query Expression

A JSON object that is sent by an application client to the server (Oracle Database), to query documents of a collection.

The object can contain query operator fields, whose names start with $. The operators are interpreted, and their operations are invoked to act on the collection. The server returns the action results to the client.

Query expressions are typically used to query a collection, but they can also be used to project or update data in documents.

Oracle Database API for MongoDB translates query expressions into SQL (Structured Query Language) queries.
Index

Indexes enhance performance when acting on collections (querying, inserting, updating, and deleting documents).

An index name is unique for a given database schema: Different indexes can have the same name if they are in different schemas.

Note:

If Oracle Database parameter compatible is less than 23 then MongoDB commands to create or drop indexes are ignored by Oracle Database API for MongoDB. You must instead create Oracle Database indexes that are relevant for your JSON data.
Pipeline

MongoDB aggregation operations chain multiple operations together, invoking them sequentially as a pipeline.

If Oracle Database parameter compatible is less than 23 then MongoDB aggregation pipelines are not used; Oracle Database API for MongoDB carries out aggregation operations differently. See MongoDB Aggregation Pipeline Support.

Related Topics

See Also:

Parent topic: Overview of Oracle Database API for MongoDB

1.4 Default Naming of a Collection Table

By default, the name of the database table that underlies a document collection is derived from the collection name.

If you want a different table name from that provided by default then use custom collection metadata to explicitly provide the name.

The default table name is derived from the collection name you provide, as follows:

  1. Each ASCII control character and double quotation mark character (") in the collection name is replaced by an underscore character (_).

  2. If all of the following conditions apply, then all letters in the name are converted to uppercase, to provide the table name. In this case, you need not quote the table name in SQL code; otherwise, you must quote it.

    • The letters in the name are either all lowercase or all uppercase.

    • The name begins with an ASCII letter.

    • Each character in the name is alphanumeric ASCII, an underscore (_), a dollar sign ($), or a number sign (#).

      Note:

      Oracle recommends that you do not use dollar-sign characters ($) or number-sign characters (#) in Oracle identifier names.

For example:

  • Collection names "col" and "COL" both result in a table named "COL". When used in SQL, the table name is interpreted case-insensitively, so it need not be enclosed in double quotation marks (").

  • Collection name "myCol" results in a table named "myCol". When used in SQL, the table name is interpreted case-sensitively, so it must be enclosed in double quotation marks (").

Parent topic: Overview of Oracle Database API for MongoDB

1.5 Using the Mongo DB API with JSON-Relational Duality Views

You can use Oracle Database API for MongoDB with documents supported by a JSON-relational duality view. Such documents are automatically generated, based on underlying table data.

JSON-relational duality views are supported only in Oracle Database Release 23ai or later.

A JSON-relational duality view exposes data stored in relational database tables as JSON documents. The documents are materialized on demand, not stored as such. Duality views give data both a conceptual and an operational duality: it's organized both relationally and hierarchically. You can base different duality views on data stored in one or more of the same tables, providing different JSON hierarchies over the same, shared data.

This means that applications can access (create, query, modify) the same data as a collection of JSON documents or as a set of related database tables and columns, and both approaches can be employed at the same time.

You can manipulate the documents realized by duality views in the ways you're used to, using your usual drivers, frameworks, tools, and development methods. In particular, applications can use any programming languages.

An application uses a document collection that's supported by a duality view as if the documents were stored in a table column of JSON data type. You use the duality-view name as collection-name argument in MongoDB API calls.

Note:

If the duality view name wasn't quoted when the view was created then be sure to pass the name as uppercase in MongoDB API calls. For example if my_dv was used when the view was created then pass "MY_DV" as the collection name. If "my_dv" was used when the view was created then pass "my_dv".

As one important use case, a MongoDB API application can easily make use of any existing database data — just create one or more duality views over that data, to support JSON collections.

An important aspect of the JSON-relational duality is that it lets different kinds of JSON document share common data (as well as share the same data in relational tables). How you define a duality view determines what data gets shared, and how (who can perform what kinds of updating operations on which document parts).

Creating JSON Duality Views for Use With the MongoDB API

You cannot create a JSON-relational view using the MongoDB API. You can use SQL statement CREATE JSON RELATIONAL DUALITY VIEW to do that.

All duality views are compatible with the MongoDB API. They always have field _id as their document identifier. The value of field _id specifies the document fields whose values are the primary-key columns of the root table that underlies the duality view.

  • If there is only one primary-key column, then you use that column as the value of field _id when you define the duality view. For example: _id : race_id, as in Example 1-1.

  • If there are multiple primary-key columns, then you use an object as the value of field _id when you define the view. The members of the object specify document fields whose values are the primary-key columns. For example, suppose you have a car-racing duality view with two primary-key columns, race_id and race_year, which together uniquely identify a root-table row, but neither of which does so alone. This _id field in the duality view definition maps document fields raceId and year to primary-key columns race_id and race_year, respectively: 

    _id : {raceId : race_id, year : race_year}

    If there is only one primary-key column, you can nevertheless use an object value for _id, if you like. Doing so lets you provide a meaningful field name. For example, here the single primary-key column, race_id, provides the value of field raceId as well as the value of field _id: 

    _id : {raceId : race_id}

The value(s) provided by field _id for the primary key column(s) it maps to must of course be insertable into those columns, which means that their data types must be compatible with the column types. For example, if field _id maps to a single primary-key column that is of SQL type NUMBER, then the _id value of a document you insert must be numeric. Otherwise, an error is raised for the insertion attempt.

If you don't explicitly include an _id field in a document that you insert, then it is added automatically, with an ObjectId value. (You can also explicitly use an ObjectId value in an _id field.) An ObjectId value can only be used for a field that the duality view maps to a column of SQL type RAW.

Example 1-1 Creating JSON Duality View RACE_DV Using GraphQL

This example creates a duality view, race_dv, that supports car-racing race documents. 

CREATE JSON RELATIONAL DUALITY VIEW race_dv AS
  race @insert @update @delete
    {_id : race_id
     name   : name
     laps   : laps @noupdate
     date   : race_date
     podium : podium @nocheck
     result : driver_race_map @insert @update @delete
       [ {driverRaceMapId : driver_race_map_id
          position        : position
          driver @noinsert @update @nodelete
                 @unnest {driverId : driver_id,
                          name     : name}} ]};

This definition is the same as the one in Creating Duality View RACE_DV Using GraphQL in JSON-Relational Duality Developer's Guide. See that documentation for similar duality view creations for driver and race documents. The SQL code in this example embeds Oracle GraphQL code. Alternatively you can use only SQL code for the definition, as in Creating Duality View RACE_DV, With Unnested Driver Information Using SQL.

This duality view supports JSON documents where the race objects look like this — they contain a result field whose value is an array of objects that specify the drivers and their resulting positions in the given race: 

{"_id" : 201,
"name" : "Bahrain Grand Prix",
"laps" : 57,
"date" : "2022-03-20T00:00:00",
"podium" : {...},
"result" : [ {"driverRaceMapId" : 3,
              "position" : 1,
              "driverId" : 103,
              "name" : "Charles Leclerc"},... ]}

The value of document identifier field _id is taken from the single primary-key column, race_id of the root table, race. For example, the document identified by the _id field whose value is 201 is generated from the row of data that has 201 in primary-key column race_id of the root table (race) underlying the duality view.

Generation of the documents supported by the view automatically joins data from columns driver_race_map_id, position and driver_id from table driver_race_map, and column name from table driver.

The annotations (GraphQL directives) @insert, @update, and @delete are used to specify that applications can insert, update, and delete documents supported by the view, respectively, but that they can only perform update operations on the driver field of the documents (a driver cannot be inserted or deleted when you modify a race document) and you cannot update the laps field (you cannot change the number of laps when you update a race document).

The @nocheck annotation applied to column podium specifies that updating field podium in a race document does not contribute to checking the state/version of the document (its ETAG value).

See Also:

Parent topic: Overview of Oracle Database API for MongoDB