mongoDB prefix wildcard: fulltext-search ($text) find part with search-string
MongodbWildcardMongodb Problem Overview
I have mongodb with a $text-Index
and elements like this:
{
foo: "my super cool item"
}
{
foo: "your not so cool item"
}
If i do search with
mycoll.find({ $text: { $search: "super"} })
i get the first item (correct).
But i also want to search with "uper" to get the fist item - but if i try:
mycoll.find({ $text: { $search: "uper"} })
I dont get any results.
My Question:
If there is a way to use $text so its finds results with a part of the searching string? (e.g. like '%uper%'
in mysql
)
Attention: I dont ask for a regex only search - i ask for a regex-search within a $text-search!
Mongodb Solutions
Solution 1 - Mongodb
It's not possible to do it with $text
operator.
Text indexes are created with the terms included in the string value or in an array of strings and the search is based in those indices.
You can only group terms on a phrase but not take part of them.
Solution 2 - Mongodb
I don't have enough reputation to comment jasenkoh solution, but this is clearly the best way to deal with this situation.
In OP situation, I would:
db.mycoll.createIndex( { foo: "text" } )
db.mycoll.createIndex( { foo: 1 } )
db.mycoll.find({$or: [{$text: {$search: 'uper'}}, {foo: {$regex: 'uper'}}]})
For better performances (but slightly different results), replace the last line with:
db.mycoll.find({$or: [{$text: {$search: 'uper'}}, {foo: {$regex: '^uper'}}]})
Solution 3 - Mongodb
What you are trying to do in your second example is prefix wildcard search in your collection mycoll
on field foo
. This is not something the textsearch feature is designed for and it is not possible to do it with $text
operator. This behaviour does not include wildcard prefix search on any given token in the indexed field. However you can alternatively perform regex search as others suggested. Here is my walkthrough:
>db.mycoll.find()
{ "_id" : ObjectId("53add9364dfbffa0471c6e8e"), "foo" : "my super cool item" }
{ "_id" : ObjectId("53add9674dfbffa0471c6e8f"), "foo" : "your not so cool item" }
> db.mycoll.find({ $text: { $search: "super"} })
{ "_id" : ObjectId("53add9364dfbffa0471c6e8e"), "foo" : "my super cool item" }
> db.mycoll.count({ $text: { $search: "uper"} })
0
The $text
operator supports search for a single word, search for one or more words or search for phrase. The kind of search you wish is not supported
The regex solution:
> db.mycoll.find({foo:/uper/})
{ "_id" : ObjectId("53add9364dfbffa0471c6e8e"), "foo" : "my super cool item" }
>
The answer to your final question: to do mysql style %super%
in mongoDB you would most likely have to do:
db.mycoll.find( { foo : /.*super.*/ } );
Solution 4 - Mongodb
It should work with /uper/
.
See http://docs.mongodb.org/manual/reference/operator/query/regex/ for details.
Edit:
As per request in the comments:
The solution wasn't necessarily meant to actually give what the OP requested, but what he needed to solve the problem.
Since $regex
searches don't work with text indices, a simple regex search over an indexed field should give the expected result, though not using the requested means.
Actually, it is pretty easy to do this:
db.collection.insert( {foo: "my super cool item"} )
db.collection.insert( {foo: "your not so cool item"})
db.collection.ensureIndex({ foo: 1 })
db.collection.find({'foo': /uper/})
gives us the expected result:
{ "_id" : ObjectId("557f3ba4c1664dadf9fcfe47"), "foo" : "my super cool item" }
An added explain shows us that the index was used efficiently:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.collection",
"indexFilterSet" : false,
"parsedQuery" : {
"foo" : /uper/
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"filter" : {
"foo" : /uper/
},
"keyPattern" : {
"foo" : 1
},
"indexName" : "foo_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"foo" : [
"[\"\", {})",
"[/uper/, /uper/]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
// skipped
},
"ok" : 1
}
To make a long story short: No, you can not reuse a $text
index, but you can do the query efficiently. Like written in https://stackoverflow.com/questions/29892947/implement-auto-complete-feature-using-mongodb-search , one could probably be even more efficient by using a map/reduce approach, eliminating redundancy and unnecessary stop words from the indices, at the cost of being not real time any more.
Solution 5 - Mongodb
As francadaval said, text index is searching by terms but if you combine regex
and text-index
you should be good.
mycoll.find({$or: [
{
$text: {
$search: "super"
}
},
{
'column-name': {
$regex: 'uper',
$options: 'i'
}
]})
Also, make sure that you have normal index applied to the column other than text index.
Solution 6 - Mongodb
if you go with regex you can achieve search for "super cool" but not "super item", to achieve both request do an or request with $text and $regex for the search term.
make sure you index both text indexing and normal indexing to work.
Solution 7 - Mongodb
You could have achieved is as-
db.mycoll.find( {foo: { $regex : /uper/i } })
Here 'i' is an option, denotes case-insensitive search