Aws document db doesnot use indexes in elemMatch operator in find queries?

Question

I am using seperate indexes for an array field of the documents. While querying the documents elemMatch operator is being used. But when i try running the explain command it shows me that the index is not used.

I get the following result for explain query :

    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "mydb.mycollection",
        "winningPlan" : {
            "stage" : "COLLSCAN"
        }
    },
    "serverInfo" : {
        "host" : "xxxxxx",
        "port" : xxxxx,
        "version" : "3.6.0"
    },
    "ok" : 1.0
}

that means that it is not using the index i created for this collection.

Does aws document not use array indexes in elemMatch operator. ?

Answer 1

AWS DocumentDB currently does not support utilizing an index while using $elemMatch operator.

$distinct, $elemMatch, and $lookup Indexing

Amazon DocumentDB does not currently support the ability to use indexes with the $distinct, $elemMatch, and $lookup 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.

https://docs.aws.amazon.com/documentdb/latest/developerguide/functional-differences.html#functional-differences.elemMatch

Example query:

db.getCollection("collection").find(
    { 
        "MyArrayField" : { 
            "$elemMatch" : { 
                "MyFieldOfObjectInArray" : UUID("11111111-1111-1111-111-111111111111")
            }
        }
    }
).hint("Multi_key_index").explain();

Returns error:

Cannot use Hint for this Query. Index is multi key index or sparse index and query is not optimized to use this index.