“With the right set of mind, with the right people, with the right support, things happen.” Grigor Dimitrov
There is an extensive list of the MongoDB APIs, operations, and data types supported by Oracle Database. The list is not specific to a certain release of MongoDB but to all releases, and is being updated on regular basis as soon as new operations are being supported.
Unsupported MongoDB constructs raise an error while constructs that makes no sense or are not needed due to the Oracle architecture are ignored and no error is raised.
The aim of this blog post is to show how you can bypass unsupported MongoDB constructs with an appropriate workaround.
Let us use the COFFEE collections and tables from the blog post entitled Outliers, high-entropy and flex columns in JSON-to-Duality.
Consider the following examples below, which vary from query operators to DDL statements.
Example 1:
The evaluation query operator $expr is not supported. If we try to find all coffee items where the old price is less than the new price:
db.COFFEE_DUALITY.find({$expr: {$lt: ["$old_price","$price"]}});
we would get the following error message in mongosh:
However, using $sql, we have a workaround:
db.aggregate([{ $sql: "select * from COFFEE_DUALITY cd where cd.data.old_price < cd.data.price" }] );
Example 2:
The DBA command createView is not supported either (we need a view for cheap coffee – price at most 5):
db.createView("CHEAP_COFFEE","COFFEE",[ { $match: { price: {$lt: 5} } } ]);
However, using “$sql” along with “statement”:
db.aggregate([{$sql: {statement: "create JSON Collection View CHEAP_COFFEE
as select * from COFFEE_DUALITY cd where cd.data.price <=5 "}}]);
we can create the view:
Examples 3 and 4:
We have a similar situation with the aggregate $indexStats and the command renameCollection():
db.aggregate([{ $sql: "select * from user_indexes where index_type <> 'LOB' and table_name = 'COFFEE'" }] );
db.aggregate([ {$sql: {statement: "alter table NEW_COL rename to OLD_COL"}} ]);
Example 5:
And here is how to create a partial index for JSON – but in the Oracle database:
db.aggregate([ {$sql: {statement: "CREATE INDEX coffee_price_idx on COFFEE(CASE WHEN JSON_VALUE(data, '$.price') <=5
THEN JSON_VALUE(data, '$.price' RETURNING NUMBER ERROR ON ERROR)
ELSE NULL END)"}} ]);
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. Oracle Database API for MongoDB supports GridFS, a specification for storing large files in a MongoDB database.
Read MoreJulian Dontcheff’s Database Blog
