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.

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.

MongoDB index operations, such as createIndex and dropIndex, are not usable with Oracle Database; they are ignored. And you typically do not need to create indexes equivalent to those you might use for MongoDB. If needed, you can instead create relevant indexes for Oracle Database, using the JSON Page of Using Oracle Database Actions (see Creating Indexes for JSON Collections), Simple Oracle Document Access (SODA), or SQL (CREATE INDEX). 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 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-4 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.

  1. Right-click the collection name (here, collection orders) and select Indexes from the popup menu.

    Description of json_page_create_index_001.png follows
    Description of the illustration json_page_create_index_001.png
  2. 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 to VARCHAR2 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 json_page_create_index_002.png follows
    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 the unique index 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.

Item method number() is used in the path expression that identifies the field to index, to convert the field value to a JSON number value. For example, a PONumber string value of "42" is converted to the number 42.

The code uses ERROR ON ERROR handling, to raise an error if a document no PONumber field or it has more than one.

CREATE UNIQUE INDEX idx1 ON orders
  (json_value(data, '$.PONumber.number()' ERROR ON ERROR))

Example 2-4 Creating a Materialized View And an Index For Fields Within Elements of an Array

This example indexes field UPCCode, which can occur multiple times in a document because it is contained in objects within an array (objects as elements or at lower levels within elements).

Starting with Oracle Database 21c you can create a multivalue index for such fields. Prior to Oracle Database 21c you can instead create a materialized view that extracts the data you want to index, and create a function-based index on that view data.

This SQL code does that. It creates materialized view mv_UPCCode with column upccode, which is a projection of field UPCCode from within 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 '$.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 myindex:

db.fruit.find({"name":"apple"}).hint("myindex")

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.col.find().hint({"$native":"MONITOR"})

See Also:

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 option authMechanism. In particular, the SCRAM-SHA-* authentication methods are not supported.

  • $external value for option authSource. (This is anyway required for MongoDB whenever the authentication method is PLAIN.)

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 the Oracle Autonomous Database console. The minimum Oracle Database roles required to use the API are CONNECT, RESOURCE, and SODA_APP.

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 otherDB) that switches 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. User admin is a predefined administrative 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 as other_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).

See Also:

  • Create Users on Autonomous Database in Using Oracle Autonomous Database on Shared Exadata Infrastructure

  • Manage User Roles and Privileges on Autonomous Database in Using Oracle Autonomous Database on Shared Exadata Infrastructure

  • 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 on Shared Exadata Infrastructure 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.

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 and mongoimport.

    mongoexport exports data from a MongoDB instance to your file system, and mongoimport imports the exported data from your file system to Oracle Database. Provide your database connection information when using mongoimport. Example 2-5 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 mongodb_compass.png follows
    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-6 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-5 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 on Shared Exadata Infrastructure 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-6 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 on Shared Exadata Infrastructure 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;
/

See Also:

2.4 Replace MongoDB Aggregation Pipelines

Oracle Database does not use MongoDB aggregation pipelines. Replace any such pipelines for your application with direct use of SQL. The Oracle Database optimizer executes queries in an optimal order.

MongoDB's aggregation pipeline is essentially a weak emulation of SQL capabilities. With MongoDB you express operations such as sort and group as separate steps in a procedural pipeline — you specify them as executing in sequence.

Oracle Database does not, in general, execute aggregation operators in a sequential manner, and you need not specify the execution order. Instead, the database relies on declarative SQL code and an optimizer. The optimizer picks an optimal execution plan based on available indexes, data statistics, cost estimates, and so on.

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.

As a user of Oracle Database API for MongoDB, you can apply SQL directly to JSON data in the database without worrying about manually sequencing any operations you perform on it.

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 analytics. You can easily join JSON data from different collections, or join JSON data with non-JSON data stored in the database. And you can encapsulate queries as database views or materialized views.

You can generally replace a MongoDB aggregation-pipeline expression with equivalent SQL code. (In fact, most MongoDB commands use the same or similar names as an equivalent SQL operator.) Example 2-7 illustrates this.

These are correspondences between MongoDB aggregation operators and Oracle SQL constructs:

  • $avg — Use aggregate function avg. See AVG in Oracle Database SQL Language Reference.in Oracle Database SQL Language Reference.

  • $count — Use aggregate function count. See COUNT in Oracle Database SQL Language Reference.in Oracle Database SQL Language Reference.

  • $first — Use SQL SELECT with FETCH FIRST. See SELECT - row_limiting_clause in Oracle Database SQL Language Reference in Oracle Database SQL Language Reference.

  • $geoNear — Use function sdo_nn or other Oracle Spatial and Graph functions, to return SDO_GEOMETRY object-type instances. See Using GeoJSON Geographic Data in Oracle Database JSON Developer’s Guide.

  • $group — Use GROUP BY. See SELECT in Oracle Database SQL Language Reference.in Oracle Database SQL Language Reference.

  • $limit — Use SQL SELECT with FETCH NEXT. See SELECT - row_limiting_clause in Oracle Database SQL Language Reference in Oracle Database SQL Language Reference.

  • $lookup — Use a SQL join. See Joins in Oracle Database SQL Language Reference.

  • $match — Use function json_value or condition json_exists, in a WHERE clause.

  • $max — Use aggregate function max. See MAX in Oracle Database SQL Language Reference.in Oracle Database SQL Language Reference.

  • $merge — Use MERGE. See MERGE in Oracle Database SQL Language Reference.

  • $min — Use aggregate function min. See MIN in Oracle Database SQL Language Reference.in Oracle Database SQL Language Reference.

  • $out — Use INSERT AS SELECT. See INSERT in Oracle Database SQL Language Reference.

  • $project — Use the JSON generation functions or updating function json_transform. See Generation of JSON Data Using SQL and Oracle SQL Function JSON_TRANSFORM in Oracle Database JSON Developer’s Guide.

  • $redact — Use JSON updating function json_transform. See Oracle SQL Function JSON_TRANSFORM in Oracle Database JSON Developer’s Guide.

  • $search — Use Oracle SQL condition json_textcontains. See Oracle SQL Condition JSON_TEXTCONTAINS in Oracle Database JSON Developer’s Guide.

  • $set — Use a JSON updating function: json_transform or json_mergepatch. See Oracle SQL Function JSON_TRANSFORM and Oracle SQL Function JSON_MERGEPATCH in Oracle Database JSON Developer’s Guide.

  • $skip — Use SQL SELECT with OFFSET. See SELECT - row_limiting_clause in Oracle Database SQL Language Reference in Oracle Database SQL Language Reference.

  • $sort — Use ORDER BY. See Sorting Query Results in Oracle Database SQL Language Reference.in Oracle Database SQL Language Reference.

  • $sum — Use aggregate function sum. See SUM in Oracle Database SQL Language Reference.in Oracle Database SQL Language Reference.

  • $unionWith — Use UNION. See Set Operators in Oracle Database SQL Language Reference

  • $unset — Use a JSON updating function: json_transform or json_mergepatch. See Oracle SQL Function JSON_TRANSFORM and Oracle SQL Function JSON_MERGEPATCH in Oracle Database JSON Developer’s Guide.

  • $unwind — Use function json_table. See SQL/JSON Function JSON_TABLE in Oracle Database JSON Developer’s Guide.

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.

Example 2-7 Replacing MongoDB Aggregation Pipeline Code with SQL 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;

See Also:

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 used by Oracle Database.

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

Document Key: Differences and Conversion

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, 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, Oracle Database API for MongoDB creates id column values from the values of field _id in your MongoDB documents. MondoDB 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 the documents in a JSON collection need not have an _id field. And because, for Oracle Database, 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) and 123 (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 column id.

BSON values of field _id are converted to VARCHAR2 column id values according to Table 2-2. 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 of 1E+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 of 1E-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 There is no Oracle SQL type for this.
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.

See Also:

Overview of SODA Documents in Oracle Database Introduction to Simple Oracle Document Access (SODA)

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 types ObjectId, String, Double, 32-bit integer, 64-bit integer, Decimal128, and Binary 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 has retryWrite turned on by default, then set retryWrites=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 as find). An error is raised if you use field collation. 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.



Footnote Legend

Footnote 1: MongoDB calls a composite index a compound index. A composite index is also sometimes called a concatenated index.