Multikey partial index not used with elemMatch

Question

Consider the following document format which has an array field tasks holding embedded documents

{
    "foo": "bar",
    "tasks": [
        {
            "status": "sleep",
            "id": "1"
        },
        {
            "status": "active",
            "id": "2"
        }
    ]
}

There exists a partial index on key tasks.id

{
    "v": 2,
    "unique": true,
    "key": {
        "tasks.id": 1
    },
    "name": "tasks.id_1",
    "partialFilterExpression": {
        "tasks.id": {
            "$exists": true
        }
    },
    "ns": "zardb.quxcollection"
}

The following $elemMatch query with multiple conditions on the same array element

db.quxcollection.find(
{
    "tasks": {
        "$elemMatch": {
            "id": {
                "$eq": "1"
            },
            "status": {
                "$nin": ["active"]
            }
        }
    }
}).explain()

does not seem to use the index

 "winningPlan": {
    "stage": "COLLSCAN",
    "filter": {
        "tasks": {
            "$elemMatch": {
                "$and": [{
                        "id": {
                            "$eq": "1"
                        }
                    },
                    {
                        "status": {
                            "$not": {
                                "$eq": "active"
                            }
                        }
                    }
                ]
            }
        }
    },
    "direction": "forward"
 }

How can I make the above query use the index? The index does seem to be used via dot notation

db.quxcollection.find({"tasks.id": "1"})

however I need the same array element to match multiple conditions which includes the status field, and the following does not seem to be equivalent to the above $elemMatch based query

db.quxcollection.find({
  "tasks.id": "1",
  "tasks.status": { "$nin": ["active"] }
})

There is something missing in the question. I tried to reproduce it here https://mongoplayground.net/p/McHKOlujolW Could you take a look. It is using the index there and I wonder if you can spot how it differs from your setup. — Alex Blex, May 19 '21 at 12:12
@AlexBlex One difference I can spot is in the partial index. If I add `"partialFilterExpression": { "tasks.id": { "$exists": true } }` then it results in collection scan again https://mongoplayground.net/p/NLAHfYangIs — Mario Galic, May 19 '21 at 12:19

score 4 · Accepted Answer · answered May 19 '21 at 12:51

The way the partial indexes work is it uses the path as a key. With $elemMatch you don't have the path explicitly in the query. If you check it with .explain("allPlansExecution") it is not even considered by the query planner.

To benefit from the index you can specify the path in the query:

db.quxcollection.find(
{
    "tasks.id": "1",
    "tasks": {
        "$elemMatch": {
            "id": {
                "$eq": "1"
            },
            "status": {
                "$nin": ["active"]
            }
        }
    }
}).explain()

It duplicates part of the elemMatch condition, so the index will be used to get all documents containing tasks of specific id, then it will filter out documents with "active" tasks at fetch stage. I must admit the query doesn't look nice, so may be add some comments to the code with explanations.

Multikey partial index not used with elemMatch

1 Answers1