MongoDB index bounds on embedded documents

Question

I have a scenario where my "_id" field contains an embedded document and I am seeing a curious behavior of comparison operators ($gte/$lte) when used against the fields inside the embedded document.

e.g. Consider the below collection which has 9 documents, each with an embedded document as "_id"

db.DocumentWithCompoundKeyCollection.find()

{ "_id" : { "Part1" : 1, "Part2" : 1 }, "SomeValue" : BinData(3,"B8+yWvTV4kS/u3e6Cv8Kcw==") }
{ "_id" : { "Part1" : 1, "Part2" : 2 }, "SomeValue" : BinData(3,"eLS1ONAoGUawW+v+vQdFDQ==") }
{ "_id" : { "Part1" : 1, "Part2" : 3 }, "SomeValue" : BinData(3,"m7WsIyInIEmsgWUMcsJPAw==") }
{ "_id" : { "Part1" : 2, "Part2" : 4 }, "SomeValue" : BinData(3,"z7/2j0g4AUikqS5K1TzZig==") }
{ "_id" : { "Part1" : 2, "Part2" : 5 }, "SomeValue" : BinData(3,"WudfqGYE8U+YwWe3Q0qL1w==") }
{ "_id" : { "Part1" : 2, "Part2" : 6 }, "SomeValue" : BinData(3,"B60SpSmXdUGn6AJDu1JIzg==") }
{ "_id" : { "Part1" : 3, "Part2" : 7 }, "SomeValue" : BinData(3,"xVmhanYiV0+dOdTx7PAZkw==") }
{ "_id" : { "Part1" : 3, "Part2" : 8 }, "SomeValue" : BinData(3,"5NNdVzErt0qephmCMRR1nQ==") }
{ "_id" : { "Part1" : 3, "Part2" : 9 }, "SomeValue" : BinData(3,"mhTiJoHGKkCPUeglCfLUoQ==") }

Now when I run a query to return all document where "Part1" >= 1 and "Part1" <=3, I should get all the 9 documents but mongo returns only 6 documents (all the documents with {"Part1": 3...} are skipped)

db.DocumentWithCompoundKeyCollection.find({ "_id" : { "$gte" : { "Part1" : 1 }, "$lte" : { "Part1" : 3 } } })

{ "_id" : { "Part1" : 1, "Part2" : 1 }, "SomeValue" : BinData(3,"B8+yWvTV4kS/u3e6Cv8Kcw==") }
{ "_id" : { "Part1" : 1, "Part2" : 2 }, "SomeValue" : BinData(3,"eLS1ONAoGUawW+v+vQdFDQ==") }
{ "_id" : { "Part1" : 1, "Part2" : 3 }, "SomeValue" : BinData(3,"m7WsIyInIEmsgWUMcsJPAw==") }
{ "_id" : { "Part1" : 2, "Part2" : 4 }, "SomeValue" : BinData(3,"z7/2j0g4AUikqS5K1TzZig==") }
{ "_id" : { "Part1" : 2, "Part2" : 5 }, "SomeValue" : BinData(3,"WudfqGYE8U+YwWe3Q0qL1w==") }
{ "_id" : { "Part1" : 2, "Part2" : 6 }, "SomeValue" : BinData(3,"B60SpSmXdUGn6AJDu1JIzg==") }

Adding .explain() returns the correct indexBounds as expected so why are the last 3 documents not returned?

Index Plan

"winningPlan" : {
    "stage" : "FETCH",
        "filter" : {
           "$and" : [
                    {
                        "_id" : {
                                "$lte" : {
                                        "Part1" : 3
                                }
                        }
                    },
                    {
                        "_id" : {
                                "$gte" : {
                                        "Part1" : 1
                                }
                        }
                    }
            ]
    },
    "inputStage" : {
            "stage" : "IXSCAN",
            "keyPattern" : {
                    "_id" : 1
            },
            "indexName" : "_id_",
            "isMultiKey" : false,
            "isUnique" : true,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 1,
            "direction" : "forward",
            "indexBounds" : {
                    "_id" : [
                            "[{ Part1: 1.0 }, { Part1: 3.0 }]"
                    ]
            }
    }
},

andresk andresk · Accepted Answer · 2016-10-17T21:17:03

I've never seem this kinda of comparison using objects. Perhaps MongoDB is not handling it correctly.

In order to find the range you want, you could try:

db.DocumentWithCompoundKeyCollection.find({ "_id.Part1" : { $gte : 1, $lte : 3 } })

More info on $gte and $lte can be found here

MongoDB index bounds on embedded documents

1 Answers