Compound multikey index on array in embedded document

[Mathias Anderssén]

Hi,

I'm trying to figure out if there is a way of having a compound index with a nested prefix and array, so far the query planner seems to ignore the multikey part:

To reproduce:

db.createCollection("test");

db.test.insert([{a: { b: 1, c: [{d:1},{d:2}]}},{a: { b: 1, c: [{d:2},{d:3}]}}]);

db.test.createIndex({"a.b": 1, "a.c.d": 1});

db.test.find({"a.b": 1, "a.c.d": 1}).explain(true);

The interesting bits:

"winningPlan" : {

"stage" : "KEEP_MUTATIONS",

"inputStage" : {

"stage" : "FETCH",

"filter" : {

"a.c.d" : {

"$eq" : 1

}

},

"inputStage" : {

"stage" : "IXSCAN",

"keyPattern" : {

"a.b" : 1,

"a.c.d" : 1

},

"indexName" : "a.b_1_a.c.d_1",

"isMultiKey" : true,

"direction" : "forward",

"indexBounds" : {

"a.b" : [

"[1.0, 1.0]"

],

"a.c.d" : [

"[MinKey, MaxKey]"

]

}

}

}

},

and

"executionStats" : {

"nReturned" : 1,

"totalKeysExamined" : 4,

"totalDocsExamined" : 2,

Basically I'm looking to have http://docs.mongodb.org/master/core/index-multikey/#index-arrays-with-embedded-documents but with that "a.b"-prefix.

Can I build my index or query in another way?

Regards,

Mathias

[Stephen Steneker]

On Saturday, 19 September 2015 01:32:05 UTC+10, Mathias Anderssén wrote:

I'm trying to figure out if there is a way of having a compound index with a nested prefix and array, so far the query planner seems to ignore the multikey part:

Hi Mathias,

The rules on multikey index bounds are particularly complex in order to ensure correctness. For more information see:

  http://docs.mongodb.org/manual/core/multikey-index-bounds/#compound-bounds-of-index-fields-from-an-array

Your particular example is described as "Query without $elemMatch":

 http://docs.mongodb.org/manual/core/multikey-index-bounds/#query-without-elemmatch

For this case the query planner cannot compound the bounds of the indexed array so the range ends up being unconstrained (ie. [MinKey, MaxKey]) as per your explain() output.

The multikey documentation page includes some examples of how you can structure your document & multikey index if you want to have compounded index bounds and reduce the number of key comparisons.

In particular:

 - the index keys must share the same field path up to but excluding the field names, and

 - the query must specify predicates on the fields using $elemMatch on that path.

Regards,

Stephen