Indexing MongoDB array position queries

Question

I'd like to build an index that supports queries against specific array element positions.

Given several million documents with arrays of values:

db.foo.insert({ array: [true, false, null, true] })
db.foo.insert({ array: [false, null, true, true] })

I'd like to search for documents with true in position 2.

db.foo.find({ "array.2": true })

If I create a multikey index:

db.foo.createIndex({ array: 1 })

The index supports queries of the form { "array": true } (searching all array elements) but not { "array.2": true } (searching a specific array element).

The documentation says "MongoDB creates an index key for each element in the array", so I was expecting that if I created an index on the array field, it would be used for queries on array.0, array.1, array.2, etc. Is there a trick to getting this to work?

score 3 · Accepted Answer · answered Mar 08 '18 at 06:28

MongoDB's multikey index only indexes the values of the array, and not the position of the said value in the array.

Having said that, there is one possible workaround to do what you require.

Instead of only storing the value of the array, you would need to store a "key" showing the position of the element. Using your example:

> db.test.find()
{
  "_id": 0,
  "a": [
    {"k": 0, "v": true},
    {"k": 1, "v": false},
    {"k": 2, "v": null},
    {"k": 3, "v": true}
  ]
}
{
  "_id": 1,
  "a": [
    {"k": 0, "v": false},
    {"k": 1, "v": null},
    {"k": 2, "v": true},
    {"k": 3, "v": true}
  ]
}

Note that in the example above, I'm using sub-documents with the field k to denote the "array position", and the field v to store the "array element".

You can then create an index that indexes both fields:

db.test.createIndex({'a.k':1, 'a.v':1})

And the query should use the $elemMatch operator to ensure that the whole sub-document matches. Let's say you're searching for the k of 2 and the v of true:

> db.test.find({a: {$elemMatch: {k:2, v:true}}})
{
  "_id": 1,
  "a": [
    {"k": 0, "v": false},
    {"k": 1, "v": null},
    {"k": 2, "v": true},
    {"k": 3, "v": true}
  ]
}

One drawback of this approach is you need to do your own housekeeping regarding the k field. Inserting into the array are a bit more complex since you have to know the highest value of k before doing $push, if you want to treat k like an array index.

Indexing MongoDB array position queries

1 Answers1