2 Develop Applications with Oracle Database API for MongoDB
Considerations when developing or migrating applications — a combination of (1) how-to information and (2) descriptions of differences and possible adjustments.
- Indexing and Performance Tuning
Oracle Database offers multiple technologies to accelerate queries over JSON data, including indexes, materialized views, in-memory column storage, and Exadata storage-cell pushdown. Which performance-tuning approaches you take depend on the needs of your application. - Users, Authentication, and Authorization
Oracle Database security differs significantly from that of MongoDB. The security model of Oracle Database API for MongoDB is described: the creation of users, their authentication, and their authorization to perform different operations. - Migrate Application Data from MongoDB to Oracle Database
Some ways to export your JSON data from MongoDB and then import it into Oracle Database are described. Migration considerations are presented. - MongoDB Aggregation Pipeline Support
Oracle Database API for MongoDB supports MongoDB aggregation pipelines, that is, MongoDB commandaggregate
. It lets you use pipeline code to execute a query as a sequence of operations. You can also use SQL as a declarative alternative to this procedural approach. - MongoDB Documents and Oracle Database
Presented here is the relationship between a JSON document used by MongoDB and the same content as a JSON document stored in, and used by, Oracle Database. - Other Differences Between MongoDB and Oracle Database
Various differences between MongoDB and Oracle Database are described. These differences are generally not covered in other topics. Consider these differences when you migrate an application to Oracle Database or you develop a new application for Oracle Database that uses MongoDB commands. - Accessing Collections Owned By Other Users (Database Schemas)
You can directly access a MongoDB API collection owned by another user (database schema) if you log into that schema. You can indirectly access a collection owned by another user, without logging into that schema, if that collection has been mapped to a collection in your schema.
2.1 Indexing and Performance Tuning
Oracle Database offers multiple technologies to accelerate queries over JSON data, including indexes, materialized views, in-memory column storage, and Exadata storage-cell pushdown. Which performance-tuning approaches you take depend on the needs of your application.
If your Oracle Database compatible
parameter is
23
or greater, then you can use MongoDB index
operations createIndex
and dropIndex
to
automatically create and drop the relevant Oracle indexes. If parameter
compatible
parameter is less than
23
, then such MongoDB index operations are not
supported; they are ignored.
Regardless of your database release you can create whatever Oracle Database indexes you need directly, using (1) the JSON Page of Using Oracle Database Actions (see Creating Indexes for JSON Collections), (2) Simple Oracle Document Access (SODA), or (3) SQL — see Indexes for JSON Data in Oracle Database JSON Developer’s Guide. Using the JSON page is perhaps the easiest approach to indexing JSON data.
Note:
MongoDB allows different collections in the same "database" to have indexes of the same name. This is not allowed in Oracle Database — the name of an index must be unique across all collections of a given database schema ("database").
Consider, for example, indexing a collection, named
orders
, of purchase-order documents such as this
one:
{ "PONumber" : 1600,
"User" : "ABULL",
"LineItems" : [{ "Part" : { "Description" : "One Magic Christmas",
"UnitPrice" : 19.95,
"UPCCode" : 13131092899 },
"Quantity" : 9.0 },
{ "Part" : { "Description" : "Lethal Weapon",
"UnitPrice" : 19.95,
"UPCCode" : 85391628927
},
"Quantity" : 5.0 } ]}
Two important use cases are (1) indexing a singleton scalar field, that is, a
field that occurs only once in a document (2) indexing a scalar field in
objects within the elements of an array. Indexing the value of field
PONumber
is an example of the first case. Indexing
the value of field UPCCode
is an example of the second
case.
Example 2-1, Example 2-2, and Example 2-3 illustrate the first case. Example 2-5 illustrates the second case.
You can also index GeoJSON (spatial) data, using a function-based SQL index that
returns SDO_GEOMETRY
data. And for all JSON data you can
create a JSON search index, and then perform full-text queries using
SQL/JSON condition json_textcontains
.
Example 2-1 Indexing a Singleton Scalar Field Using the JSON Page of Database Actions
To create an index for field PONumber
using the JSON
Page, do the following.
-
Right-click the collection name (
orders
) and select Indexes from the popup menu.
Description of the illustration json_page_create_index_001.png -
In the New Index page:
-
Type
*
in the Properties search box.This populates the Properties list with paths to all scalar fields in your collection. These paths are provided by sampling the collection data using a JSON data guide — see JSON_DATAGUIDE in Oracle Database SQL Language Reference.
If you turn on option Advanced, by pushing its slider to the right, then the types of the listed scalar fields are also shown. The types shown are those picked up by sampling the collection. But you can change the type of a field for indexing purposes.
-
Select the paths of the fields to be indexed. In this case we want only a single scalar field indexed,
PONumber
, so select that.Note: This dialog box lets you select multiple paths. If you select more than one path then a composite index is created for the data at those paths.Foot 1 But if you want to index two different fields separately then create two indexes, not one composite index (which indexes both fields together).
The index data type is determined automatically by the types of the data at the selected paths, but you can control this by turning on Automatic and changing the data types. For example, JSON numbers in the collection data for a given field cause a type of
number
to be listed, but you can edit this toVARCHAR2
to force indexing as a string value.
The values of field
PONumber
are unique — the same numeric value is not used for the field more than once in the collection, so select Unique index.Select Index Nulls also. This is needed for queries that use
ORDER BY
to sort the results. It causes every document to have an entry in the index.The values in field
PONumber
are JSON numbers, which means the index can be used for numerical comparison.
Description of the illustration json_page_create_index_002.png -
Example 2-2 Indexing a Singleton Scalar Field Using SODA
Each SODA implementation (programming language or framework) that
supports indexing provides a way to create an index. They all use a
SODA index specification to define the index to be created.
For example, with SODA for REST you use an HTTP POST request,
passing URI argument action=index
, and providing
the index specification in the POST body.
This is a SODA index specification for a unique index named
poNumIdx
on field
PONumber
:
{ "name" : "poNumIdx",
"unique" : true,
"fields" : [ { "path" : "PONumber",
"dataType" : "NUMBER",
"order" : "ASC" } ] }
Example 2-3 Indexing a Singleton Scalar Field Using SQL
You can use Database Actions to create an index for field
PONumber
in column data
of
tableorders
with this SQL code. This uses
SQL/JSON function json_value
to extract values of
field PONumber
.
The code uses ERROR ON ERROR
handling, to
raise an error if a document has no PONumber
field
or it has more than one.
Item method numberOnly()
is used in the path
expression that identifies the field to index, to ensure that the
field value is numeric.
Method numberOnly()
is used instead of
method number()
, because number()
allows also for conversion of non-numeric fields to numbers. For
example, number()
converts a
PONumber
string value of
"42"
to the number 42
.
Other such "only" item methods, which similarly provide
strict type checking, include stringOnly()
,
dateTimeOnly()
, and
binaryOnly()
, for strings, dates, and
binary values, respectively.
CREATE UNIQUE INDEX "poNumIdx" ON orders
(json_value(data, '$.PONumber.numberOnly()' ERROR ON ERROR))
See Also:
SQL/JSON Path Expression Item Methods in Oracle Database JSON Developer’s Guide
Example 2-4 Creating a Multivalue Index For Fields Within Elements of an Array
Starting with Oracle Database 21c you can create a multivalue index for the values of fields that can occur multiple times in a document because they are contained in objects within an array (objects as elements or at lower levels within elements).
This example creates a multivalue index on collection
orders
for values of field
UPCCode
. It example uses item method
numberOnly()
, so it applies only to numeric
UPCCode
fields.
CREATE MULTIVALUE INDEX mvi_UPCCode ON orders o
(o.data.LineItems.Part.UPCCode.numberOnly());
See Also:
Creating Multivalue Function-Based Indexes for JSON_EXISTS in Oracle Database JSON Developer’s Guide
Example 2-5 Creating a Materialized View And an Index For Fields Within Elements of an Array
Prior to Oracle Database 21c you cannot create a multivalue
index for fields such as UPCCode
, which can occur
multiple times in a document because they are contained in objects
within an array (objects as elements or at lower levels within
elements).
You can instead, as in this example, create a materialized view that extracts the data you want to index, and then create a function-based index on that view data.
This example creates materialized view
mv_UPCCode
with column
upccode
, which is a projection of field
UPCCode
from within the
Part
object in array
LineItems
of column data
of table orders
. It then creates index
mv_UPCCode_idx
on column
upccode
of the materialized view
(mv_UPCCode
).
CREATE MATERIALIZED VIEW mv_UPCCode
BUILD IMMEDIATE
REFRESH FAST ON STATEMENT WITH PRIMARY KEY
AS SELECT o.id, jt.upccode
FROM orders o,
json_table(data, '$.LineItems[*]'
ERROR ON ERROR NULL ON EMPTY
COLUMNS (upccode NUMBER PATH '$.Part.UPCCode')) jt;
CREATE INDEX mv_UPCCode_idx ON mv_UPCCode(upccode);
The query optimizer is responsible for finding the most efficient method for a SQL statement to access requested data. In particular, it determines whether to use an index that applies to the queried data, and which index to use if more than one is relevant. In most cases the best guideline is to rely on the optimizer.
In some cases, however, you might prefer to specify that a particular index be picked up for a given query. You can do this with a MongoDB hint that names the index. (Oracle does not support the use of MongoDB index specifications — just provide the index name.)
For example, this query uses index poNumIdx
on
collection orders
, created in Example 2-1.
db.orders.find({"PONumber":1600}).hint("poNumIdx")
Alternatively, you can specify an index to use by passing an Oracle
SQL hint, using query-by-example (QBE) operator
$native
, which is an Oracle extension to the
MongoDB hint syntax.
The argument for $native
has the same syntax as a
SQL hint string (that is, the actual hint text, without the enclosing SQL
comment syntax /*+...*/
). You can pass any SQL hint
using $native
. In particular, you can turn on
monitoring for the current SQL statement using hint
MONITOR
. This code does that for a
find()
query:
db.orders.find().hint({"$native":"MONITOR"})
Related Topics
See Also:
-
The JSON Page in Using Oracle Database Actions
-
Overview of SODA Indexing in Oracle Database Introduction to Simple Oracle Document Access (SODA)
-
Creating Multivalue Function-Based Indexes for JSON_EXISTS in Oracle Database JSON Developer’s Guide
-
Performance Tuning for JSON in Oracle Database JSON Developer’s Guide for detailed information about improving performance when using JSON data
-
JSON Search Index for Ad Hoc Queries and Full-Text Search in Oracle Database JSON Developer’s Guide for information about JSON search indexes
-
Creating a Spatial Index For Scalar GeoJSON Data in Oracle Database JSON Developer’s Guide
-
Influencing the Optimizer with Hints in Oracle Database SQL Tuning Guide
-
Monitoring Database Operations in Oracle Database SQL Tuning Guide for complete information about monitoring database operations
-
MONITOR and NO_MONITOR Hints in Oracle Database SQL Tuning Guide for information about the syntax and behavior of SQL hints
MONITOR
andNO_MONITOR
2.2 Users, Authentication, and Authorization
Oracle Database security differs significantly from that of MongoDB. The security model of Oracle Database API for MongoDB is described: the creation of users, their authentication, and their authorization to perform different operations.
By default, MongoDB does not enable user authentication and authorization checks. Oracle Database always requires authentication, and it always verifies that a connected user is authorized to perform a requested operation. A valid username and password must be provided for authentication.
Oracle Database API for MongoDB supports only the following connection-option values for authentication:
-
PLAIN
value (plain-text authentication) for optionauthMechanism
. In particular, theSCRAM-SHA-*
authentication methods are not supported. $external
value for optionauthSource
. (This is anyway required for MongoDB whenever the authentication method isPLAIN
.)
Oracle Database API for MongoDB relies on Oracle Database users, privileges,
and roles. You cannot add or modify these users and roles using MongoDB clients or
drivers. You can instead do this using SQL or Oracle Database Actions. The minimum Oracle
Database roles required to use the API are CONNECT
,
RESOURCE
, and SODA_APP
.
A user (database schema) also needs to be enabled for use with Oracle REST
Data Services (ORDS). This can be done by invoking PL/SQL procedure
ORDS.enable_schema
or using Oracle Database Actions.
For MongoDB, a "database" is a set of collections. For Oracle Database API for MongoDB, this corresponds to an Oracle Database schema.
Note:
Using Oracle API for MongoDB to drop a "database" does not drop the underlying database schema. Instead, it drops all collections within the schema.
An administrative user can drop a schema using SQL (for example, using Database Actions with an Autonomous Oracle Database).
For the API, a username must be a database schema name. The name is case-insensitive, it cannot start with a nonalphabetic character (including a numeral), and it must be provided with a secure password.
Normally, a user of the API can only perform operations within its schema (the username is the schema name). Examples of such operations include creating new collections, reading and writing documents, and creating indexes.
When an administrative user tries to insert data into a database schema
(user) that does not exist, that schema is created automatically as a schema-only
account, which means that it does not have a password and it cannot be logged into. The
new schema is granted these privileges: SODA_APP
, CREATE
SESSION
, CREATE TABLE
, CREATE VIEW
,
CREATE SEQUENCE
, CREATE PROCEDURE
, and
CREATE JOB
. The schema is also given an unlimited tablespace quota,
and is enabled for using Oracle REST Data Services (ORDS).
For an ordinary user of the API, a MongoDB shell command (such as
use <database>
) that switches from the current MongoDB
database to another one is typically not supported — switching to another database
schema raises an error.
However, an administrative user, which is one that
has all of the following privileges, can create new users (database schemas), and can
access any schema as any user: CREATE USER
, ALTER
USER
, DROP USER
.
An administrative user can do the following:
-
Use the schemas of other users.
Access to other schemas than that of the current user makes use of a proxied connection. For example, someone connected as an administrative user can perform operations in schema
other_user
using the same roles and privileges as if connected directly asother_user
. -
Create new users (schemas).
For example, if an administrative user tries to create a collection in a schema
toto
that does not already exist, that schema (user) is automatically created.
Oracle recommends that you do not allow production applications to make use of an administrative user. Applications should instead connect as ordinary users, with a minimum of privileges. In particular, connect an application to the database using a MongoClient that is specific to a particular schema (user).
Related Topics
See Also:
-
Create Users on Autonomous Database in Using Oracle Autonomous Database Serverless
-
Manage User Roles and Privileges on Autonomous Database in Using Oracle Autonomous Database Serverless
-
CREATE USER in Oracle Database SQL Language Reference for information about using SQL to create database schemas (also called database users)
-
GRANT in Oracle Database SQL Language Reference for information about using SQL to grant roles to database schemas
-
Using the Oracle Database API for MongoDB in Using Oracle Autonomous Database Serverless for information about using an Autonomous Database (including an Autonomous JSON Database) with Oracle Database API for MongoDB. This covers configuring the database for use with the API, including for security and connection.
-
ORDS.ENABLE_SCHEMA in Oracle REST Data Services Developer's Guide for information about enabling a database schema for ORDS
2.3 Migrate Application Data from MongoDB to Oracle Database
Some ways to export your JSON data from MongoDB and then import it into Oracle Database are described. Migration considerations are presented.
You can migrate your application data in any of these ways:
-
Use the MongoDB command-line tools
mongoexport
andmongoimport
.mongoexport
exports data from a MongoDB instance to your file system, andmongoimport
imports the exported data from your file system to Oracle Database. Provide your database connection information when usingmongoimport
. Example 2-6 illustrates this. -
Use a MongoDB tool such as Compass to import data into Oracle Database after connecting that tool to the database. Select the name of your JSON collection, then select ADD DATA.
This displays a popup dialog box where you browse to and import the JSON file containing your collection data. See MongoDB Compass.
Description of the illustration mongodb_compass.png -
After exporting JSON data to your file system, import it to the Oracle Cloud Object Store, then load it from there into a collection using PL/SQL procedure
DBMS_CLOUD.copy_collection
. Example 2-7 illustrates this.This processes the data in parallel, so it is typically faster than
mongoimport
. -
Write a program that reads JSON documents from a connection to MongoDB and writes them to a connection to Oracle Database.
Example 2-6 Migrate JSON Data to Oracle Database Using mongoexport and mongoimport
This example exports collection sales
from
MongoDB to file-system file sales.json
. It then
imports the data from that file to Oracle Database as collection
sales
. The user is connected to host
<host>
as database schema
<user>
with password
<password>
.
mongoexport --collection=sales --out sales.json
mongoimport 'mongodb://<user>:<password>@<host>:27017/<user>?authMechanism=PLAIN&authSource=$external&ssl=true' --collection=sales --file=sales.json
Note:
Use URI percent-encoding to replace any reserved characters in your connection-string URI — in particular, characters in your username and password. These are the reserved characters and their percent encodings:
! |
# |
$
|
% |
& |
'
|
( |
)
|
* |
+ |
---|---|---|---|---|---|---|---|---|---|
%21 |
%23 |
%24 |
%25 |
%26 |
%27 |
%28 |
%29 |
%2A |
%2B |
, |
/ |
:
|
; |
= |
?
|
@
|
[ |
] |
---|---|---|---|---|---|---|---|---|
%2C |
%2F |
%3A |
%3B |
%3D |
%3F |
%40 |
%5B |
%5D |
For example, if your username is RUTH
and your password is
@least1/2#?
then your MongoDB connection string to server
<server>
might look like this:
'mongodb://RUTH:%40least1%2F2%23%3F@<server>:27017/ruth/ ...'
Depending on the tools or drivers you use, you might be able to provide a username and password as separate parameters, instead of as part of a URI connection string. In that case you likely won't need to encode any reserved characters they contain.
See also:
See Also:
Using the Oracle Database API for MongoDB in Using Oracle Autonomous Database Serverless for information about using an Autonomous Database (including an Autonomous JSON Database) with Oracle Database API for MongoDB. This covers configuring the database for use with the API, including for security and connection.
Example 2-7 Loading JSON Data Into a Collection Using DBMS_CLOUD.COPY_COLLECTION
This example loads data from the Oracle Cloud Object Store into a new
collection, newCollection
, using PL/SQL procedure
DBMS_CLOUD.copy_collection
. It assumes that
the data was exported from MongoDB to your file system and then
imported from there to the object-store location that's passed as
the value of parameter file_uri_list
.
The value passed as copy_collection
parameter
FORMAT
is a JSON object with fields
recorddelimiter
and
type
:
-
Field
recorddelimiter
specifies that records in the input data are separated by newline characters. A JSON document is created for each record, that is, for each line in the newline-delimited input data. -
Field
type
specifies that the input JSON data can contain EJSON extended objects, and that these should be interpreted.
See DBMS_CLOUD Package Format
Options in Using Oracle Autonomous Database Serverless for information about
parameter FORMAT
.
BEGIN
DBMS_CLOUD.copy_collection(
collection_name => 'newCollection',
file_uri_list => 'https://objectstorage.../data.json',
format => json_object(
'recorddelimiter' : '''\n''',
'type' : 'ejson'));
END;
/
Related Topics
See Also:
-
Load an Array of JSON Documents into a Collection in Using Oracle Autonomous JSON Database for information about using PL/SQL procedure
DBMS_CLOUD.COPY_COLLECTION
2.4 MongoDB Aggregation Pipeline Support
Oracle Database API for MongoDB supports MongoDB aggregation pipelines, that
is, MongoDB command aggregate
. It lets you use pipeline code to execute a
query as a sequence of operations. You can also use SQL as a declarative alternative to this
procedural approach.
MongoDB's aggregation pipeline is essentially a weak emulation of SQL capabilities. With MongoDB you express operations such as sorting, grouping, and ordering as separate steps in a pipeline. This approach is procedural: you specify how to execute a query as a sequence of operations.
SQL on the other hand is declarative. You specify the query result you want, and the optimizer picks an optimal execution plan based on available indexes, data statistics, cost estimate, and so on. In other words, you specify what you want done, and the optimizer, not you, determines how it should be done.
Oracle Database SQL support of JSON data includes operating on documents and collections, as well as joining JSON and non-JSON data (relational, spatial, graph, …). As a user of Oracle Database API for MongoDB you can apply SQL directly to JSON data without worrying about manually specifying and sequencing any specific operations.
But if you do use MongoDB aggregation pipeline code then the MongoDB API automatically translates the pipeline stages and operations into equivalent SQL code, and the optimizer picks the best execution plan possible. The API supports a subset of the MongoDB aggregation pipeline stages and operations — see Aggregation Pipeline Operators for details.
Unlike MongoDB, Oracle Database does not limit the size of the data to be sorted, joined, or grouped. You can use it for reporting or analytical work that spans millions of documents across any number of collections.
You can use Oracle Database simplified dot notation for JSON data, or
standard SQL/JSON functions json_value
, json_query
,
and json_table
, to extract values from your JSON data for reporting or
analytic purposes. You can convert relational and other kinds of data (including spatial
and graph data) to JSON data using the SQL/JSON generation functions. You can join JSON
data from multiple tables and collections with a single SQL FROM
clause.
A MongoDB aggregation pipeline performs operations on JSON documents from one or
more collections. It's composed of successive stages, each of which performs
document operations and passes the resulting documents to the next stage for further
processing. The operations for any stage can filter the documents passed from the
previous stage, transform (update) them, or even create new documents, for
the next stage. Transformation can involve the use of aggregate operators, also called
accumulators, such as $avg
(average), which can combine field values
from multiple documents.
Each stage in a pipeline is represented by an aggregation expression, which is a JSON value. See the MongoDB Aggregation Pipeline documentation for more background.
You can use declarative SQL code to accomplish what you would otherwise use an
aggregation pipeline for. This is particularly relevant if your Oracle Database
parameter compatible
is less than 23
, in which case
most MongoDB aggregation pipelines are not supported. Example 2-8 illustrates this.
Example 2-8 Using SQL Code Instead of MongoDB Aggregation Pipeline Code
This example calculates average revenues by zip code. It first shows a MongoDB aggregation pipeline expression to do this; then it shows equivalent SQL code.
MongoDB aggregation pipeline:
This code tells MongoDB how to calculate the result; it specifies the order of execution.
db.sales.aggregate(
[{"$group" : {"_id" : "$address.zip",
"avgRev" : {"$avg" : "$revenue"}}},
{"$sort" : {"avgRev" : -1}}])
SQL:
This code specifies the grouping and order of the output presentation declaratively. It does not specify how the computation is to be carried out, including the order of execution. It simply says that the results are to be grouped by zipcode and presented in descending order of the average revenue figures. The query returns rows of two columns with scalar values for zipcode (a string) and average revenue (a number).
SELECT s.data.address.zip.string(),
avg(s.data.revenue.number())
FROM sales s
GROUP BY s.data.address.zip.string()
ORDER BY 2 DESC;
The following query is similar, but it provides the result as rows of
JSON objects, each with a string field zip
, for the
zipcode, and a numeric field avgRev
, for the average revenue.
SQL/JSON generation function json_object
constructs JSON objects
from the results of evaluating its argument SQL expressions.
SELECT json_object('zip' : s.data.address.zip.string(),
'avgRev' : avg(s.data.revenue.number()))
FROM sales s
GROUP BY s.data.address.zip.string()
ORDER BY avg(s.data.revenue.number()) DESC;
Related Topics
2.5 MongoDB Documents and Oracle Database
Presented here is the relationship between a JSON document used by MongoDB and the same content as a JSON document stored in, and used by, Oracle Database.
Note:
This topic applies to JSON documents that you migrate from MongoDB and store in Oracle Database. It does not apply to JSON documents that are generated/supported by JSON-relational duality views. For information about MongoDB-compatible duality views see Using the Mongo DB API with JSON-Relational Duality Views.
You can migrate an existing application and its data from MongoDB to Oracle Database, or you can develop new applications on Oracle Database, which use the same or similar data as applications on MongoDB. JSON data in both cases is stored in documents.
It's helpful to have a general understanding of the differences between the documents used by MongoDB and those used by Oracle Database. In particular, it helps to understand what happens to a MongoDB document that you import, to make it usable with Oracle Database.
Some of the information here presents details that you can ignore if you read this topic just to get a high-level view. But it's good to be aware of what's involved; you may want to revisit this at some point.
When you import a collection of MongoDB documents, the key and the content of each document are converted to forms appropriate for Oracle Database.
A MongoDB document has a native binary JSON format called BSON. An Oracle Database document has a native binary JSON format called OSON. So one change that's made to your MongoDB document is to translate its binary format from BSON to OSON. This translation applies to both the key and the content of a document
Note:
For Oracle Database API for MongoDB, as for MongoDB itself, a stage receives input, and produces output, in the form of BSON data, that is, binary JSON data in the MongoDB format.
Document Key: Differences and Conversion (Oracle Database Prior to 23ai)
This section applies only to Oracle Database releases prior to 23ai.
For MongoDB, the unique key of a document, which identifies it, is the
value of mandatory field _id
, in the document itself. For
Orace Database releases prior to 23ai, the unique key that identifies a document is
separate from the document; the key is stored in a separate database column from the
column that stores the document. The key column has is named id
,
and it is the primary key column for the table that stores your collection
data.
When you import a collection into Oracle Database prior to 23ai, Oracle
Database API for MongoDB creates id
column values from the values
of field _id
in your MongoDB documents. MongoDB field
_id
can have values of several different data types. The Oracle
Database id
column that corresponds to that field is always of SQL
data type VARCHAR2
(character data; in other words, a string).
The _id
field in your imported documents is untouched
during import or thereafter. Oracle Database doesn't use it — it uses column
id
instead. But it also doesn't change it, so any use your
application might make of that field is still valid. Field _id
in
your documents is never changed; even applications cannot change (delete or update)
it.
If you need to work with your documents using SQL or Simplified Oracle
Document Access (SODA) then you can directly use column id
. You can
easily use that primary-key column to join JSON data with other database data, for
instance. The documents that result from importing from MongoDB are SODA documents
(with native binary OSON data).
Be aware of these considerations that result from the separation of document key from document:
-
Though all documents imported from MongoDB will continue to have their
_id
fields, for Oracle Database prior to 23ai the documents in a JSON collection need not have an_id
field. And because, for Oracle Database prior to 23ai, a document and its key are separate, a document other than one imported from MongoDB could have an_id
field that has no relation whatsoever with the document key. -
Because MongoDB allows
_id
values of different types, and these are all converted to string values (VARCHAR2
), if for some reason your collection has documents with_id
values"123"
(JSON string) and123
(JSON number) then importing the collection will raise a duplicate-key error, because those values would each be translated as the same string value for columnid
.
BSON values of field _id
are converted to
VARCHAR2
column id
values according to Table 2-1. If an _id
field value is any type not listed in the table then
it is replaced by a generated ObjectId
value, which is then
converted to the id
column value.
Table 2-1 Conversion of BSON Field _id Value To Column ID VARCHAR2 Value
_id Field Type | ID Column VARCHAR2 Value |
---|---|
Double |
Canonical numeric format string |
32-bit integer |
Canonical numeric format string |
64-bit integer |
Canonical numeric format string |
Decimal128 |
Canonical numeric format string |
String |
No conversion, including no character escaping |
ObjectId |
Lowercase hexadecimal string |
Binary data (UUID)
|
Lowercase hexadecimal string |
Binary data (non-UUID)
|
Uppercase hexadecimal string |
The canonical numeric format for a VARCHAR2
value is as
follows:
-
If the input number has no fractional part (it is integral), and if it can be rendered in 40 digits or less, then it is rendered as an integer. If necessary, trailing zeros are used, to avoid notation with an exponent. For example,
1000000000
is used instead of1E+9
. -
If the input number has a fractional part, the number is rendered in 40 digits or less with a decimal point separator. If necessary, zeros are used to avoid notation with an exponent. For example,
0.00001
is used instead of1E-5
. -
If conversion of the input number would result in a loss of digit precision in the 40-digit format, the number is instead rendered with an exponent. This can happen for a number whose absolute value is extremely small or extremely large, even if the number is integral. For example,
1E100
is used, to avoid a 1 followed by 100 zeros.
In practice, this canonical numeric format means that in most cases the
numeric _id
field value results in an obvious, or "pretty"
VARCHAR2
value for column id
. A format that
uses an exponent is used only when necessary, which generally means
infrequently.
Document Content Conversion
Two general considerations:
-
BSON format allows duplicate field values in the same object. OSON format does not. When converting to OSON, detection of duplicate fields in BSON data raises an error.
-
OSON format has no notion of the order of fields in an object; applications cannot depend on or expect any particular order (in keeping with the JSON standard). BSON format maintains the order of object fields; applications can depend on the order not changing.
Table 2-2 specifies the type mappings that are applied when converting
scalar BSON data to scalar OSON data. The OSON scalar types used are SQL data types,
except as noted. Any BSON types not listed are not converted; instead, an error is
raised when they are encountered. This includes BSON types regex
,
and JavaScript
.
Table 2-2 JSON Scalar Type Conversions: BSON to OSON Format
BSON Type | OSON TypeFoot 2 | Notes |
---|---|---|
Double |
BINARY_DOUBLE |
NA |
32-bit integer |
NUMBER (Oracle number)
|
Flagged as int .
|
64-bit integer |
NUMBER (Oracle number)
|
Flagged as long .
|
Decimal128 |
NUMBER (Oracle number)
|
Flagged as decimal . Note:
This conversion can be lossy. |
Date |
TIMESTAMP WITH TIME ZONE |
Always UTC time zone. |
String |
VARCHAR2 |
Always in character set AL32UTF8 (Unicode UTF-8). |
Boolean |
BOOLEAN |
Supported only if initialization parameter
compatible has value 23 or
larger. (There is no Oracle SQL BOOLEAN type in
releases prior to 23ai.)
|
ObjectId |
ID
(RAW(12) )
|
NA |
Binary data (UUID)
|
ID
(RAW(16) )
|
NA |
Binary data (non-UUID)
|
RAW |
NA |
Null |
NULL |
Used for JSON null .
|
Footnote 2 These are SQL data types, except as noted.
Related Topics
See Also:
-
Overview of SODA Documents in Oracle Database Introduction to Simple Oracle Document Access (SODA)
-
BSON types (MongoDB)
-
Data Types (MongoDB shell)
2.6 Other Differences Between MongoDB and Oracle Database
Various differences between MongoDB and Oracle Database are described. These differences are generally not covered in other topics. Consider these differences when you migrate an application to Oracle Database or you develop a new application for Oracle Database that uses MongoDB commands.
-
With MongoDB, fields in a JSON object are ordered. With Oracle Database, they are not ordered. For example, field
_id
is not necessarily the first field in an object. Applications must not expect or rely on any particular field order. According to the JSON language standard, object fields are not ordered; only array elements are ordered. See JSON Syntax and the Data It Represents in Oracle Database JSON Developer’s Guide. -
With MongoDB, the value of field
_id
can be a JSON object. Oracle Database API for MongoDB supports only BSON typesObjectId
,String
,Double
,32-bit integer
,64-bit integer
,Decimal128
, andBinary data
(subtype for UUID) for field_id
; an error is raised for any other type. See BSON Types.If you are migrating an existing application that expects object values for
_id
then consider copying the values of field_id
in your data to some new field and using a string value for_id
. -
Read and write concerns regarding MongoDB transactions do not apply to Oracle Database. Oracle Database transactions are fully ACID-compliant, and thus reliable — atomicity, consistency, isolation, and durability. ACID compliance ensures that your data remains accurate and consistent despite any failure that might occur while processing a transaction.
-
Oracle API for MongoDB does not support the following MongoDB transaction capabilities:
-
Inclusion of DDL operations, such as
createCollection
, within a transaction. Attempts to create a collection or an index within a transaction raise an error. -
Inclusion of operations across multiple databases. All operations within a transaction must be confined to a single database (schema). Otherwise, an error is raised.
-
-
Retryable writes or commits when an error is raised.
MongoDB
retryWrite
operations raise an error. If you use a driver that hasretryWrite
turned on by default, then setretryWrites=false
in your connection string to turn this off. -
Oracle Database and MongoDB have different read isolation and consistency levels. Oracle Database API for MongoDB uses read-committed consistency as described in Data Concurrency and Consistency of Oracle Database Concepts.
-
Oracle Database API for MongoDB supports only the PLAIN (LDAP SASL) authentication mechanism, and it relies on Oracle Database authentication and authorization.
-
Oracle Database does not support the MongoDB
collation
field for any command (such asfind
). An error is raised if you use fieldcollation
. Oracle collates values using the Unicode binary collation order. -
MongoDB allows different collections in the same "database" to have indexes of the same name. This is not allowed in Oracle Database — the name of an index must be unique across all collections of a given database schema ("database").
-
The maximum size of a document for MongoDB is 16 MB. The maximum size for Oracle Database (and thus for the MongoDB API) is 32 MB.
2.7 Accessing Collections Owned By Other Users (Database Schemas)
You can directly access a MongoDB API collection owned by another user (database schema) if you log into that schema. You can indirectly access a collection owned by another user, without logging into that schema, if that collection has been mapped to a collection in your schema.
A MongoDB API collection of JSON documents consists of (1) a collection backing table, which contains the JSON documents in the collection, and (2) some JSON-format collection metadata, which is stored in the data dictionary and specifies various collection-configuration properties. The backing table belongs to a given database user/schema. The metadata is stored in the database data dictionary.
A mapped collection is a collection that is defined (mapped) on top of an existing table, which can belong to any database schema and which could also back one or more other collections.
You can control which operations on a collection — including a mapped collection — are allowed for various users (schemas), by granting those users different privileges or roles on the backing table.
Example 2-9 illustrates this.
Example 2-9 Creating a Collection in One Schema and Mapping a Collection To It in Another Schema
In this example user john
creates collection
john_coll
(in database schema john
), and adds
a document to it. User john
then grants user janet
some access privileges to the backing table of collection
john_coll
.
User janet
then maps a new collection,
janet_coll
(in schema janet
) to collection
john_coll
in schema john
. (The original and
mapped collections need not have different names, such as john_coll
and janet_coll
; they could both have the same name.)
User janet
then lists the collections available to
schema janet
, and reads the content of mapped collection
janet_coll
, which is the same as the content of collection
john_coll
.
(The commands submitted to mongosh
are each a single
line (string), but they are shown here continued across multiple lines for
clarity.)
Note:
Examples in this documentation of input to, and output from, Oracle Database API for
MongoDB use the syntax of shell mongosh
.
1. When connected to the database as user
john
, run PL/SQL code to create collection
john_coll
backed by table
john_coll
. The second argument to
create_collection
is the metadata needed for a
MongoDB-compatible collection. (The backing table name is derived from the
collection name — see Default Naming of a Collection Table.)
DECLARE
col SODA_COLLECTION_T;
BEGIN
col := DBMS_SODA.create_collection(
'john_coll',
'{"contentColumn" : {"name" : "DATA",
"sqlType" : "BLOB",
"jsonFormat" : "OSON"},
"keyColumn" : {"name" : "ID",
"assignmentMethod" : "EMBEDDED_OID",
"sqlType" : "VARCHAR2"},
"versionColumn" : {"name" : "VERSION", "method" : "UUID"},
"lastModifiedColumn" : {"name" : "LAST_MODIFIED"},
"creationTimeColumn" : {"name" : "CREATED_ON"}}');
END;
2. Connect to the database using shell mongosh
as user john
, list the collections in that schema (John's
collections), insert a document into collection john_coll
, and show
the result of the insertion.
mongosh 'mongodb://john:...
@MQSSYOWMQVGAC1Y-CTEST.adb.us-ashburn-1.oraclecloudapps.com:27017/john
?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'
john> show collections;
Output:
john_coll
john> db.john_coll.insert({"hello" : "world"});
john> db.john_coll.find()
Output:
[ { _id: ObjectId("6318b0060a51240e4bf3b001"), hello: 'world' } ]
3. In schema john
, grant user
janet
access privileges to collection
john_coll
and its backing table of the same name,
john_coll
.
GRANT SELECT, INSERT, UPDATE, DELETE ON john.john_coll TO janet;
4. When connected to the database as user (schema)
janet
, Create a new collection
janet_coll
in schema janet
that's
mapped to collection john_coll
in schema
john
.
The second argument to method create_collection()
is the
collection metadata. Among the things it specifies here are the schema and
backing-table names of the collection to be mapped to. The last argument,
CREATE_MODE_MAP
, specifies that the new collection
is to be mapped on top of the table that backs the original collection.
DECLARE
col SODA_COLLECTION_T;
BEGIN
col := DBMS_SODA.create_collection(
'janet_coll',
'{"schemaName" : "JOHN",
"tableName" : "JOHN_COLL",
"contentColumn" : {"name" : "DATA",
"sqlType" : "BLOB",
"jsonFormat" : "OSON"},
"keyColumn" : {"name" : "ID",
"assignmentMethod" : "EMBEDDED_OID",
"sqlType" : "VARCHAR2"},
"versionColumn" : {"name" : "VERSION", "method" : "UUID"},
"lastModifiedColumn" : {"name" : "LAST_MODIFIED"},
"creationTimeColumn" : {"name" : "CREATED_ON"}}',
DBMS_SODA.CREATE_MODE_MAP);
END;
Note:
The schema and table names used in the collection metadata argument
must be as they appear in the data dictionary, which in this case means they
must be uppercase. You can use these queries to obtain the correct schema and
table names for collection <collection>
(when
connected as the owner of <collection>
):
SELECT c.json_descriptor.schemaName FROM USER_SODA_COLLECTIONS c
WHERE uri_name = '<collection>';
SELECT c.json_descriptor.tableName FROM USER_SODA_COLLECTIONS c
WHERE uri_name = '<collection>';
5. Connect to the database using shell mongosh
as
user janet
, list the available collections, and show the
content of collection janet_coll
(which is the same as the content
of John's collection john_coll
).
mongosh 'mongodb://janet:...
@MQSSYOWMQVGAC1Y-CTEST.adb.us-ashburn-1.oraclecloudapps.com:27017/janet
?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'
janet> show collections;
janet_coll
janet> db.janet_coll.find()
[ { _id: ObjectId("6318b0060a51240e4bf3b001"), hello: 'world' } ]
Footnote Legend
Footnote 1: MongoDB calls a composite index a compound index. A composite index is also sometimes called a concatenated index.