Can I do a MongoDB "starts with" query on an indexed subdocument field?

Question

I'm trying to find documents where a field starts with a value.

Table scans are disabled using notablescan.

This works:

db.articles.find({"url" : { $regex : /^http/ }})

This doesn't:

db.articles.find({"source.homeUrl" : { $regex : /^http/ }})

I get the error:

error: { "$err" : "table scans not allowed:moreover.articles", "code" : 10111 }

There are indexes on both url and source.homeUrl:

{
    "v" : 1,
    "key" : {
        "url" : 1
    },
    "ns" : "mydb.articles",
    "name" : "url_1"
}

{
    "v" : 1,
    "key" : {
        "source.homeUrl" : 1
    },
    "ns" : "mydb.articles",
    "name" : "source.homeUrl_1",
    "background" : true
}

Are there any limitations with regex queries on subdocument indexes?

Terpstra

Should work, what happen when you do an explain? Have you tried to run it with a db allowing scan? (with an explain) – Melvinamelvyn

I've tried running an explain but it was taking so long I had to kill it. The queries both return the expected results if i enable table scans. – Terpstra

Answer 1

When you disable table scans, it means that any query where a table scan "wins" in the query optimizer will fail to run. You haven't posted an explain but it's reasonable to assume that's what is happening here based on the error. Try hinting the index explicitly:

db.articles.find({"source.homeUrl" : { $regex : /^http/ }}).hint({"source.homeUrl" : 1})

That should eliminate the table scan as a possible choice and allow the query to return successfully.

Hatti

This looks promising though I'm getting: error: { "$err" : "bad hint", "code" : 10113 } – Terpstra

That's the error you get when you specify an index that doesn't exist. Which suggests that either it is missing, not finished building yet (note background: true), or that you might have a typo – Hatti

Good point, though I've confirmed that the index has finished building and still get the same error. Even more strangely, if I specify the name of a non-subdocument index, e.g. url, I get results back but they don't honour the regex query! For example: db.articles.find({"source.homeUrl" : { $regex : /^http/ }}).hint({"url" : 1}) – Terpstra

The results issue may be due to this bug: jira.mongodb.org/browse/SERVER-15235 which is currently not fixed, even in master as of writing this. The inability to hint is odd, and should be reproducible so I will see if I can make it happen - can you mention what version you are currently on? – Hatti

We're using 2.4.9 currently. – Terpstra

Any luck reproducing? – Terpstra