Functional Differences: Amazon DocumentDB and MongoDB

    In Amazon DocumentDB, all CRUD statements (findAndModify, update, insert, delete) guarantee atomicity and consistency, even for operations that modify multiple documents. With the launch of Amazon DocumentDB 4.0, explicit transactions that provide ACID properties for multi-statement and multi-collection operations are now supported. For more on using transactions in Amazon DocumentDB, please see Transactions.

    The following are examples of operations in Amazon DocumentDB that modify multiple documents that satisfy both atomic and consistent behaviors.

    1. db.miles.updateMany(
    2.     { "credit_card": { $eq: true } }, 
    3.     { $mul: { "flight_miles.$[]": NumberInt(2) } }
    4. )
    1. db.runCommand({
    2.   update: "miles",
    3.   updates: [
    4.     {
    5.       q: { "credit_card": { $eq: true } },
    6.       u: { $mul: { "flight_miles.$[]": NumberInt(2) } },
    7.       multi: true
    8.     }
    9.   ]
    10. })
    1. db.products.deleteMany({
    2.   "cost": { $gt: 30.00 }
    3. })
    1. db.runCommand({
    2.   delete: "products",
    3.   deletes: [{ q: { "cost": { $gt: 30.00 } }, limit: 0 }]
    4. })

    The individual operations that compose bulk operations such as updateMany and deleteMany are atomic but the entirety of the bulk operation is not atomic. For example, the entirety of the insertMany operation is atomic if the individual insert operations execute successfully without error. If an error is encountered with an insertMany operation, each individual insert statement within the insertMany operation will execute as an atomic operation. If you require ACID properties for insertMany, updateMany, and deleteMany operations, it is recommended to use an transaction.

    Amazon DocumentDB continues to improve compatibility with MongoDB by working backwards from the capabilities our customers ask us to build. This section contains the functional differences that we have removed in Amazon DocumentDB to make migrations and building applications easier for our customers.

    Array Indexing

    As of April 23, 2020, Amazon DocumentDB now supports the ability to index arrays that are greater than 2,048 bytes. The limit for an individual item in an array still remains as 2,048 bytes, which is consistent with MongoDB.

    If you are creating a new index, no action is needed to take advantage of the improved functionality. If you have an existing index, you can take advantage of the improved functionality by dropping the index and then recreating it. The current index version with the improved capabilities is "v" : 3.

    Note

    For production clusters, the dropping of the index may have an impact on your application performance. We recommend that you first test and proceed with caution when making changes to a production system. In addition, the time it will take to recreate the index will be a function of the overall data size of the collection.

    You can query for the version of your indexes using the following command.

    1. db.collection.getIndexes()

    Output from this operation looks something like the following. In this output, the version of the index is "v" : 3, which is the most current index version.

    1. [
    2.     {
    3.         "v" : 3,
    4.         "key" : {
    5.         "_id" : 1
    6.         "name" : "_id_",
    7.         "ns" : "test.test"
    8.     }
    9. ]

    Multi-key Indexes

    As of April 23, 2020, Amazon DocumentDB now supports the ability to create a compound index with multiple keys in the same array.

    If you are creating a new index, no action is needed to take advantage of the improved functionality. If you have an existing index, you can take advantage of the improved functionality by dropping the index and then recreating it. The current index version with the improved capabilities is "v" : 3.

    Note

    For production clusters, the dropping of the index may have an impact on your application performance. We recommend that you first test and proceed with caution when making changes to a production system. In addition, the time it will take to recreate the index will be a function of the overall data size of the collection.

    You can query for the version of your indexes using the following command.

    Output from this operation looks something like the following. In this output, the version of the index is "v" : 3, which is the most current index version.

    1. [
    2.     {
    3.         "v" : 3,
    4.         "key" : {
    5.             "_id" : 1
    6.         },
    7.         "name" : "_id_",
    8.         "ns" : "test.test"
    9.     }
    10. ]

    Null Characters in Strings

    As of June 22, 2020, Amazon DocumentDB now supports null characters ( '\0' ) in strings.

    Role-Based Access Control

    As of March 26, 2020, Amazon DocumentDB supports role-based access control (RBAC) for built-in roles. To learn more, see . Amazon DocumentDB does not yet support custom roles for RBAC.

    $regex Indexing

    As of June 22, 2020, Amazon DocumentDB now supports the ability for $regex operators to utilize an index.

    To utilize an index with the $regex operator, you must use the hint() command. When using hint(), you must specify the name of the field you are applying the $regex on. For example, if you have an index on field product with the index name as p_1, db.foo.find({product: /^x.*/}).hint({product:1}) will utilize the p_1 index, but db.foo.find({product: /^x.*/}).hint(“p_1”) will not utilize the index. You can verify if an index is chosen by utilizing the explain() command or using the profiler for logging slow queries. For example, db.foo.find({product: /^x.*/}).hint(“p_1”).explain().

    Note

    The hint() method can only be used with one index at a time.

    When using an index with $regex, it is recommended that you create an index on highly selective fields where the number of duplicate values is less than 1% of the total number of documents in the collection. As an example, if your collection contains 100,000 documents, only create indexes on fields where the same value occurs 1000 times or fewer.

    Projection for Nested Documents

    There is a functional difference with $project operator between Amazon DocumentDB and MongoDB in version 3.6 that has been resolved in Amazon DocumentDB 4.0 but will remain unsupported in Amazon DocumentDB 3.6.

    Amazon DocumentDB 3.6 only considers the first field in a nested document when applying a projection whereas MongoDB 3.6 will parse subdocuments and apply the projection to each sub document as well.

    For example: if the projection is “a.b.c”: 1, then the behavior works as expect in both Amazon DocumentDB and MongoDB. However, if the projection is {a:{b:{c:1}}} then Amazon DocumentDB 3.6 will only apply the projection to a and not or c. In Amazon DocumentDB 4.0, the projection {a:{b:{c:1}}} will be applied to a, b, and c.

    Amazon DocumentDB does not support the admin or local database nor MongoDB system.* or startup_log collections respectively.

    cursormaxTimeMS

    In Amazon DocumentDB, cursor.maxTimeMS resets the counter for each getMore request. Thus, if a 3000MS maxTimeMS is specified, the query takes 2800MS, and each subsequent getMore request takes 300MS, then the cursor will not timeout. The cursor will only timeout when a single operations, either the query or an individual getMore request, takes more than the specified maxTimeMS. Further, the sweeper that checks cursor execution time runs at a minute granularity.

    explain()

    Amazon DocumentDB emulates the MongoDB 4.0 API on a purpose-built database engine that utilizes a distributed, fault-tolerant, self-healing storage system. As a result, query plans and the output of explain() may differ between Amazon DocumentDB and MongoDB. Customers who want control over their query plan can use the $hint operator to enforce selection of a preferred index.

    Field Name Restrictions

    Amazon DocumentDB does not support dots “.” in a document field name, for example, db.foo.insert({‘x.1’:1}).

    Amazon DocumentDB also does not support the $ prefix in field names.

    For example, try the following command in Amazon DocumentDB or MongoDB:

    1. rs0:PRIMARY< db.foo.insert({"a":{"$a":1}})

    MongoDB will return the following:

    1. WriteResult({ "nInserted" : 1 })

    Amazon DocumentDB will return an error:

    1. WriteResult({
    2. "nInserted" : 0,
    3. "writeError" : {
    4. "code" : 2,
    5. "errmsg" : "Document can't have $ prefix field names: $a"
    6. }
    7. })

    Note

    There is an exception to this functional difference. The following field names that begin with the $ prefix have been whitelisted and can be successfully used in Amazon DocumentDB: $id, $ref and $db.

    Index Builds

    Amazon DocumentDB allows only one index build to occur on a collection at any given time (foreground or background). If operations such as createIndex() or dropIndex() occur on the same collection when an index build is currently in progress, the newly attempted operation will fail.

    A Time to Live (TTL) index starts expiring documents after the index build (foreground or background) is completed.

    Lookup with empty key in path

    When you look up with a key that includes empty string as part of the path (e.g. x., x..b), and the object has an empty string key path (e.g. {"x" : [ { "" : 10 }, { "b" : 20 } ]}) inside an array, Amazon DocumentDB will return different results than if you were to run the same look up in MongoDB.

    In MongoDB, the empty key path look up within array works as expected when the empty string key is not at the end of path look up. However, when the empty string key is at the end of path look up, it does not look into the array.

    However in Amazon DocumentDB, only the first element within the array is read, because getArrayIndexFromKeyString converts empty string to 0, so string key look up is treated as array index look up.

    MongoDB APIs, Operations, and Data Types

    Amazon DocumentDB is compatible with the MongoDB 3.6 and 4.0 APIs. For an up-to-date list of supported functionality, see Supported MongoDB APIs, Operations, and Data Types.

    Amazon DocumentDB does not support an admin database and thus does not dump or restore the admin database when using the mongodump or mongorestore utilities. When you create a new database in Amazon DocumentDB using mongorestore, you need to re-create the user roles in addition to the restore operation.

    Result Ordering

    Amazon DocumentDB does not guarantee implicit result sort ordering of result sets. To ensure the ordering of a result set, explicitly specify a sort order using sort().

    1. db.inventory.find().sort({ stock: -1 })

    When using the $sort aggregation stage, the sort order is not preserved unless the $sort stage is the last stage in the aggregation pipeline. When using the $sort aggregation stage in combination with the $group aggregation stage, the $sort aggregation stage is only applied to the $first and $last accumulators. In Amazon DocumentDB 4.0, support was added for $push to respect sort order from the previous $sort stage.

    Retryable Writes

    Starting with MongoDB 4.2 compatible drivers, retryable writes is enabled by default. However, Amazon DocumentDB does not currently support retryable writes. The functional difference will manifest itself in an error message similar to the following.

    1. {"ok":0,"errmsg":"Unrecognized field: 'txnNumber'","code":9,"name":"MongoError"}

    Retryable writes can be disabled via the connection string (for example, MongoClient("mongodb://my.mongodb.cluster/db?retryWrites=false")) or the MongoClient constructor’s keyword argument (for example, MongoClient("mongodb://my.mongodb.cluster/db", retryWrites=False)).

    The following is a Python example that disables retryable writes in the connection string.

    Sparse Index

    To use a sparse index that you have created in a query, you must use the $exists clause on the fields that cover the index. If you omit $exists, Amazon DocumentDB does not use the sparse index.

    The following is an example.

    1. db.inventory.count({ "stock": { $exists: true }})

    For sparse, multi-key indexes, Amazon DocumentDB does not support a unique key constraint if the look up of a document results in a set of values and only a subset of the indexed fields is missing. For example, createIndex({"a.b" : 1 }, { unique : true, sparse :true }) is not supported, given the input of "a" : [ { "b" : 2 }, { "c" : 1 } ], as "a.c" is stored in the index.

    Storage Compression

    Amazon DocumentDB doesn’t currently support compression for stored data or indexes. Data sizes for stored data and indexes might be larger than when you use other options.

    Using $elemMatch Within an $all Expression

    Amazon DocumentDB does not currently support the use of the $elemMatch operator within an $all expression. As a workaround, you can use the $and operator with $elemMatch as follows.

    Original operation:

    1. db.col.find({
    2.   qty: {
    3.     $all: [
    4.       { "$elemMatch": { part: "xyz", qty: { $lt: 11 } } },
    5.       { "$elemMatch": { num: 40, size: "XL" } }
    6.     ]
    7.   }
    8. })

    Updated operation:

    1. db.col.find({
    2.   $and: [
    3.     { qty: { "$elemMatch": { part: "xyz", qty: { $lt: 11 } } } },
    4.     { qty: { "$elemMatch": { qty: 40, size: "XL" } } }
    5.   ]
    6. })

    $distinct and $elemMatch Indexing

    Amazon DocumentDB does not currently support the ability to use indexes with the $distinct and $elemMatch operators. As a result, utilizing these operators will result in collection scans. Performing a filter or match before utilizing one of these operators will reduce the amount of data that needs to be scanned, and thus can improve performance.

    Amazon DocumentDB does not currently support the ability to use indexes with the $ne, $nin, $nor, $not, $exists, $distinct and $elemMatch operators. As a result, utilizing these operators will result in collection scans. Performing a filter or match before utilizing one of these operators will reduce the amount of data that needs to be scanned, and thus can improve performance.

    Amazon DocumentDB supports the ability to do equality matches (for example, left outer join) but does not support uncorrelated subqueries.

    Utilizing an index with $lookup

    You can now utilize an index with the $lookup stage operator. Based on your use case, there are multiple indexing algorithms that you can use to optimize for performance. This section will explain the different indexing algorithms for and help you choose the best one for your workload.

    By default, Amazon DocumentDB will utilize the hash algorithm when allowDiskUse:false is used and sort merge when allowDiskUse:true is used. For some use cases, it may be desirable to force the query optimizer to use a different algorithm. Below are the different indexing algorithms that the $lookup aggregation operator can utilize:

    • Nested loop: A nested loop plan is typically beneficial for a workload if the foreign collection is <1 GB and the field in the foreign collection has an index. If the nested loop algorithm is being used, the explain plan will show the stage as NESTED_LOOP_LOOKUP.
    • Sort merge: A sort merge plan is typically beneficial for a workload if the foreign collection does not have an index on the field used in lookup and the working dataset doesn’t fit in memory. If the sort merge algorithm is being used, the explain plan will show the stage as SORT_LOOKUP.
    • Hash: A hash plan is typically beneficial for a workload if the foreign collection is < 1GB and the working dataset fits in memory. If the hash algorithm is being used, the explain plan will show the stage as HASH_LOOKUP.

    You can identify the indexing algorithm that is being used for the $lookup operator by using explain on the query. Below is an example.

    1. db.localCollection.explain().
    2. aggregate( [ 
    4.    { 
    5.           $lookup: 
    6.                    { 
    7.                               from: "foreignCollection", 
    8.                               localField: "a", 
    9.        foreignField: "b", 
    10.        as: "joined" 
    11. } 
    12. ]
    14. output
    15. {
    16.     "queryPlanner" : {
    17.         "plannerVersion" : 1,
    18.         "namespace" : "test.localCollection",
    19.         "winningPlan" : {
    20.             "stage" : "SUBSCAN",
    21.             "inputStage" : {
    22.                 "stage" : "SORT_AGGREGATE",
    23.                 "inputStage" : {
    24.                     "stage" : "SORT",
    25.                     "inputStage" : {
    26.                         "stage" : "NESTED_LOOP_LOOKUP",
    27.                         "inputStages" : [
    28.                             {
    29.                                 "stage" : "COLLSCAN"
    30.                             },
    31.                             {
    32.                                 "stage" : "FETCH",
    33.                                 "inputStage" : {
    34.                                     "stage" : "COLLSCAN"
    35.                                 }
    36.                             }
    37.                         ]
    38.                     }
    39.                 }
    40.             }
    41.         }
    42.     },
    43.     "serverInfo" : {
    44.         "host" : "devbox-test",
    45.         "port" : 27317,
    46.         "version" : "3.6.0"
    47.     },
    48.     "ok" : 1
    49. }

    As an alternative to the using the explain() method, you can use the profiler to review the algorithm that is being utilized with your use of the $lookup operator. For more information on the profiler, please see Profiling Amazon DocumentDB Operations.

    Using a planHint

    If you wish to force the query optimizer to use a different indexing algorithm with $lookup, you can use a planHint. To do that, use the comment in the aggregation stage options to force a different plan. Below is an example of the syntax for the comment:

    1. comment : {
    2.     comment :  “<string>”,
    3.     lookupStage : { planHint : “SORT” | “HASH” | "NESTED_LOOP" }
    4. }

    Below is an example of using the planHint to force the query optimizer to use the HASH indexing algorithm:

    1. db.foo.aggregate(
    2.   [                           
    3.     {   
    4.         $lookup:
    5.         {   
    6.             from: "foo",
    7.             localField: "_id",
    8.             foreignField: "_id",
    9.             as: "joined"
    10.         },
    11.     }
    12.   ],
    13.     comment : "{ \\"lookupStage\\" : { \\"planHint\\": \\"HASH\\" }}"

    To test which algorithm is best for your workload, you can use the executionStats parameter of the explain method to measure the execution time of the $lookup stage while modifying the indexing algorithm (i.e., HASH/SORT/NESTED_LOOP).

    The following example shows how to use executionStats to measure the execution time of the $lookup stage using the algorithm.