CODEWITHSUNDEEP
mongodbindexingmongodb-query
Joyce Babu
Joyce Babu

Reputation: 20694

MongoDB - Sub document search does not work

I have a collection called variants with its specifications as a sub document. Searching for variants with dot notation works for me (but does not use index), whereas using sub document format returns zero results (but utilizes the index). What did I do wrong?

> db.variants.getIndexes();
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "ns" : "automobile.variants",
        "name" : "_id_"
    },
    {
        "v" : 1,
        "key" : {
            "specifications" : 1
        },
        "ns" : "automobile.variants",
        "name" : "specifications_1"
    }
]
> db.variants.find({"specifications" : { "Body" : "SUV" }}).explain()
{
    "cursor" : "BtreeCursor specifications_1",
    "nscanned" : 0,
    "nscannedObjects" : 0,
    "n" : 0,
    "millis" : 0,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "isMultiKey" : false,
    "indexOnly" : false,
    "indexBounds" : {
        "specifications" : [
            [
                {
                    "Body" : "SUV"
                },
                {
                    "Body" : "SUV"
                }
            ]
        ]
    }
}
> db.variants.find({"specifications.Body" : "SUV" }).explain()
{
    "cursor" : "BasicCursor",
    "nscanned" : 787,
    "nscannedObjects" : 787,
    "n" : 176,
    "millis" : 0,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "isMultiKey" : false,
    "indexOnly" : false,
    "indexBounds" : {

    }
}

Upvotes: 0

Views: 1044

Answers (2)

Sammaye
Sammaye

Reputation: 43884

You have put the index on the subdocument itself.

When you do this MongoDB indexes the elements of the array, as such if you were to have:

{
    Specifications: [
        {Body: 'SUV'}
    ]
}

MongoDB could use an index if you queried:

db.col.find({Specifications: {Body: 'SUV'}})

Since that matches the element but it cannot query by the parts of that subdocument.

If you wish to query by the parts of the subdocument using an index you should index those parts, i.e.:

ensureIndex({Specifications.body: 1})

Upvotes: 2

cubbuk
cubbuk

Reputation: 7920

You should have an index on the exact field you will make the query, in this case specifications.Body. In your first query since you have an index on specifications field the query uses the index, but it is not valid according to your schema.

Upvotes: 1

Related Questions