1. Oracle Database API for MongoDB
  2. Support for MongoDB APIs, Operations, and Data Types — Reference

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

MongoDB APIs, operations, and data types supported by Oracle 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.

Note:

Only server commands are covered, not client-side wrapper functions. Client-side wrapper functions such as deleteMany() and updateMany() use server commands delete() and update() internally.

  • 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.
  • 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.
  • Update Operators
    Support of MongoDB update operators is described. This includes array, bitwise, field, and modifier update operators.
  • Cursor Methods
    Support of MongoDB cursor methods is described.
  • Aggregation Pipeline Stages
    Support of MongoDB aggregation pipeline stages is described.
  • Aggregation Pipeline Operators
    Support of MongoDB aggregation pipeline operators is described.
  • Data Types
    Support of MongoDB data types is described.
  • Indexes and Index Properties
    Support of MongoDB indexes and index properties is described.

3.1 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.

See Also:

Database Commands in the MongoDB Reference manual

Table 3-1 Administration Commands

Command Support (Since) Notes
Capped Collections No None.
cloneCollectionAsCapped No None.
collMod No None.
collMod, expireAfterSeconds No None.
convertToCapped No None.
create 19c

Creates a collection in the current Oracle Database schema. If the specified collection already exists then this is a no-op.
createView No None.
createIndexes 23ai. No-op (19c) None.
currentOp No None.

drop

 19c None.
dropDatabase 19c

Deletes all collections in the current Oracle Database schema. Does not delete (drop) the schema itself.

The command is available only to a user who is logged in with role root.

dropIndexes 23ai. No-op (19c) None.
filemd5 No None.
getParameter 19c Parameter supported: authenticationMechanisms
killCursors 19c

Supported field: cursors.

killOp No None.
listCollections 19c

Lists collections in the current Oracle Database schema.
listDatabases 19c

Lists Oracle Database schemas enabled for access by Oracle Database API for MongoDB and for Simple Oracle Document Access (SODA).
listIndexes 19c

Lists Oracle Database indexes relevant for the specified collection.
reIndex No-op None.
renameCollection No None.
setParameter No-op Ignored (no error).
validate 19c None.
repairDatabase No-op Ignored (no error).

Note:

Besides creating a collection with explicit use of command create, a collection is automatically created upon its first insertion of a document. That is, to create a collection it is sufficient to refer to it by name when inserting a document into it.

See Also:

Administration Commands in the MongoDB Reference manual

Table 3-2 Aggregation Commands

Command Support (Since) Notes
aggregate 19c None.
count 19c

Supported field: query.

distinct 19c

Supported fields: key, query.

Returns the distinct scalar values targeted by the path specified by key, as an array. Unlike MongoDB, nonscalar values targeted by the path are not included.

mapReduce No None.

See Also:

Aggregation Commands in the MongoDB Reference manual

Table 3-3 Authentication Commands

Command Support (Since) Notes
logout 19c Logs out the current user of an Oracle Database schema on a specific port.

See Also:

Authentication Commands in the MongoDB Reference manual

Table 3-4 Diagnostic Commands

Command Support (Since) Notes
buildInfo 19c

Returns information about current build of Oracle Database API for MongoDB.
collStats 19c None.
compact No-op Ignored (no error).
connPoolStats No None.
connectionStatus 19c None.
dataSize 23ai Supported fields: estimate, keyPattern, min, max.
dbHash No None.
dbStats 19c

Supported field: scale.

Lists statistics about an Oracle Database schema: its collections and relevant indexes.

explain 19c None.
explain, executionStats 19c None.
features No None.
getLog No-op Ignored (no error).
hostInfo 19c None.
listCommands 19c None.
ping 19c None.
profiler No None.
serverStatus 19c None.
top No None.
whatsmyuri 19c None.

See Also:

Diagnostic Commands in the MongoDB Reference manual

Table 3-5 Query and Write Operation Commands

Command Support (Since) Notes
Change Streams No None.
delete 19c

  • Supported fields: deletes, ordered.

  • Supported deletes array operators: q, limit.

See Supported query operators for commands delete, find, findAndModify, and update.

find 19c

See Support for command find.

findAndModify 19c

  • Supported fields: arrayFilters, fields, new, query, remove, sort, update, upsert.

  • Supported field update operators: $bit, $currentDate, $inc, $min, $max, $mul, $rename, $set, $setOnInsert, $unset.

  • Supported array update operators: $, $[], $[<identifier>], $addToOffset, $pop, $pull, $pullAll, $push.

  • Supported array update-operator modifiers supported: $each, $position, $slice, $sort.

See Supported query operators for commands delete, find, findAndModify, and update.

getLastError 19c None.
getMore 19c

Supported fields: batchSize, collection.

getPrevError No None.
GridFS 19c None.
insert 19c

Supported field: documents.

parallelCollectionScan No None.
ReplaceOne No None.
resetError 19c None.
update 19c

  • Supported fields: ordered, updates.

  • Supported fields in elements of array updates: arrayFilters, multi, q, u, upsert.

Returned response contains fields n, nModified, upserted, and writeErrors. Array upserted contains only the document _id values, no index.

Note:

Support for command find.

  • Supported operators: see Supported query operators for commands delete, find, findAndModify, and update.

  • Supported fields: batchSize, filter, limit, projection, returnKey, singleBatch, skip, sort.

    Field returnKey can only return the primary key (e.g. the ObjectID) associated with the documents found. You cannot use it to return only the index key if an index is used to support the query.

  • $ cannot be used in a projection specification. Only simple field selections or omissions can be performed.

  • The JSON scalar types you can specify with $type are as follows:

    • string (default)

    • number

    • date — A date with no time component.

    • dateTime — A timestamp: a date with a time component.

Sorting JSON values:

  • Oracle Database 23ai or later: JSON values are sorted using a canonical sort order — see Comparison and Sorting of JSON Data Type Values.

  • Oracle Database 19c: By default, sorting is lexicographical: JSON values are serialized to obtain strings, which are then compared.

    To request a numeric ordering, date ordering, or timestamp ordering, you use a hint, providing the relevant JSON scalar type with $type.

    For example, the following code requests an ascending lexicographical sort on field name, then an ascending numeric sort on field age, and then a descending date-time (that is, reverse chronological) sort on field birthday. (A positive number, such as 1, means ascending; a negative number, such as -1, means descending.) 

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

Note:

Supported query operators for commands delete, find, findAndModify, and update.

  • Comparison and logical: $eq, $gt, $gte, $in, $lt, $lte, $ne, $nin, $and, $not, $nor, and $or.

  • Element and evaluation: $type, $regex, and $text.

  • Geospatial: $geoIntersects, $geoWithin, $near, $nearSphere.

  • Array: $all, $elemMatch.

See Also:

Query and Write Operation Commands in the MongoDB Reference manual

Table 3-6 Role Management Commands

Command Support (Since) Notes
createRole No None.
dropRole No None.
dropAllRolesFromDatabase No None.
grantRolesToRole No None.
revokePrivilegesFromRole No None.
updateRole No None.
rolesInfo No None.

See Also:

Role Management Commands in the MongoDB Reference manual

Table 3-7 Replication Commands

Command Support (Since) Notes
hello 19c None.
isMaster 19c None.
replSetGetStatus No-op Ignored (no error).

See Also:

Replication Commands in the MongoDB Reference manual

Table 3-8 Sessions Commands

Command Support (Since) Notes
abortTransaction 19c None.
commitTransaction 19c None.
endSessions 19c None.
killAllSessions 19c None.
killAllSessionsByPattern 19c None.
killSessions 19c None.
refreshSessions 19c None.
startSession 19c

Starts a server-side session. Uses a UUID created by the client, if provided, or a secure random UUID. Returns the UUID used.

See Also:

Sessions Commands in the MongoDB Reference manual

Table 3-9 User Management Commands

Command Support (Since) Notes
createUser No None.
dropAllUsersFromDatabase No None.
dropUser No None.
grantRolesToUser No None.
revokeRolesFromUser No None.
updateUser No None.
userInfo No None.

See Also:

User Management Commands in the MongoDB Reference manual

Table 3-10 Sharding Commands

Command Support (Since) Notes
abortReshardCollection No None.
addShard No None.
addShardZone No None.
balancerCollectionStatus No None.
balancerStart No None.
balancerStatus No None.
balancerStop No None.
checkShardingIndex No None.
clearJumboFlag No None.
cleanupOrphaned No None.
cleanupReshardCollection No None.
commitReshardCollection No None.
enableSharding No None.
flushRouterConfig No None.
getShardMap No None.
getShardVersion No None.
isdbGrid No None.
listShards No None.
medianKey No None.
moveChunk No None.
movePrimary No None.
mergeChunks No None.
refineCollectionShardKey No None.
removeShard No None.
removeShardFromZone No None.
reshardCollection No None.
setAllowMigrations No None.
setShardVersion No None.
shardCollection No None.
shardingState No None.
split No None.
splitVector No None.
unsetSharding No None.
updateZoneKeyRange No None.

See Also:

Sharding Commands in the MongoDB Reference manual

Parent topic: Support for MongoDB APIs, Operations, and Data Types — Reference

3.2 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.

See Also:

Query and Projection Operators in the MongoDB Reference manual

Table 3-11 Array Query Operators

Operator Support (Since) Notes

$all

 19c None.

$elemMatch

 19c None.

$size

 19c None.

See Also:

Array Query Operators in the MongoDB Reference manual

Table 3-12 Bitwise Query Operators

Operator Support (Since) Notes

$bitsAllSet

 No None.

$bitsAnySet

 No None.

$bitsAllClear

 No None.

$bitsAnyClear

 No None.

Note:

Bitwise Query Operators in the MongoDB Reference manual

Table 3-13 Comment Query Operator

Operator Support (Since) Notes

$comment

 23ai None.

See Also:

$comment in the MongoDB Reference manual

Table 3-14 Comparison Query Operators

Operator Support (Since) Notes

$eq

 19c None.

$gt

 19c None.

$gte

 19c None.

$lt

 19c None.

$lte

 19c None.

$ne

 19c None.

$in

 19c None.

$nin

 19c None.

See Also:

Comparison Query Operators in the MongoDB Reference manual

Table 3-15 Element Query Operators

Operator Support (Since) Notes

$exists

 19c None.

$type

 19c None.

See Also:

Element Query Operators in the MongoDB Reference manual

Table 3-16 Evaluation Query Operators

Operator Support (Since) Notes

$expr

 No None.

$jsonSchema

 No None.

$mod

 23ai None.

$regex

 19c None.

$text

 19c None.

$where

 No None.

See Also:

Evaluation Query Operators in the MongoDB Reference manual

Table 3-17 Geospatial Query Operators

Operator Support (Since) Notes
$box No None.
$center No None.
$centerSphere No None.
$geoIntersects 19c None.
$geometry No None.
$geoWithin 19c None.
$maxDistance No None.
$near 19c None.
$nearSphere 19c None.
$polygon No None.
$uniqueDocs No None.

Table 3-18 Logical Query Operators

Operator Support (Since) Notes

$and

 19c None.

$nor

 19c None.

$not

 19c None.

$or

 19c None.

See Also:

Logical Query Operators in the MongoDB Reference manual

Table 3-19 Projection Operators

Operator Support (Since) Notes

$elemMatch

 19c None.

$meta

 No None.

$slice

 No None.

See Also:

Projection Operators in the MongoDB Reference manual

Parent topic: Support for MongoDB APIs, Operations, and Data Types — Reference

3.3 Update Operators

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

Table 3-20 Array Update Operators

Operator Support (Since) Notes

$

 19c None.

$[]

 19c None.

$[<identifier>]

 19c None.

$addToSet

 19c None.

$pop

 19c None.

$pull

 19c None.

$pullAll

 19c None.

$push

 19c None.

See Also:

Update Array

Table 3-21 Bitwise Update Operator

Operator Support (Since) Notes

$bit

 19c None.

Note:

Update Bitwise in the MongoDB Reference manual

Table 3-22 Field Update Operators

Operator Support (Since) Notes

$currentDate

 19c None.

$inc

 19c None.

$max

 19c None.

$min

 19c None.

$mul

 19c None.

$rename

 19c None.

$set

 19c None.

$setOnInsert

 19c None.

$unset

 19c None.

See Also:

Update Field in the MongoDB Reference manual

Table 3-23 Modifier Update Operators

Operator Support (Since) Notes

$each

 19c None.

$position

 19c None.

$slice

 19c None.

$sort

 19c None.

See Also:

Update Operators in the MongoDB Reference manual

Parent topic: Support for MongoDB APIs, Operations, and Data Types — Reference

3.4 Cursor Methods

Support of MongoDB cursor methods is described.

Table 3-24 Cursor Methods

Method Support (Since) Notes
$cursor.batchSize() 19c None.
$cursor.close() 19c None.
$cursor.collation() No None.
$cursor.comment() 19c None.
$cursor.count() 19c None.
$cursor.explain() 19c None.
$cursor.forEach() 19c None.
$cursor.hasNext() 19c None.
$cursor.hint() 19c None.
$cursor.isClosed() 19c None.
$cursor.isExhausted() 19c None.
$cursor.itcount() 19c None.
$cursor.limit() 19c None.
$cursor.map() 19c None.
$cursor.max() 19c None.
$cursor.maxScan() No None.
$cursor.maxTimeMS() 19c None.
$cursor.min() 19c None.
$cursor.next() 19c None.
$cursor.noCursorTimeout() 19c None.
$cursor.objsLeftInBatch() 19c None.
$cursor.pretty() 19c None.
$cursor.readConcern() 19c None.
$cursor.readPref() 19c None.
$cursor.returnKey() 19c None.
$cursor.showRecordId() 19c None.
$cursor.size() 19c None.
$cursor.skip() 19c None.
$cursor.sort() 19c None.
$cursor.tailable() 19c None.
$cursor.toArray() 19c None.

See Also:

Cursor Methods in the MongoDB Reference manual

Parent topic: Support for MongoDB APIs, Operations, and Data Types — Reference

3.5 Aggregation Pipeline Stages

Support of MongoDB aggregation pipeline stages is described.

See Also:

Aggregation Pipeline Stages in the MongoDB Reference manual

Table 3-25 Stages

Stage Support (Since) Notes
$addFields 23ai Alias: $set.
$bucket 23ai None.
$bucketAuto No None.
$collStats 19c

Lists statistics about the specified collection and the Oracle Database indexes relevant for it.

Supported fields: scale.

$count 19c None.
$currentOp No None.
$external 23ai None.
$facet 23ai None.
$geoNear No None.
$graphLookup No None.
$group 23ai None.
$indexStats No None.
$limit 19c None.
$listLocalSessions No None.
$listSessions No None.
$lookup No None.
$match 19c None.
$merge No None.
$out 23ai None.
$planCacheStats No None.
$project 19c None.
$redact No None.
$replaceRoot 23ai Alias: $replaceWith.
$replaceWith 23ai Alias for $replaceRoot.
$sample 23ai None.
$setWindowFields No None.
$set 23ai Alias for $addFields.
$skip 19c None.
$sort 23ai None.
$sortByCount 23ai None.
$sql 19c See $sql Aggregation Pipeline Stage.
$unionWith 23ai None.
$unset 19c None.
$unwind 23ai None.

Parent topic: Support for MongoDB APIs, Operations, and Data Types — Reference

3.5.1 $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-26. 

{$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>}}.

<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 a SELECT statement that projects a single JSON-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 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:

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.

Table 3-26 $sql Fields

Field Type Description Required?
statement string

The SQL statement to execute.

 Yes.
binds Any type

SQL variable bindings, each being a variable and its value. See binds Field.

No.
dialect string

The dialect of the SQL statement (statement). The value must be "oracle" (otherwise, an error is raised).

No.
format string

The format of the output documents for stage $sql. The value must be "oracle" (otherwise, an error is raised).

No.
resetSession boolean

  • true means that the database session in which the $sql statement is executed is not reused. Changes in session state are thus not visible to commands subsequent to the $sql command.

    If the $sql statement is part of a transaction, then the session is not reset until that transaction ends.

  • false means that the current session is reused after the $sql command. The sessions state might be visible to subsequent commands.

No. Default: false.

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, the statement 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 3-27 Fields of binds Object

Field JSON (BSON) Type Description Required?
index number

The index (one-based position) of the given variable binding in the SQL statement.

No. If absent, it is inferred from the value's position in the array.

Fields index and name are mutually exclusive: if one is present the other must be absent (otherwise an error is raised).

name string

The name of the bind variable.

No.

Fields index and name are mutually exclusive: if one is present the other must be absent (otherwise an error is raised).

value Any type

The value of the bind variable.

No. If absent, the object itself is the bind value.

For example,

{binds:[{"foo":123},...]}

is equivalent to

{binds:[{value:{"foo":123}},...]}
dataType string

The SQL data type to use for a given variable binding.

No. If absent, the default type for the given BSON value is used. See Supported SQL Data Types for Field dataType.

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 Database 23ai, JSON type is supported for each of the supported BSON types. Prior to release 23ai, an error is raised if field dataType has value JSON.

Table 3-28 Field datatype Values

Input BSON Type Supported SQL Type Default SQL Type
String JSON, VARCHAR2 VARCHAR2
Double JSON, BINARY_DOUBLE BINARY_DOUBLE
Decimal128, Int32, or Int64 JSON, NUMBER NUMBER
Boolean JSON, VARCHAR2, BOOLEAN

Oracle Database 23ai: BOOLEAN

Oracle Database 19c: Error — no default type

ObjectId or Binary JSON, RAW RAW
DateTime JSON, TIMESTAMP WITH TIME ZONE TIMESTAMP WITH TIME ZONE
Object JSON, VARCHAR2

Oracle Database 23ai: JSON

Oracle Database 19c: Error (no default type)

Array JSON, VARCHAR2

Oracle Database 23ai: JSON

Oracle Database 19c: Error (no default type)

Null

Any SQL type mentioned above.

For JSON type, BSON null maps to JSON null. For all other types it maps to SQL NULL.

VARCHAR2

$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-29. Selection of a value from a column of any other type raises an error.

Table 3-29 SELECT: Mappings of Non-JSON SQL Columns to BSON

SQL Column Type BSON (JSON Scalar) Type
BINARY_DOUBLE, BINARY_FLOAT double
BLOB raw
RAW binary
CLOB, VARCHAR2 string
DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE

date

(UTC is assumed for DATE and TIMESTAMP.)

NUMBER If scale is zero then int32 or int64, depending on the precision. Otherwise, double.

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} ]

Parent topic: Aggregation Pipeline Stages

3.5.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-30. 


{$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 3-30 $external Fields

Field Type Description Required?
location string

The location of the external JSON file to use.Foot 4

The string text is a either a URL, such as "https://raw.somerepository.com/myuser/my-db-schemas/main/order_entry/PurchaseOrders.dmp" or a file name, such as "mycomments.json", within the database directory object specified by field directory. See Location of Data Files and Output Files in Oracle Database Utilities.

Yes
directory string

The database directory object that contains the file specified by field location. See Location of Data Files and Output Files in Oracle Database Utilities.

No, unless the value of field location is a file within a directory object.

credential string

The credential object to use when accessing a file in an object-store private bucket. (Field credential need not be specified for a file in a public bucket.)

You create a credential object using PL/SQL subprogram DBMS_CREDENTIAL.create_credential or DBMS_CLOUD.create_credential. See how to Create a Credential for Object Stores in Oracle Database Utilities.

No
path string

A SQL/JSON path expression that targets JSON objects to extract from the JSON file as separate documents. The default value is "$[*]".

One typical case is a file with a single JSON array of objects. Another is a file with multiple objects at top level.

No

Footnote 4 Accessing external files using the Internet requires PL/SQL package DBMS_CLOUD. It is pre-installed for Oracle Autonomous 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 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"} ])

Parent topic: Aggregation Pipeline Stages

3.6 Aggregation Pipeline Operators

Support of MongoDB aggregation pipeline operators is described.

See Also:

Aggregation Pipeline Operators in the MongoDB Reference manual

Table 3-31 Arithmetic Expression Operators

Operator Support (Since) Notes
$abs 23ai None.
$add 23ai None.
$ceil 23ai None.
$divide 23ai None.
$exp 23ai None.
$floor 23ai None.
$ln 23ai None.
$log 23ai None.
$log10 23ai None.
$mod 23ai None.
$multiply 23ai None.
$pow 23ai None.
$round 23ai None.
$sqrt 23ai None.
$subtract 23ai None.
$trunc 23ai None.

See Also:

Arithmetic Expression Operators in the MongoDB Reference manual

Table 3-32 Array Expression Operators

Operator Support (Since) Notes
$arrayElemAt 23ai None.
$arrayToObject 23ai None.
$concatArrays 23ai None.
$filter 23ai None.
$first 23ai None.
$firstN 23ai None.
$in 23ai None.
$indexOfArray 23ai None.
$isArray 23ai None.
$last 23ai None.
$lastN 23ai None.
$objectToArray 23ai None.
$range No None.
$reduce 23ai None.
$reverseArray 23ai None.
$size 23ai None.
$slice 23ai None.
$sortArray 23ai None.
$zip 23ai None.

See Also:

Array Expression Operators in the MongoDB Reference manual

Table 3-33 Boolean Expression Operators

Operator Support (Since) Notes
$and 23ai None.
$not 23ai None.
$or 23ai None.

See Also:

Boolean Expression Operators in the MongoDB Reference manual

Table 3-34 Comparison Expression Operators

Operator Support (Since) Notes
$cmp 23ai None.
$eq 23ai None.
$gt 23ai None.
$gte 23ai None.
$lt 23ai None.
$lte 23ai None.
$ne 23ai None.

See Also:

Comparison Expression Operators in the MongoDB Reference manual

Table 3-35 Conditional Expression Operators

Operator Support (Since) Notes
$cond 23ai None.
$ifNull 23ai None.
$switch 23ai None.

See Also:

Conditional Expression Operators in the MongoDB Reference manual

Table 3-36 Date Expression Operators

Operator Support (Since) Notes
$dateAdd No None.
$dateDiff No None.
$dateFromParts No None.
$dateFromString 23ai None.
$dateSubtract No None.
$dateToParts No None.
$dateToString 23ai None.
$dateTrunc No None.
$dayOfMonth No None.
$dayOfWeek No None.
$dayOfYear No None.
$hour No None.
$isoDayOfWeek No None.
$isoWeek No None.
$isoWeekYear No None.
$millisecond No None.
$minute No None.
$month No None.
$second No None.
$week No None.
$year No None.

See Also:

Date Expression Operators in the MongoDB Reference manual

Table 3-37 Literal Expression Operator ($literal)

Operator Support (Since) Notes
$literal 23ai None.

See Also:

Literal Expression Operator in the MongoDB Reference manual

Table 3-38 Object Expression Operators

Operator Support (Since) Notes
$mergeObjects 23ai None.
$objectToArray 23ai None.
$setField No None.

See Also:

Object Expression Operators in the MongoDB Reference manual

Table 3-39 Set Expression Operators

Operator Support (Since) Notes
$anyElementFalse No None.
$anyElementTrue No None.
$setDifference No None.
$setEquals No None.
$setIntersection No None.
$setIsSubset No None.
$setUnion 23ai None.

See Also:

Set Expression Operators in the MongoDB Reference manual

Table 3-40 String Expression Operators

Operator Support (Since) Notes
$concat 23ai None.
$indexOfBytes No None.
$indexOfCP 23ai None.
$ltrim 23ai None.
$regexFind No None.
$regexFindAll No None.
$regexMatch No None.
$replaceAll No None.
$replaceOne No None.
$rtrim 23ai None.
$split No None.
$strcasecmp 23ai None.
$strLenBytes No None.
$strLenCP No None.
$substr No None.
$substrBytes No None.
$substrCP No None.
$toLower 23ai None.
$toUpper 23ai None.
$trim 19c None.

See Also:

String Expression Operators in the MongoDB Reference manual

Table 3-41 Text Expression Operator ($meta)

Operator Support (Since) Notes
$meta No None.

See Also:

Text Expression Operator in the MongoDB Reference manual

Table 3-42 Type Expression Operators

Operator Support (Since) Notes
$convert No None.
$isNumber 23ai None.
$toBool 23ai None.
$toDate 23ai None.
$toDecimal No None.
$toDouble 23ai None.
$toInt No None.
$toLong No None.
$toObjectId No None.
$toString 23ai None.
$type 19c None.

See Also:

Type Expression Operators in the MongoDB Reference manual

Table 3-43 Accumulator Expression Operators

Operator Support (Since) Notes
$accumulator No None.
$addToSet 23ai None.
$avg 23ai None.
$bottom 23ai None.
$bottomN No None.
$count 23ai None.
$first 23ai None.
$firstN No None.
$last 23ai None.
$lastN No None.
$max 23ai None.
$maxN No None.
$min 23ai None.
$push 23ai None.
$stdDevPop 23ai None.
$stdDevSamp 23ai None.
$sum 23ai None.
$top 23ai None.
$topN No None.

See Also:

Accumulators ($group) and Accumulators ($project)in the MongoDB Reference manual

Table 3-44 Variable Expression Operator

Operator Support (Since) Notes
$let 23ai None.

See Also:

Variable Expression Operators in the MongoDB Reference manual

Table 3-45 System Variables

Variable Support (Since) Notes
$$CURRENT 23ai None.
$$DESCEND No None.
$$KEEP No None.
$$PRUNE No None.
$$REMOVE No None.
$$ROOT 23ai None.

See Also:

Variables in Aggregation Expressions in the MongoDB Reference manual

Table 3-46 Miscellaneous Operators

Operator Support (Since) Notes
$getField No None.
$rand 23ai None.
$sampleRate No None.
$map No None.

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"}}});

Related Topics

Parent topic: Support for MongoDB APIs, Operations, and Data Types — Reference

3.7 Data Types

Support of MongoDB data types is described.

Table 3-47 Data Types

Data Type and Alias Support (Since) Notes
32-Bit Integer (int) 19c None.
64-Bit Integer (long) 19c None.
Array (array) 19c None.
Binary Data (binData) 19c None.
Boolean (bool) 19c None.
Date (date) 19c None.
DBPointer (dbPointer) No None.
Decimal128 (decimal) 19c None.
Double (double) 19c None.
JavaScript (javascript) No None.
MaxKey (maxKey) No None.
MinKey (minKey) No None.
Null (null) 19c None.
Object (object) 19c None.
ObjectId (objectId) 19c None.
Regular Expression (regex) No None.
String (string) 19c None.
Symbol (symbol) No None.
Timestamp (timestamp) No None.
Undefined (undefined) No None.

See Also:

$type in the MongoDB Reference manual

Parent topic: Support for MongoDB APIs, Operations, and Data Types — Reference

3.8 Indexes and Index Properties

Support of MongoDB indexes and index properties is described.

Table 3-48 Indexes

Index Type Support (Since) Notes
2d Index No (23ai). No-op (19c) None.
2dsphere Index No (23ai). No-op (19c) You can create an Oracle Database spatial index using SQL CREATE INDEX on the backing table of the collection.
Compound Multikey Index No (23ai). No-op (19c) See Note, below.
Hashed Index No (23ai). No-op (19c) None.
Single Field Multikey Index 23ai. No-op (19c) See Note, below.
Text Index 19c None.

Note:

You can create a suitable Oracle Database index using SQL CREATE INDEX on the backing table of the collection. See Indexes for JSON Data.

If the field cannot ever have an array value then create a json_value function-based index. Otherwise, use an index over a materialized view. See JSON Query Rewrite To Use a Materialized View Over JSON_TABLE.

See Also:

Index Types in the MongoDB Reference manual

Table 3-49 Index Properties

Index Property Support (Since) Notes
Background No (23ai); No-op (19c) None.
Case Insensitive No (23ai); No-op (19c) None.
Partial No (23ai); No-op (19c) None.
Sparse No (23ai); No-op (19c) None.
TTL No (23ai); No-op (19c) When creating the equivalent of a MongoDB compound or single field index using SQL, the index can have property TTL.
Unique 23ai (No-op in 19c) When creating the equivalent of a MongoDB compound or single field index using SQL, the index can be unique.

See Also:

Index Properties in the MongoDB Reference manual

Parent topic: Support for MongoDB APIs, Operations, and Data Types — Reference


Footnote Legend

Footnote 1: In Oracle 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;