Support for MongoDB APIs, Operations, and Data Types — Reference

The script content on this page is for navigation purposes only and does not alter the content in any way.

MongoDB APIs, operations, and data types supported by Oracle AI Database are listed, together with information about their support.

Unsupported MongoDB constructs raise an error. A construct that is ignored is listed in this documentation as a no-op (it does not raise an error). A construct can be ignored because it makes no sense or is not needed on Oracle architecture.

Database Commands

Support of MongoDB database commands is described. This includes commands for administration, aggregation, authentication, diagnostic, query and write operations, role management, replication, sessions, user management, and sharding.

Table 1 Administration Commands

Table 2 Aggregation Commands

Table 3 Authentication Commands

Table 4 Diagnostic

Table 5 Query and Write Operation

find().sort({"name":**1**, "age":**1**, "birthday":**-1**}).**hint**({"**$type**":{"age":"**number**", "birthday":"**dateTime**"}})

See Also: Query and Write Operation Commands in the MongoDB Reference manual

Table 6 Role Management Commands

Table 7 Replication Commands

Table 8 Sessions Commands

The security model of the Oracle Database API for MongoDB leverages the built-in security model of the Oracle AI Database.

Table 9 User Management Commands

Table 10 Sharding Commands

Query and Projection Operators

Support of MongoDB query and projection operators is described. This includes array, bitwise, comment, comparison, element, evaluation, geospatial, and logical query operators, as well as projection operators.

Table 11 Array Query Operators

Table 12 Bitwise Query Operators

Table 13 Comparison Query Operators

Table 14 Element Query Operators

Table 15 Evaluation Query Operators

Table 16 Geospatial Query Operators

Table 17 Logical Query Operators

Table 18 Projection Operators

Update Operators

Support of MongoDB update operators is described. This includes array, bitwise, field, and modifier update operators.

Table 19 Array Update Operators

Table 20 Bitwise Update Operator

Table 21 Field Update Operators

Table 22 Modifier Update Operators

Cursor Methods

Support of MongoDB cursor methods is described.

Table 23 Cursor Methods

Aggregation Pipeline Stages

Support of MongoDB aggregation pipeline stages is described.

Table 24 Stages

$sql Aggregation Pipeline Stage

You can use a $sql stage to execute Oracle SQL and PL/SQL code.

Here is an example that uses shell mongosh to execute, as user user100, an aggregation pipeline with a simple $sql stage from a MongoDB client.

insertMany is used to create a collection called emps and inserts three employee documents into it.Foot 1

user100> db.**emps**.insertMany([
           {"ename" : "SMITH", "job" : "CLERK",    "sal" : 800},
           {"ename" : "ALLEN", "job" : "SALESMAN", "sal" : 1600},
           {"ename" : "WARD",  "job" : "SALESMAN", "sal" : 1250}
         ]);

Result shown by mongosh:

{
  acknowledged: true,
  insertedIds: {
    '0': ObjectId("6595eb06e0fc41db6de93a6d"),
    '1': ObjectId("6595eb06e0fc41db6de93a6e"),
    '2': ObjectId("6595eb06e0fc41db6de93a6f")
  }
}

A SQL SELECT query is used to compute the average of the employee salaries for each job. The average is computed using SQL function AVG.

user100> db.aggregate([ {**$sql** :
           **`****SELECT** e.data.**job**, **AVG**(e.data.**sal**) **average**
              FROM emps e
              GROUP BY e.data.**job****`**
         } ]);

The query returns two JSON objects with fields JOB and AVERAGE.

[
  { **JOB**: 'CLERK', **AVERAGE**: 800 },
  { JOB: 'SALESMAN', AVERAGE: 1425 }
]

A $sql stage has the following syntax. The fields other than $sql are described in Table 3-25.

{$sql    : {statement : *<SQL statement>*,
 binds   : *<variables>*,
 dialect : *<dialect>*,
 format  : *<format>*}}

The abbreviated syntax {$sql : <SQL statement>} is equivalent to this syntax {$sql : **{statement :** <SQL statement>**}**}.

** is the Oracle SQL statement to execute.

• If $sql is the only stage in the pipeline and the pipeline has no starting collection, then <SQL statement> can be any Oracle SQL or PL/SQL code, including SQL data definition language (DDL) and data manipulation language (DML) code.

  For example, this code uses a SQL UPDATE statement to increase the salaries of all employees,by 10 percent:

  db.aggregate([ {**$sql** :
                  {statement :
                   "**UPDATE** employees **SET** salary = salary * 0.1"}} ]);

• Otherwise, either the pipeline is executed on a collection or it has multiple stages. In this case:

  • <SQL statement> must be aSELECTstatement that projects a singleJSON-type column.

  • The SELECT statement can refer to the output from the input collection or the previous stage using the database view (row source) named INPUT, which has a single JSON-type column DATA containing the input documents.

    See also Query JSON Data in Oracle AI Database JSON Developer’s Guide.

  For example, the following code acts on starting collection orders. It has three stages:

  • Stage $match filters collection orders, choosing only the documents with a status field that has value closed.

  • Stage $sql takes as input the filtered documents output from stage $match. It obtains them from column data of view input (alias v). While selecting the documents, it uses Oracle SQL Function JSON_MERGEPATCH to add a system timestamp to them as the value of new field updated. The resulting timestamped documents are returned as the output from stage $sql.

  • Stage $out creates a new collection, closed_orders, using the output of stage $sql, that is, the documents returned as the result of the SQL SELECT statement.

  db.**orders**.aggregate([ {$**match** : {status : "closed"}},
                        {**$sql** :
  
                         **`**SELECT **json_mergepatch**(
  
                                   **v**.**data**,
                                   JSON {'updated' : **SYSTIMESTAMP**})
                            FROM **input** **v****`**},
                        {**$out** : "**closed_orders**"} ]);

  This query returns a document from the new collection, closed_orders:

  db.closed_orders.**findOne**()

  {
    _id: ObjectId('65e8b973ca4d0a3a255794c8'),
    order_id: 12382,
    product: 'Autonomous Database',
    status: '**closed**',
    updated: **ISODate('2024-03-06T18:44:23.275Z**')
  }

These SQL statements are not supported by stage $sql:

• Statements that use OUTparameters or invoke stored procedures directly (see Subprogram Parameter Modes and SQL Statements for Stored PL/SQL Units)

• Data Manipulation Language (DML) statements that use a returning clause and return variables (see DML Returning)

All stages return zero or more JSON objects as their result. The result for a $sql stage depends on whether or not the SQL statement executed is a SELECT statement.

• For a SELECT statement, each row in the query result set is mapped to a JSON object in the $sql stage result. See $sql Stage Result for a SELECT Statement.

• For a non-SELECT statement, the $sql stage result is a JSON object with the single field result, whose value indicates the number of table rows that the statement changed. See $sql Stage Result for a Non-SELECT Statement.

Table 25 $sql Fields

binds Field

The optional binds field in a $sql stage specifies one or more sets of SQL variable bindings (placeholder expressions). Each binding specifies a variable used in the SQL statement and the value to replace it with. When multiple binding sets are specified, thestatement is executed once for each set.

There are three ways to specify a single set of bindings:

• Specify a set of bindings as an object, each of whose members has a variable's name as its field name and the variable's value as field value.

  For example, here variable empno is bound to value "E123", and variable ename is bound to value "Abdul J.".

  db.aggregate([ {$sql :
                   {statement :
                     `INSERT INTO emp(empno, ename)
                        VALUES(**:empno**, :ename)`,
                     binds : **{**"**empno**" : "**E123**",
                              "ename" : "Abdul J."**}**}} ]);

• Specify a set of bindings as an array, each of whose elements is an object with any of these fields: index, name, value, dataType. Each object represents a binding.

  For example, here the bind variable :empno has value "E123", and variable :ename, has value "Abdul J.":

  db.aggregate([ {$sql :
                   {statement :
                     `INSERT INTO emp(empno, ename)
                        VALUES (**:empno**, :ename)`,
                    binds : **[** {**name : empno**,
  
                               **value** : **"E123"**},
                              {name : "ename",
                               value : "Abdul J."} **]** }} ]);

• Specify a set of bindings as an array, each of whose elements is a bind-variable value. Each value is bound according to its position in the array: the first array element ("E123", here) is the value of the first bind variable, :empno, and the second element is the value of the second variable. (The array elements need not be of the same type.)

  db.aggregate([ {$sql :
                   {statement :
                     `INSERT INTO emp(empno, ename)
                        VALUES (**:empno**, :ename)`,
                    binds : [ **"E123"**, "Abdul J." ] }} ]);

To specify multiple sets of bindings you just use an array of values that each specify a single set of bindings. Each of the array elements can specify a binding set using any of the ways described above: (1) an object whose members are variable name–value pairs, (2) an array of objects with optional fields index, name, value, and dataType, (3) an array of variable values whose array positions correspond to the variable indexes in the VALUES clause.

The following three examples illustrate this. They are semantically equivalent. The INSERT statement of each example is executed three times:

• Once for the first set of bindings: variable :empno as "E123", and variable :ename as "Abdul J."

• Once for the second set of bindings: variable :empno as "E456" and variable :ename as "Elena H."

• Once for the third set of bindings: variable :empno as "E789" and variable :ename as "Francis K."

In the first example, the array elements are objects, each of which specifies a set of bindings. Each element of an object specifies the value of an individual (positional) binding.

db.aggregate([ {$sql :
                 {statement :
                   `INSERT INTO emp(empno, ename)
                      VALUES (**:empno**, :ename)`,
                  binds     :
                   [ **{**"**empno**" : "**E123**", "ename" : "Abdul J."**}**,
                     **{**"**empno**" : "**E456**", "ename" : "Elena H."**}**,
                     **{**"**empno**" : "**E789**", "ename" : "Francis K."**}** ]}} ]);

In the second example, the array elements are themselves arrays, each of which specifies a set of variable bindings. But in this case each element of the inner arrays is an object with the fields: name and value, specifying the value of an individual (positional) binding.

db.aggregate([ {$sql :
                 {statement :
                   `INSERT INTO emp(empno, ename)
                      VALUES (**:empno**, :ename)`,
                  binds : [ **[** {**name**  : **empno**,
                               **value** : **"E123"**},
                              {name : ename,
                               value : "Abdul J."} **]**,
                            **[** {**name**  : **empno**,
                               **value** : "**E456**"},
                              {name  : ename,
                               value : "Elena H."} **]**,
                            **[** {**name**  : **empno**,
                               **value** : "**E789**"},
                              {name  : ename,
                               value : "Francis K."} **]** ]}} ]);

In the third example, the array elements are themselves arrays, each of which specifies a set of variable bindings. Each element of the inner arrays specifies the value of an individual (positional) binding.

db.aggregate([ {$sql :
                 {statement :
                   `INSERT INTO emp(empno, ename)
                      VALUES (**:empno**, :ename)`,
                  binds : [ **[** "**E123**", "Abdul J." **]**,
                            **[** "E456", "Elena H." **]**,
                            **[** "E789", "Francis K." **]** ]}} ]);

See also Example 3-5.

Table 26 Fields of binds Object

Supported SQL Data Types for Field dataType

The allowed values for field dataType are described.

BSON types not listed are not supported; their use raises an error.

Starting with Oracle AI Database 26ai, JSON type is supported for each of the supported BSON types. Prior to release 26ai, an error is raised if field dataType has value JSON.

Table 27 Field datatype Values

$sql Stage Result for a SELECT Statement

For a SELECT statement, each row in the query result set is mapped to a JSON object in the $sql stage result. (The MongoDB shell output encloses the objects in brackets ([, ]); the result is not a JSON array.)

The query can return a single column of JSON data, or it can return data from multiple columns, each of which can be of any type.

• In the former case, the JSON object in the $sql-stage result is the JSON data returned by the SQL query. This is illustrated in Example 3-1.

• In the latter case, the JSON object in the result is constructed from the multiple column values. The column aliases in the query are used as the object field names. This is illustrated in Example 3-2.

For the second case (query returning multiple columns), the query results are mapped to new BSON documents. If a given SQL column is known to be JSON data (because it is JSON type or it has an IS JSON constraint) then it is used directly, as a BSON (JSON) value. Otherwise, the SQL-to-BSON type mappings for the column values are as shown in Table 3-28. Selection of a value from a column of any other type raises an error.

Table 28 SELECT: Mappings of Non-JSON

Example 3-1 Result for SELECT Query that Returns a Single Column of JSON Data

This example shows two queries that select columns from table dept and return a single column of JSON data. They both use SQL construction JSON{…} to produce a JSON-type object.

This first query uses a wildcard (*) to select all columns from table dept. The column names are used as the resulting object field names.

Query:

SELECT JSON{*****} **data** FROM deptFoot 2

Result:

[ {**DEPTNO** : 10, **DNAME** : 'ACCOUNTING', **LOC** : 'NEW YORK'},
  {DEPTNO : 20, DNAME : 'RESEARCH',   LOC : 'DALLAS'},
  {DEPTNO : 30, DNAME : 'SALES',      LOC : 'CHICAGO'},
  {DEPTNO : 40, DNAME : 'OPERATIONS', LOC : 'BOSTON'} ]

This second query selects columns deptno and dname from table dept. It uses JSON{…} to produce a JSON-type object with the column names as the values of fields _id and name, respectively.

Query:

SELECT JSON{'**_id**' : **deptno**, '**name**', **dname**} **data** FROM deptFoot 3

Result:

[ {**_id** : 10, **name** : 'ACCOUNTING'},
  {_id : 20, name : 'RESEARCH'},
  {_id : 30, name : 'SALES'},
  {_id : 40, name : 'OPERATIONS'} ]

Example 3-2 Result for SELECT Query that Returns Data from Multiple Columns (Any Types)

This example shows two queries that select columns from table dept and construct a JSON object. (These queries do not use construction JSON{…}.)

This first query selects columns deptno, dname, and loc. The field names of the resulting object are the aliases of the selected columns and the field values are the corresponding column values.

Query:

SELECT deptno, dname, loc FROM dept

Result:

[ {**DEPTNO** : 10, **DNAME** : 'ACCOUNTING', **LOC** : 'NEW YORK'},
  {DEPTNO : 20, DNAME : 'RESEARCH',   LOC : 'DALLAS'},
  {DEPTNO : 30, DNAME : 'SALES',      LOC : 'CHICAGO'},
  {DEPTNO : 40, DNAME : 'OPERATIONS', LOC : 'BOSTON' } ]

This second query selects columns deptno and loc, and it uses SQL function SYSTIMESTAMP to produce a timestamp. The query provides field names id, location, and ts for the resulting object, instead of using the column aliases. mongosh wraps the ISO timestamp value with the ISODate helper.

Query:

SELECT deptno "**id**", loc "**location**", **SYSTIMESTAMP** "**ts**" FROM dept

Result:

[ {**id**       : 10,
   **location** : 'NEW YORK',
   **ts**       : **ISODate("2023-12-01T20:44:17.118Z")**},
  {id       : 20,
   location : 'DALLAS',
   ts       : ISODate("2023-12-01T20:44:17.118Z")},
  {id       : 30,
   location : 'CHICAGO',
   ts       : ISODate("2023-12-01T20:44:17.118Z")},
  {id       : 40,
   location : 'BOSTON',
   ts       : ISODate("2023-12-01T20:44:17.118Z")} ]

$sql Stage Result for a Non-SELECT Statement

The result of a $sql stage whose statement is not a SELECT statement is a JSON object with the single field result, whose value indicates the number of rows of data that were changed by the statement (that is, inserted, deleted, or updated). When such a stage uses multiple sets of bind variables, the result is an array of such numbers (of rows changed). Example 3-3, Example 3-4, Example 3-5, and Example 3-6 illustrate the result for non-SELECT statements.

Example 3-3 Result for a DDL Statement — No Rows Are Modified

A DDL statement, such as this CREATE TABLE statement, changes no rows.

db.aggregate([{$sql:`**CREATE TABLE** employee (name VARCHAR2(4000), job
      VARCHAR2(4000))`}])

[ {result : **0**} ]

Example 3-4 Result for a DML Statement That Modifies One Row

The INSERT statement in this $sql stage inserts one row, so result is 1.

db.aggregate([ {$sql : "**INSERT** INTO employee VALUES ('Bob', 'Programmer')"} ]);

[ {result : **1**} ]

Example 3-5 Result for a DML Statement That Modifies Three Rows

The INSERT statement in this $sql stage inserts three rows, one for each of the three sets of bind variables.

db.aggregate([ {$sql :
                 {statement : "INSERT INTO employee VALUES (:name, :job)",
                  binds     : [ **{"name" : "John",    "job" : "Programmer"}**,
                                **{"name" : "Jane",    "job" : "Manager"}**,
                                **{"name" : "Francis", "job" : "CEO"}** ]}}]);

[ {result : [ **1**, **1**, **1** ]} ]

Example 3-6 Result for a DML Statement That Modifies Two Rows

This DELETE statement deletes two rows, so result is 2.

db.aggregate([ {$sql : `DELETE FROM employee e WHERE e.job = 'Programmer'`} ])

[ {result : 2} ]

$external Aggregation Pipeline Stage

You can use an $external stage to access data from external files.

Using JSON data stored in an external file you can, for example:

• Use stage $match to filter documents in the file. See Example 3-10.

• Use stage $group to group documents in the file.

• Use stage $out to store the output of stage $external in a new JSON collection. See Example 3-10.

An $external stage has the following syntax. The fields are described in Table 3-29.

{$external : {location   : *<URL or file name>*,
              directory  : *<database directory name>*,
              credential : *<credential name*
              path       : *<SQL/JSON path expression>*}}

The abbreviated syntax {$external : <pre-authenticated URI>} is equivalent to this syntax {$external : **{location :** <pre-authenticated URI>**}**}.

Table 29 $external Fields

Footnote 4 Accessing external files using the Internet requires PL/SQL package DBMS_CLOUD. It is pre-installed for Oracle Autonomous AI Database, but you must install and configure it for a non-autonomous database.

For any URI for which you don’t need authentication, so you don’t need to specify a credential, if you also don’t need to specify a path then you can just use the location URL as the value of field $external. See Example 3-10. No authentication is needed for public repositories, files in public buckets, or pre-authenticated URIs.

If fields location and directory are both present, then if their values are both are valid then the directory field is ignored; otherwise an error is raised.

Example 3-7 Creating a Credential

This use of stage $sql uses PL/SQL subprogram DBMS_CLOUD.create_credential to create credential MYCRED for user myuser@example.com. PL/SQL package DBMS_CLOUD is pre-installed for Oracle Autonomous AI Database, but you must install and configure it for a non-autonomous database.

db.aggregate([ {$sql :
                 `BEGIN
                    DBMS_CLOUD.**create_credential**(
                      credential_name => '**MYCRED**',
                      username        => 'myuser@example.com',
                      password        => 'XXXXXXXXX');
                  END;`} ])

Example 3-8 Extracting JSON Documents From a Private Bucket

This example extracts the elements of the array in private object-store bucket array.json as document rows, using credential MYCRED. The path, $[*], matches each array element.

Assume that this is the content of array.json:

[ {"_id" : {"$oid" : "663bce1c219cb9c411e8a719"},
   "a"   : {"b"    : [ {"z" : {"b" : 1, "c" : 99}},
                       {"z" : {"b" : 2}},
                       {"z" : {"a" : 5}},
                       {"z" : {"b" : 1, "a" : 5}} ]}},
  {"_id" : {"$oid" : "663bce1c219cb9c411e8a71a"},
   "a"   : {"b"    : [ {"z" : {"b" : 1, "c" : 99}},
                       {"z" : {"b" : 2}},
                       {"z" : {"a" : 5}} ]}} ]

db.aggregate([ {$external :
                 {location   :
                   "https://private-repo.example.com/.../array.json",
                  credential : "**MYCRED**",
                  path       : "**$[*]**"}} ])

The stage returns the elements (two objects) from the array:

[ {_id : ObjectId("663bce1c219cb9c411e8a719"),
   a   : {b : [ {z : {b : 1, c : 99}},
                {z : {b : 2}},
                {z : {a : 5}},
                {z : {b : 1, a : 5}} ]}}
  {_id : ObjectId("663bce1c219cb9c411e8a71a"),
   a   : {b : [ {z : {b : 1, c : 99}}, {z : {b : 2}}, {z : {a : 5}} ]}} ]

Example 3-9 Extracting JSON Documents From an External File In a Directory

This example uses a path expression to extract only the objects that are values of field z from external file array.json (defined in Example 3-8). The example assumes that file array.json exists in directory object DEMO.

db.aggregate([ {$external :
                 {location  : "array.json",
                  directory : "DEMO",
                  path      : `**$.a.b[*].z**`}}])

The stage returns these objects from the array:

[ {b : 1, c : 99},
  {b : 2},
  {a : 5},
  {b : 1, a : 5},
  {b : 1, c : 99},
  {b : 2},
  {a : 5} ]

Example 3-10 Create a New Collection From Selected Documents

This example extracts the purchase orders with status closed from a public repository, using stage $out to create collection closed-orders for them.

Because no path is needed and the repository is public (so no credential is needed), we can use the abbreviated form for the value of field $external: just the repository location. (You can use the abbreviated form with any pre-authenticated URI.)

db.aggregate([ {**$external** :
                  **"https://public-repo.example.com/.../orders.json"**},
               {$match : {status : "**closed**"},
               {**$out** : "**closed-orders**"} ])

$lookup Aggregation Pipeline Stage

Restrictions on the use of stage $lookup are described.

You can use a $lookup stage to join documents from another collection on given fields. The following restrictions apply to the fields of stage $lookup:

• Field let is not supported. An error is raised if you use it.

• If the value of field localField is a field that's missing from any input documents then an error is raised. (The missing field is not treated as if it were present with the value of null.)

• If the value of field localField is an array, or if it is a field that occurs more than once in an input document (thus producing multiple values), then an error is raised.

• If the value of field foreignField is a field that's missing from the input documents then no documents match (no error is raised). This includes the case where the document field specified by field localField is missing or has a null value.

• Field pipeline can contain only stages $lookup, $project, and $sort; otherwise, an error is raised. Fields $lookup and $project must not alter any fields referenced by field foreignField; otherwise, an error is raised. See Also: $lookup (aggregation) in the MongoDB Reference manual.

Aggregation Pipeline Operators

Support of MongoDB aggregation pipeline operators is described.

Table 30 Arithmetic Expression

Table 31 Trigonometry Expression Operators

Table 32 Array Expression Operators

Table 33 Boolean Expression

Table 34 Comparison Expression

Table 35 Conditional Expression

Table 36 Date Expression Operators

Table 37 Literal Expression Operator

Table 38 Object Expression

Table 39 Set Expression Operators

Table 40 String Expression

Table 41 Text Expression Operator

Table 42 Type Expression Operators

Table 43 Accumulator Expression

Table 44 Variable Expression

Table 45 System Variables

Table 46 Miscellaneous Operators

Hint $service: Application-Connection Service (Consumer Group)

You can use any of the following application-connection services (consumer groups) with any aggregation pipeline expression, by adding a $service hint to the expression. Service LOW is used by default. LOW, MEDIUM, and HIGH are typically used for reporting and batch processing; TP and TPURGENT are typically used for transaction processing.

• LOW — Low-priority service for reporting and batch processing. Operations are not run in parallel.

• MEDIUM — Medium-priority service for reporting and batch operations. All operations run in parallel and are subject to queuing.

• HIGH — High-priority service for reporting and batch operations. All operations run in parallel and are subject to queuing.

• TP — Typical service for transaction processing. Operations are not run in parallel.

• TPURGENT — Highest-priority service, for time-critical transaction processing. Supports manual parallelism.

For example, the hint here specifies that operator $count should use service HIGH.

db.foo.aggregate([ {"$count":"cnt"} ], {"**hint**":{"**$service**":"HIGH"}}});

Hint $service uses PL/SQL procedure CS_SESSION.switch_service, to switch the consumer group from the default of LOW. Because of this, a user of $service must be granted privilege EXECUTE on package CS_SESSION. Otherwise, the consumer group remains LOW, and no error is raised to indicate that the group status is unchanged.

For example, while connected as an administrator, you can use this command to grant privilege EXECUTE to user myuser:

db.aggregate([{$sql : `grant execute on cs_session to myuser`}]);

Related Topics

• MongoDB Aggregation Pipeline Support

• $sql Aggregation Pipeline Stage

Data Types

Support of MongoDB data types is described.

Table 47 Data Types

Indexes and Index Properties

Support of MongoDB indexes and index properties is described.

Table 48 Indexes

See Also: Index Types in the MongoDB Reference manual

Table 49 Index Options

Footnote Legend

Footnote 1: In Oracle AI Database the collection is table emps with a single JSON-type column data. Footnote 2: On Oracle Database 19c use this query instead: SELECT **json_object**(*) data FROM dept; Footnote 3: On Oracle Database 19c use this query instead: SELECT **json_object**('_id':deptno, 'name', dname) data FROM dept;