If I have data in my users collection that looks like:

{ name: '...', 
  email: '...', 
  ...,
  photos: {
     123: { url: '...', title: '...', ... },
     456: { url: '...', title: '...', ... },
     ...
  }
} 

And I want to find which user owns photo id 127, then I am using the query:

db.users.find( {'photos.127': {'$exists' => true} } );

I've tried, but it doesn't seem possible to get MongoDB to use an index for this query. The index I tried was: db.users.ensureIndex({photos:1});. And when I used explain() mongo told me it was using a BasicCursor (i.e., no index was used).

Is it possible to create an index that mongo will use for this query?

Solution 1

Updated:

Seems $exists queries use index properly now based on these tickets $exists queries should use index & {$exists: false} will not use index

Old Answer:

No, there is no way to tell mongodb to use index for exists query. Indexing is completely related to data. Since $exists is only related to the keys (fields) it cant be used in indexes.

$exists just verifies whether the given key (or field) exists in the document.

Solution 2

Since MongoDB 2.0 $exists queries should use an index. Unfortunately this fix has disappeared in the newest version and will be fixed in MongoDB 2.5

Solution 3

$exist will not use index, but you can change your data structure to

photos: [
     {id:123, url: '...', title: '...', ... },
     {id:456, url: '...', title: '...', ... },
     ...
  ]

and then use

db.users.ensureIndex({photos.id:1}) 

to create index for photo id.

It seems I am wrong, in fact, you can force your $exists query to use your index. Let us go on using the above structure, but your photo id is not certainly contained , that is to say some docs will have the key 'id' and some will not. Then you can create sparse index on it:

db.users.ensureIndex({'photos.id': 1}, {'sparse': true})

then query like this:

db.users.find({'photos.id': {$exists: true}}).hint({'photos.id': 1})

you can add explain to see if the query is using index. Here is my result, my collection's mobile key is similar to your photos.id:

> db.test.count()
50000
> db.test.find({'mobile': {$exists: true}}).hint({'mobile': 1}).explain()
{
        "cursor" : "BtreeCursor mobile_1",
        "nscanned" : 49999,
        "nscannedObjects" : 49999,
        "n" : 49999,
        "millis" : 138,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
                "mobile" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ]
        }
}

> db.test.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "ns" : "test.test",
                "name" : "_id_"
        },
        {
                "v" : 1,
                "key" : {
                        "mobile" : 1
                },
                "ns" : "test.test",
                "name" : "mobile_1",
                "sparse" : true,
                "background" : true
        }
]

Hope to help!

Solution 4

As of June 2022 the index CANNOT be used for ${exists: true} case and CAN BE ONLY PARTIALLY used for the half of other cases. There is a major (IMO) design bug with the MongoDB index which people are still often unaware of so I'm posting this answer with a workaround for some cases.

The issue is that MongoDB indexes non-existing fields as nulls and as such these fields are indistinguishable from nulls from the index perspective. In addition there is some mess related to JS analogy, where undefined === null is false but undefined == null is true. That means:

  • When searching for {$exists: false} the index can be used. For that both null and non-existing values are scanned in the index , documents are fetched, and values equal to null are filtered out. Corresponding MongoDB stages: [null, null] IXSCAN and FETCH with {"$not" : {$exists: true}} filter.
  • When searching for {field: null} the index can be used. For that, just the index scan is needed. MongoDB parses that as {field: { $eq: null }}. For some likely historical reasons Mongo also searches for deprecated undefined type/value: [undefined, undefined]U[null, null] IXSCAN. IMO it would be was more logical to just interpret equality as value equality and not include non-existing fields in the results. But it is what it is.
  • To REALLY search for value equal to null, we need to search for field: {$type: "null"}. Index can be used. Similarly to the first case, stages are: [null, null] IXSCAN and FETCH with {'$type': [10]} filter. Null values are unnecessarily fetched then filtered.
  • If you need to retrieve all existing values, i.e. including those with value null, then you are in bad luck. The search query is {field: {$exists: true}}. Index cannot be used. If mongo was using the index, it would include values indexed as null to include documents with the field equal to null, to later filter out non-existing values. So full index would be needed, and so the full collection scan is more efficient. Mongo stages: COLLSCAN with { '$exists': true } filter.
  • If you can live without values equal to null, i.e. it is fine to not include $type: null in results, then index can be used. If you know your field type is e.g. ObjectId then you can search for {field: {$type: "object"}}, for the list of types: {field: {$type: ["number", "object"]}}, or just {field: {$ne: "null"}}. The latter will exclude null and undefined types from the search. Mongo stages are:
    • [{},[]) IXSCAN and FETCH
    • '[nan.0, inf.0]U[{}, [])' and FETCH
    • [MinKey, undefined)U(null, MaxKey] IXSCAN and FETCH.

There is an issue filed in Feb 2014: https://jira.mongodb.org/browse/SERVER-12869. Unfortunately, MongoDB hasn't prioritized it yet, nor reflected the issue in the official documentation.