DocDB Elastic cannot use regex filter that starts with escape characters

Question

Filtering on _id field using regex works fine if the regex does not start with escape characters

```
db.getCollection('col').aggregate([ { $match: { _id: /^xx/ } } ], {hint: {_id: 1}, explain: true })

```

But It breaks when the there are escape characters in the beginning:

```
db.getCollection('col').aggregate([ { $match: { _id: /^\/xx/ } } ], {hint: {_id: 1}, explain: true })

```

The error is:

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

Answer

Hello AWS Customer

From the details provided , kindly allow me to mention it seems in both cases you have provided similar query. 
Based on the testing in my environment, I can see that query is working and not providing any error.

### Testing Details :

##### Created collection :

```
rs0:PRIMARY> db.createCollection("mycoll")
{ "ok" : 1, "operationTime" : Timestamp(1698782387, 1) }

```

#### Inserted data :

```
rs0:PRIMARY> db.mycoll.insert({"name":"anushka", "age":54, "Test" : { "Date" : "2023-06-04T02:52:25.638-07:00", "Status" : "Completed" }});
WriteResult({ "nInserted" : 1 })
rs0:PRIMARY> db.mycoll.insert({"name":"joe", "age":45, "Test" : { "Date" : "2023-05-04T02:52:25.638-07:00", "Status" : "Pending" }});
WriteResult({ "nInserted" : 1 })

```

#### Find query :

```
rs0:PRIMARY> db.mycoll.findOne({"name":"joe"});
{
        "_id" : ObjectId("65415cb455a04e2c7cda0bc3"),
        "name" : "joe",
        "age" : 45,
        "Test" : {
                "Date" : "2023-05-04T02:52:25.638-07:00",
                "Status" : "Pending"
        }
}
```

#### Running provided query

```

rs0:PRIMARY> db.getCollection('mycoll').aggregate([ { $match: { _id: /^xx/ } } ], {hint: {_id: 1}, explain: true })
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.mycoll",
                "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                                "stage" : "IXOR",
                                "inputStages" : [
                                        {
                                                "stage" : "IXSCAN",
                                                "indexName" : "_id_"
                                        },
                                        {
                                                "stage" : "IXSCAN",
                                                "indexName" : "_id_"
                                        }
                                ]
                        }
                }
        },
        "serverInfo" : {
                "host" : "checkdb",
                "port" : 27017,
                "version" : "5.0.0"
        },
        "ok" : 1,
        "operationTime" : Timestamp(1698782503, 1)
}
```

Since complete details are not present their might be few miss in replicating the use case.

To answer your question, we require additional details to replicate your use case for getting better understanding of the issue and help you resolve it. Please open a support case with AWS using the following 
link [+] https://console.aws.amazon.com/support/home#/case/create

Thank you for your kind patience with us.

DocDB Elastic cannot use regex filter that starts with escape characters

Testing Details :

Created collection :

Inserted data :

Find query :

Running provided query

相關內容